Prev: Save a backup file
Next: Windows update
From: Rock on 14 May 2010 20:22
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 14 May 2010 20:44
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: Dave Peterson on 14 May 2010 20:48
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: Chip Pearson on 14 May 2010 20:50
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: Rick Rothstein on 15 May 2010 00:11
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?
>>
 |  Next  |  Last
Pages: 1 2
Prev: Save a backup file
Next: Windows update