Userform Initialize vs Userform Show

[bbromley]

I am unable to get the Userform Initialize function to work.
I have a commandbutton on Userform4 which I want to use to launch Userform2. I can launch Userform2 using the Show command, but then it bye-passes the Userform2_Initialize() routine (shown below).
I have been told not to put the "2" after "Userform" in the Initialize sub routine, but this does not work either.
Any thoughts?

Private Sub Userform2_Initialize() 
     
    Set wsActive = ActiveSheet 
     
     'Clear controls for next entry and set default box
    TextShares.Value = "" 
    TextPrice.Text = "" 
    TextShares.SetFocus 
     
     'Populate text boxes with data from the spreadsheet
    TextTicker.Text = Range("Sheet1!C4").Value 
    TextCountry.Text = Range("Sheet1!C7").Value 
    TextCurrency.Text = Range("Sheet1!C6").Value 
    TextDate.Text = Range("Sheet1!C8").Value 
    TextExchangeRate.Text = Range("Sheet1!C9").Value 
End Sub 

[Greychild]

Where has this code been placed? The module for userform2 or the module for userform4?

[bbromley]

The command button routine is in the module for UserForm4 (routine shown below) and the UserForm2_Initialize () routine is in the Userform2 module

Private Sub CommandButton11_Click() 
    UserForm2.Show 
End Sub 

[Dave Hawley]

I suspect you are not Unloading the UserForm and only hiding. The Initialize Event ONLY runs when a UserForm is not loaded in memory, using hide does not UnLoad. Use;

Unload Me

To Unload it and then the Event will fire when you Show it.

[bbromley]

Sorry Dave, you've lost me.
Where do I need to put the "Unload Me" command?

[Dave Hawley]

Wherever you have the code to Hide UserForm2

[Dave Hawley]

This may help you understand the difference between Show/Load and Hide/Unload

Show and Load

The method used to launch a UserForm can be a CommandButton placed on a Worksheet, a Custom menu bar, the standard menu bar, shortcut key etc. The method we will assume here is via use of a CommandButton placed on a Worksheet. To achieve this we would go to View> Toolbar>Control Toolbox and place a CommandButton onto a Worksheet. We would then double click the CommandButton to have Excel take us straight to the Click Event of the CommandButton. It is here we would place

Private Sub CommandButton1_Click() 
    UserForm1.Show 
End Sub 

This is the simplest method to load and show a UserForm. By load, I mean load into Excel's memory. It is important to note here, that this is one of the few instances that you cannot refer to the UserForm with the key word "Me". The reason for this is the code for the CommandButton placed on a Worksheet does not and cannot reside in the Private Module for the UserForm itself. If you did use the keyword "Me", you would be referring to the Worksheet Object and not the UserForm Object. This is simply because, the CommandButton on a Worksheet is attached to the Worksheet Object as opposed to the UserForm Object.

The opposite of UserForm1.Show, would be UserForm1.Hide. But there is one very important difference. This is that while UserForm1.Show will automatically load the UserForm into memory, UserForm1.Hide will not unload it from memory. To unload the UserForm from memory, you must use the line of code: "Unload UserForm1". In most, if not all, cases you will use "Unload UserForm1". The only instance you would use UserForm1.Hide would be if your UserForm was extremely complex and took a long time to load. This way you would leave it in memory so that it could be shown again quickly. This would basically mean you would only have to load the UserForm once. The other time you may use UserForm1.Hide as instead of Unload UserForm1 would be when you wanted all the Controls to retain any information that had been added. When you unload a UserForm all Controls will go back to their default settings, while hiding it will retain all current values and settings.

The opposite to "Unload UserForm1" is "Load UserForm1". This will load your UserForm into memory, but will not make it visible. Again, as above, you would probably only use this if your UserForm was very complex.

UserForms Order of Events

The first Event that will fire when you either "Show" or "Load" your UserForm is the "Initialize" Event. This will occur immediately after the UserForm is loaded, but before it is shown (visible). It is important to understand that, if the UserForm was already Loaded (but not visible) the Initialize Event would not fire by using the Show Method. This is because the UserForm would already be loaded into memory. This means that the Show Method will Load a UserForm, if it's not already, but will only make it visible if already loaded.

The next Event that will fire is the "Activate" event. It is important that you know the difference between "Initialize" and "Activate". While "Activate" will occur if you "hide" then re-show a UserForm, the "Initialize" Event will not. This is because the UserForm has not been unloaded from memory. So this means that the Initialize Event will only fire when the UserForm is loaded into memory, while the Activate Event will fire whenever the UserForm is made visible. The order of any Events after this is dependant on the action taken by the user.

[bbromley]

Dave, thanks for your help. I have been through every line of code and have not found any reference to hiding a userform.
I have even added an unload code to the commandbutton to ensure that all userforms are unloaded (code for command button is below)

Private Sub CommandButton11_Click() 
    Unload UserForm1 
    Unload UserForm2 
    Unload UserForm4 
    UserForm2.Show 
End Sub 

Option Explicit 
Dim wsActive As Worksheet 
 
Private Sub Userform2_Initialize() 
     
    Set wsActive = ActiveSheet 
     
     'Clear controls for next entry and set default box
    TextShares.Value = "" 
    TextPrice.Text = "" 
    TextShares.SetFocus 
     
     'Populate text boxes with data from the spreadsheet
    TextTicker.Text = Range("Sheet1!C4").Value 
    TextCountry.Text = Range("Sheet1!C7").Value 
    TextCurrency.Text = Range("Sheet1!C6").Value 
    TextDate.Text = Range("Sheet1!C8").Value 
    TextExchangeRate.Text = Range("Sheet1!C9").Value 
     
End Sub 
 
Private Sub CancelButton_Click() 
     'Turn on calculation functionality
    With Application 
        .Calculation = xlAutomatic 
        .MaxChange = 0 
    End With 
     
     'Unload the userform
    Unload UserForm2 
     
     'Make sure sheet 'Sheet1' is active
    Sheets("Sheet1").Activate 
     
     'Clear cells in tables on Sheet1
    Range("C5:F5").Select 
    Selection.ClearContents 
     
     'Activate original worsheet
    wsActive.Activate 
     
End Sub 

[Andy Pope]

Hi,

Your code is not in the Initialize event routine because of the number 2 in the routines name. This is just a routine in userform2.

To test this goto the first line in that routine and add a break point. With the cursor on the line press F9. This should turn the line red. It will also cause the code to pause at this point.
Now run your program and see if it does indeed pause.

Now remove the 2 from the routine name and try again.

To remove the breakpoint select the line and press F9.

Option Explicit 
Dim wsActive As Worksheet 
 
 ''Private Sub Userform2_Initialize()
[b]Private Sub Userform_Initialize() [/b] 
 
Set wsActive = ActiveSheet 
 
 'Clear controls for next entry and set default box
TextShares.Value = "" 
TextPrice.Text = "" 
TextShares.SetFocus 
 
 'Populate text boxes with data from the spreadsheet
TextTicker.Text = Range("Sheet1!C4").Value 
TextCountry.Text = Range("Sheet1!C7").Value 
TextCurrency.Text = Range("Sheet1!C6").Value 
TextDate.Text = Range("Sheet1!C8").Value 
TextExchangeRate.Text = Range("Sheet1!C9").Value 
 
End Sub 
 
Private Sub CancelButton_Click() 
     'Turn on calculation functionality
    With Application 
        .Calculation = xlAutomatic 
        .MaxChange = 0 
    End With 
     
     'Unload the userform
    Unload UserForm2 
     
     'Make sure sheet 'Sheet1' is active
    Sheets("Sheet1").Activate 
     
     'Clear cells in tables on Sheet1
    Range("C5:F5").Select 
    Selection.ClearContents 
     
     'Activate original worsheet
    wsActive.Activate 
     
End Sub 

[bbromley]

Andy, thanks, I think I understand how this works now.
However, now that I have changed to Private Sub Userform_Initialize() when I use the Userform2.Show command in a commandbutton I get an error message. I seem to be going round in circles. The only way I can activate UserForm2 is to have the 2 in Private Sub Userform2_Initialize(), but then in doing so I circumvent the Initialize routine.
Does it matter where my commandbutton routine resides?