VBA: Userform initialize method not being hit when userform initializes

Alex Barrie picture Alex Barrie · Jun 5, 2013 · Viewed 25.9k times · Source

My module code calling the userform:

PreInfo.Show

My userform code:

Public Sub PreInfo_Initialize()
Dim Invoice, Name, Model, Crank, MyValue1, StrokeL As Variant
'Dim ListBox1 As ListBox
Dim c As Range
Dim oneControl As Object

'Empty Text Boxes and Set Focus
For Each oneControl In PreInfo.Controls
Select Case TypeName(oneControl)
Case "TextBox"
    oneControl.Text = vbNullString
'Case "ListBox"
    'oneControl.AddItem "Test"
End Select
Next oneControl

With lbTest
    .AddItem Item:="2 Cylinders" '3 different syntax used as test to isolate issue
    .AddItem "3 Cylinders"
    .AddItem ("5 Cylinders")
End With

Invoice.TextBox.SetFocus 'Activate?

End Sub

My module code does not trigger my userform initialize sub, therefore nothing in that sub runs. I cannot figure out why this is happening. I would greatly appreciate any help!

When this code runs, userform pops up, however none of the listbox items are added

Answer

FCastro picture FCastro · Apr 7, 2017

Userform_Initialize event is triggered by a line like this called in a module:

Load Userform1

In order for it to be triggered again, you'll need to unload the userform (not simply hide it). This can be done either after the load call within the module:

Unload Userform1

Or anywhere within the Userform's code:

Unload Me

Notice that the events Initialize and QueryClose will be triggered by the Unload call as well (QueryClose is also triggered when the close button on the top right corner is pressed), so I really recommend you refrain from using Initialize. Instead, After the Load call, add the initialize code within the same module (or create a separate sub if it'll be called from multiple places).

Sub LoadThatUserform
    Load Preinfo
    'All textboxes are loaded with their value set to vbnullstring, _
         unless you specified otherwise in the Properties box.
    With ThatUserform.lbTest
    'Answering this test
        .AddItem Item:="2 Cylinders" 'Here you used the parameter name. _
              It's entirely optional, which is why the one below _
              also works. It's necessary, however, if you wanna skip _
              an optional parameter on a procedure call.
        .AddItem "3 Cylinders"
        .AddItem ("5 Cylinders") 'This will theoretically create a _
              run-time error: a procedure call either outside of a Call _
              statement or not setting a value to a variable or property _
              doesn't require parentheses.
    End With
    'After loading, show the form
    Preinfo.Show
    'Showing, if not as modeless, stops code execution for the user _
          to make changes to the form. Once he presses a button _
          or whatever, and the form is hidden, code will resume. _
          After you grab every form data you need, just call Unload.
    Unload Preinfo
End Sub

Last but not least, if you're running a Modeless form (let's code run in the background while showing), you'll need to use the Activate event for the code to run. The event sequence is:

  • Userform_Initialize, after Load Userform
  • Userform_Activate, after Userform.Show
  • Userform_QueryClose, after Unload Userform, pressing the closing "X" or terminating via closing Excel/Task Manager
  • Userform_Terminate, when it's really gonna end (though I have no clue how this is used).