VBA: Multiple Userforms Referencing Same Code

Question

I have a VBA code that takes user inputs (criteria) via a userform, creates a list of items that meet said criteria, and randomly outputs one of the list items via message box.

I want to create a second userform that will open after the above code completes, and have the userform display the random output.

I have a few questions regarding how this can be done.

I want to the second userform to contain a label, whose caption will reflect the random output.
I want the second userform to allow the user to re-execute the original code (ie. to change the random result)

Anyone have experience with this?

Darren Bartrup-Cook · Accepted Answer · 2015-08-06 16:29:45Z

I'd use a Property procedure in the second form to get the random value.

As an example in a new blank workbook: Insert a new module and paste in the code below. This code represents your code which outputs a random result.

Public Function RandomNumber() As Double

    RandomNumber = Rnd(10)

End Function

Create a new userform (UserForm2), add a command button (CommandButton1) and a label (Label1). Add the below code to this form. I've put four procedures in there. MyProp sets the value of ThePassedNumber, UpdateLabel1 updates the value shown in the label, UserForm_Initialize executes when the form loads, CommandButton1_Click executes when you click the button.

Private ThePassedNumber As Double

Property Let MyProp(TheNumber As Double)
    ThePassedNumber = TheNumber
    UpdateLabel1
End Property


Private Sub CommandButton1_Click()

    ThePassedNumber = RandomNumber
    UpdateLabel1

End Sub

Private Sub UserForm_Initialize()

    UpdateLabel1

End Sub

Private Sub UpdateLabel1()

    Me.Label1.Caption = ThePassedNumber

End Sub

Add a second form (UserForm1) and add a command button (CommandButton1) and place this code within the form.
It will create a new instance of the userform, pass a random value to it and then display the form.

Private Sub CommandButton1_Click()

    Dim frm2 As UserForm2

    Set frm2 = New UserForm2
    frm2.MyProp = RandomNumber
    frm2.Show

End Sub

Now you just need to rewrite the RandomNumber function to return your list item rather than a number.

This was exactly what I needed. It took a lot of extra tinkering, but now my code works like a dream.

hughg · Accepted Answer · 2015-08-06 16:21:09Z

0

I imagine you just want to use an endless loop that the user breaks when canceled. Something like this:

Sub MySub()
    Dim myRandOutput As Single
    Do
        myRandOutput = myRand()
    Loop While MsgBox(myRandOutput, vbRetryCancel) = vbRetry
End Sub

Function myRand() As Single
    myRand = Rnd()
End Function

answered Aug 6, 2015 at 16:21

hughg

2011 silver badge9 bronze badges

Collectives™ on Stack Overflow

VBA: Multiple Userforms Referencing Same Code

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related