Call random UserForm from list

[Rock]

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?

 

[Dave Peterson]

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

 

[Dave Peterson]

You don't need the UF in the first procedure. I should have deleted it.

 

[Chip Pearson]

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

 

[Rick Rothstein]

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.

 

[Dave Peterson]

Thanks for the correction.

 

[Rock]

Thank you for clarifying; I will make the recommended changed.

 

[Rock]

Thank you Dave. This is very helpful!

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


.

 

[Rock]

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.

 

[Rock]

Hi Chip, thank you for helping me with this. I appreciate you teaching me
about strings. Thank you for your help!

 

[Dave Peterson]

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