Prev: Save a backup file
Next: Windows update
From: Dave Peterson on 15 May 2010 07:30
Thanks for the correction.

On 05/14/2010 23:11, Rick Rothstein wrote:
> I think you are using the wrong constants when you set myNum. In order
> to produce a range of random numbers between 1 and 9, I think the set up
> for the assignment should be this...
>
> myNum = Int((9 - 1 + 1) * Rnd + 1)
>
> For those who might be interested in such things, this macro, which does
> not use Select Case block, should work the same way Dave's code does...
>
> Sub TestMe2()
> Randomize
> UserForms.Add "UserForm" & (Int(9 * Rnd) + 1)
> UserForms(UserForms.Count - 1).Show
> End Sub
>
> Note that the UserForms collection is different than most in Excel as
> its first element is index number zero, not one.
>
From: Rock on 15 May 2010 11:51
Thank you for clarifying; I will make the recommended changed.

"Dave Peterson" wrote:

> You don't need the UF in the first procedure. I should have deleted it.
>
> On 05/14/2010 19:44, Dave Peterson wrote:
> > One way is to use something like:
> >
> > Option Explicit
> > Sub testme1()
> >
> > Dim UF As Object
> > Dim myNum As Long
> >
> > Randomize
> > myNum = Int((9 - 0 + 1) * Rnd + 0)
> >
> > Select Case myNum
> > Case Is = 1: UserForm1.Show
> > Case Is = 2: UserForm2.Show
> > '...repeat this. I got tired!
> > Case Is = 9: UserForm9.Show
> > End Select
> >
> > End Sub
> >
> >
> > Another way would be something like:
> >
> > Option Explicit
> > Sub testme()
> >
> > Dim UF As Object
> > Dim myNum As Long
> >
> > Randomize
> > myNum = Int((9 - 0 + 1) * Rnd + 0)
> >
> > Set UF = VBA.UserForms.Add("Userform" & myNum)
> > UF.Show
> >
> > End Sub
> >
> > On 05/14/2010 19:22, Rock wrote:
> >> I have 9 different UserForms and I can call any specific UserForm with
> >> the
> >> macro below. (Imagine any number where the question mark is)
> >>
> >> Sub ShowUserForm()
> >> UserForm?.Show
> >> End Sub
> >>
> >> What I would like to do instead is have a macro that will call a
> >> different
> >> random UserForm from this list below each time I activate it.
> >>
> >> UserForm1
> >> UserForm2
> >> UserForm3
> >> UserForm4
> >> UserForm5
> >> UserForm6
> >> UserForm7
> >> UserForm8
> >> UserForm9
> >>
> >> Can you help?
> >>
> .
>
From: Rock on 15 May 2010 11:53
Rick, I appreciate your help. Thank you very much. This is just what I
needed. I also appreciate the way you explained things. Thank you.

"Rick Rothstein" wrote:

> I think you are using the wrong constants when you set myNum. In order to
> produce a range of random numbers between 1 and 9, I think the set up for
> the assignment should be this...
>
> myNum = Int((9 - 1 + 1) * Rnd + 1)
>
> For those who might be interested in such things, this macro, which does not
> use Select Case block, should work the same way Dave's code does...
>
> Sub TestMe2()
> Randomize
> UserForms.Add "UserForm" & (Int(9 * Rnd) + 1)
> UserForms(UserForms.Count - 1).Show
> End Sub
>
> Note that the UserForms collection is different than most in Excel as its
> first element is index number zero, not one.
>
> --
> Rick (MVP - Excel)
>
>
>
> "Dave Peterson" <petersod(a)XSPAMverizon.net> wrote in message
> news:eyYqPf88KHA.5412(a)TK2MSFTNGP06.phx.gbl...
> > One way is to use something like:
> >
> > Option Explicit
> > Sub testme1()
> >
> > Dim UF As Object
> > Dim myNum As Long
> >
> > Randomize
> > myNum = Int((9 - 0 + 1) * Rnd + 0)
> >
> > Select Case myNum
> > Case Is = 1: UserForm1.Show
> > Case Is = 2: UserForm2.Show
> > '...repeat this. I got tired!
> > Case Is = 9: UserForm9.Show
> > End Select
> >
> > End Sub
> >
> >
> > Another way would be something like:
> >
> > Option Explicit
> > Sub testme()
> >
> > Dim UF As Object
> > Dim myNum As Long
> >
> > Randomize
> > myNum = Int((9 - 0 + 1) * Rnd + 0)
> >
> > Set UF = VBA.UserForms.Add("Userform" & myNum)
> > UF.Show
> >
> > End Sub
> >
> > On 05/14/2010 19:22, Rock wrote:
> >> I have 9 different UserForms and I can call any specific UserForm with
> >> the
> >> macro below. (Imagine any number where the question mark is)
> >>
> >> Sub ShowUserForm()
> >> UserForm?.Show
> >> End Sub
> >>
> >> What I would like to do instead is have a macro that will call a
> >> different
> >> random UserForm from this list below each time I activate it.
> >>
> >> UserForm1
> >> UserForm2
> >> UserForm3
> >> UserForm4
> >> UserForm5
> >> UserForm6
> >> UserForm7
> >> UserForm8
> >> UserForm9
> >>
> >> Can you help?
> >>
> .
>
From: Rock on 15 May 2010 11:54
Hi Chip, thank you for helping me with this. I appreciate you teaching me
about strings. Thank you for your help!

"Chip Pearson" wrote:

> Regardless of how you get the arbitrary form name, once you have the
> form name in a String variable, you can use code like
>
> Dim FormName As String
> ' get an arbitrary form name somehow, e.g.,
> FormName = "UserForm2"
> With VBA.UserForms
> .Add FormName
> .Item(.Count - 1).Show
> End With
>
>
> Cordially,
> Chip Pearson
> Microsoft Most Valuable Professional,
> Excel, 1998 - 2010
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
>
>
> On Fri, 14 May 2010 17:22:01 -0700, Rock
> <Rock(a)discussions.microsoft.com> wrote:
>
> >I have 9 different UserForms and I can call any specific UserForm with the
> >macro below. (Imagine any number where the question mark is)
> >
> >Sub ShowUserForm()
> >UserForm?.Show
> >End Sub
> >
> >What I would like to do instead is have a macro that will call a different
> >random UserForm from this list below each time I activate it.
> >
> >UserForm1
> >UserForm2
> >UserForm3
> >UserForm4
> >UserForm5
> >UserForm6
> >UserForm7
> >UserForm8
> >UserForm9
> >
> >Can you help?
> .
>
From: Dave Peterson on 15 May 2010 13:44
Another option you may want to consider is to use a single userform. Put put a
multipage with 9 pages/tabs on that userform. Then make each page look like the
corresponding userform.

You may find that it's easier to control with your code, too.

Option Explicit
Private Sub UserForm_Initialize()

Dim myNum As Long

Randomize

'From VBA's help for Rnd
'Int((upperbound - lowerbound + 1) * Rnd + lowerbound)
myNum = Int((9 - 1 + 1) * Rnd + 1)

With Me.MultiPage1
.Style = fmTabStyleNone
.Value = myNum - 1 'it's 0 based
End With

End Sub

On 05/14/2010 19:22, Rock wrote:
> I have 9 different UserForms and I can call any specific UserForm with the
> macro below. (Imagine any number where the question mark is)
>
> Sub ShowUserForm()
> UserForm?.Show
> End Sub
>
> What I would like to do instead is have a macro that will call a different
> random UserForm from this list below each time I activate it.
>
> UserForm1
> UserForm2
> UserForm3
> UserForm4
> UserForm5
> UserForm6
> UserForm7
> UserForm8
> UserForm9
>
> Can you help?
>
First  |  Prev  | 
Pages: 1 2
Prev: Save a backup file
Next: Windows update