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.

1. I want to the second userform to contain a label, whose caption will reflect the random output.
2. 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?

Answer 1

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.

Answer 2

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