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?

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?
>

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?
>>

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
<(E-Mail Removed)> 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?

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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?
>>

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.
>

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?
> >>
> .
>

Thank you Dave. This is very helpful!

"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?
> >
> .
>

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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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?
> >>
> .
>

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
> <(E-Mail Removed)> 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?
> .
>