VBA: Userform initialize method not being hit when userform initializes

Question

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

I use userform.hide when the user clicks the "continue" button on the userform, which closes the userform and prints the userform inputs into a worksheet. — Alex Barrie, Jun 05 '13 at 17:47

score 7 · Answer 1 · answered Apr 07 '17 at 21:55

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).

score 3 · Answer 2 · edited Nov 04 '14 at 02:32

3

I had the same problem, and found a very simple solution.

In your case, instead of using

Public Sub PreInfo_Initialize()

use

Public Sub UserForm_Initialize()

edited Nov 04 '14 at 02:32

carlodurso

2,886
4
24
37

answered Nov 04 '14 at 01:58

Sam Lucas

31
2

score 2 · Accepted Answer · answered Jun 06 '13 at 16:18

I have figured it out. Long story short, my Module needed the following code:

Userform.Userform_Activate 'THIS IS THE NEW CODE
Userform.Show 'existing code, unchanged

which signals the userform to activate before it is open (calling "initialize", then showing the userform for the user to alter).

Userform.Show SHOULD cue this activation sub to run, however mine was not for whatever reason. This fixes the issue until I determine why Userform.Userform_Activate was not called like it should have been.

enderland · Answer 4 · 2013-06-05T18:00:54.103

1

I use userform.hide when the user clicks the "continue" button on the userform, which closes the userform and prints the userform inputs into a worksheet

What is happenening is that your userform is never unloaded from memory. Hide only removes it from view.

This means that it is only initialized the first time you run the userform within that Excel instance.

You can prevent this by using

unload me

or

End

instead of UserForm.Hide depending on your other code. You could also potentially use the UserForm_Activate method instead of UserForm_Initialize method.

To populate the ListBox, use:

lbTest.AddItem "3 Cylinders"

etc outside the With statement.

edited Jun 05 '13 at 18:00

answered Jun 05 '13 at 17:52

enderland

13,825
17
98
152

Changed syntax to: unload me The userform comes up, however the ListBox still does not populate. – Alex Barrie Jun 05 '13 at 17:58
@AlexBarrie I added code which solves that problem. If the above code doesn't work, put a breakpoint there and see if your code even gets into this method. – enderland Jun 05 '13 at 18:01
Altered the code (same result). Added break point at the code (program continues past that point without triggering the breakpoint). It's as if the userform.show works, however doesn't trigger the userform_activate sub. – Alex Barrie Jun 05 '13 at 18:05
@AlexBarrie and just to be clear, you have this code in the code associated with your UserForm? – enderland Jun 05 '13 at 18:10
Yes, this project has one module and two userforms (this trouble one being the first one. the second is only textbox's and works as needed). Userform.show is one of the first lines in my module, the rest of the code above is inside the userform, inside the userform_activate() sub. it is a public sub. – Alex Barrie Jun 05 '13 at 18:16
I have figured it out. the syntax "Userform.Userform_Activate" needs to be added above "Userform.Show" in order to direct focus to it before allowing user inputs to the form. Thank you for helping, enderland. – Alex Barrie Jun 05 '13 at 19:06
@AlexBarrie don't call Userform.Userform_Activate" from your code, this is called automatically when you activate the userform (assuming the code is contained within the code for the UserForm). – enderland Jun 05 '13 at 19:07
It wasnt calling it, though. Userform_Initialize (or activate) never got called after my module hit "Userform.Show". I added the above code and now my ListBox populates correctly when the userform is opened. – Alex Barrie Jun 05 '13 at 19:23
@AlexBarrie, what sub did you place the activate in your module? – cquadrini Feb 18 '14 at 20:02

score 1 · Answer 5 · answered Jun 05 '16 at 21:22

1

you have to keep syntax UserForm_Initialize() to make it happen

Cheers

answered Jun 05 '16 at 21:22

Iu To

11
1

I feel like such an idiot! This was the problem for me. I was using TestForm_Initialize() for a form named TestForm. Changed it to UserForm_Initialize() and it works as expected. – PC_Goldman - SE is rotting Mar 08 '18 at 19:27

VBA: Userform initialize method not being hit when userform initializes

5 Answers5