How to pass values from one Userform's code to another

Question

I wish for the user to select a value from a column in a worksheet using a combo-box in a userform - from there, the program extracts a value from within that row (specifically, a hyperlink to another workbook). After that, another userform (list box) is supposed to pop up, populated with values from a row in the second workbook.

Currently, the specific thing I'm having a problem with is transferring the hyperlink value (which is a variant) to the second useform's code so it can grab values from the workbook the hyperlink directs to.

I've tried making the hyperlink variable public and I've tried replicating similar operations I found on the internet.

In the first userform's code:

Public hyperlink_A As Variant

Private Sub findColumns_button_Click()  'within uf_TestSelector
    With uf_ColSelectA
        .hyperlink_A = hyperlink_A
        .Show
    End With
    fillData    'not relevant
End Sub

In the second userform's code:

Option Explicit
Public mainWorkbook As Workbook
Public hyperlink_A As Variant

Private Sub UserForm_Initialize()    'uf_ColSelectA
    With Application.ActiveWindow
        Me.Left = .Left + (.Width - Me.Width) / 2
        Me.Top = .Top + (.Height - Me.Height) / 2
    End With

    MsgBox hyperlink_A  'shows empty MsgBox so hyperlink_A = empty
                        'MsgBox only for debugging purposes

    Dim wb As Workbook
    Set wb = Workbooks.Open(Filename:=hyperlink_A)
End Sub

I'm expecting MsgBox hyperlink_A to open a MsgBox with the path to the second workbook, currently it's returning an empty MSgBox. The Second userform does show (but obviously empty)

Mathieu Guindon Mathieu Guindon · Accepted Answer · 2019-06-05T16:33:04

The Initialize handler runs when the instance of the form class is created - and since you're coding against the forms' default instance, you're not controlling when that happens.

The default instance of a class (any class with a VB_PredeclaredId attribute set to True - not just userforms) is created automatically, as soon as it's referenced. In this case, assuming this is the first time this form is referenced in this execution path, that would be here:

With uf_ColSelectA

If you put a breakpoint (F9) immediately after this line, you'll see that the empty message box pops up before execution reaches the .hyperlink_A = hyperlink_A instruction.

If you want to keep using stateful userform default instances, you can do so by moving the code you have in the Initialize handler into the handler for the Activate event -which will be fired whenever the form is activated - since that's a modal form, that makes this chunk of code run whenever you .Show that form.

Private Sub UserForm_Activate()    'uf_ColSelectA
    With Application.ActiveWindow
        Me.Left = .Left + (.Width - Me.Width) / 2
        Me.Top = .Top + (.Height - Me.Height) / 2
    End With

    MsgBox hyperlink_A 

    Dim wb As Workbook
    Set wb = Workbooks.Open(Filename:=hyperlink_A)
End Sub

If you want to take control of how your objects are created (warmly recommend), then you need to New up the classes yourself. So instead of this:

With uf_ColSelectA
    '...
    .Show
End With

You'll do this:

With New uf_ColSelectA
    '...
    .Show
End With

See UserForm1.Show for more details about the traps & caveats of stateful default instances.

How to pass values from one Userform's code to another

1 Answers