Userform Initialize vs Userform Show

Re: Userform Initialize vs Userform Show

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.

Re: Userform Initialize vs Userform Show

Wherever you have the code to Hide UserForm2

Re: Userform Initialize vs Userform Show

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.

Re: Userform Initialize vs Userform Show

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

Re: Userform Initialize vs Userform Show

Depends what the error message is. Is it complaining about Ambigous name detected?
If so then you need to remove one of the Userform_initialize events. Presumably the one without code in it.

The commandbutton should be ok.

Any chance you can post your workbook?

Re: Userform Initialize vs Userform Show

I think you are seeing the error because this is the first time your code has actually run.

Can you step thru each line of the Initialize routine to determine which actual line causes the type mismatch?

Is the activesheet a worksheet? It's not a chartsheet by an chance.

Re: Userform Initialize vs Userform Show

bbromley, set up a standard module (Insert>Module) with the code

Sub ShowNum2Form()
    UserForm2.Show
End Sub

Then for the CommandButton (If ActiveX) that Shows UserForm2 use

Private Sub CommandButton1_Click()
    Run "ShowNum2Form"
End Sub

Re: Userform Initialize vs Userform Show

Hi,

This should get your Userform2 displayed.
See inline comments as to what is causing problems.

Sub Userform_Initialize()
    
    'Clear controls for next entry and set default box
    
    
    ' TextShares is linked to worksheet
    Range(TextShares.ControlSource).Value = ""
'    TextShares.Value = ""
    TextPrice.Text = ""
   
    'Populate text boxes with data from the spreadsheet


'TextTicker already has controlsource set to Sheet1!C14
'    TextTicker.Text = Range("Sheet1!C4").Value


' Range C7 is a lookup formula that is currently #N/A
' same test applied to subsequent controls
    If Application.WorksheetFunction.IsNA(Range("Sheet1!C7")) Then
        TextCountry.Text = ""
    Else
        TextCountry.Text = Range("Sheet1!C7").Value
    End If
    
    If Application.WorksheetFunction.IsNA(Range("Sheet1!C6")) Then
        TextCurrency.Text = ""
    Else
        TextCurrency.Text = Range("Sheet1!C6").Value
    End If
    If Application.WorksheetFunction.IsNA(Range("Sheet1!C8")) Then
        TextDate.Text = ""
    Else
        TextDate.Text = Range("Sheet1!C8").Value
    End If
    
    If Application.WorksheetFunction.IsNA(Range("Sheet1!C9")) Then
        TextExchangeRate.Text = ""
    Else
        TextExchangeRate.Text = Range("Sheet1!C9").Value
    End If
    
    TextShares.SetFocus


End Sub

Re: Userform Initialize vs Userform Show

The problem is not with your use of Userform_Initialize() that is the correct format. The problem is most likely within the code you have WITHIN Userform_Initialize()

When the commandbutton in UserForm4 is clicked it calls

UserForm2.Show - which does the following things
- Check To See If UserForm2 has already been loaded
- If Yes Then Display It On The Screen
- If No Load Userform 2 (Which calls UserForm_Initialize()) and then display it

Having UserForm_Initialize() in the module for userform 2 is what you want to do so it calls code when UserForm2 is loaded for the first time.

The problem is that you needed to troubleshoot the error that was occuring when UserForm_Initialize() is called rather than rename it.

The error is most likely occuring because of all of your code right here:

TextShares, TextPrice, TextTicker are presumably text fields on UserForm2 correct? In the UserForm_Initialize procedure the form is just being loaded which means subcomponents like TextShares haven't been initialized yet. If TextShares hasn't been initialized you can't set it's members like .Value.

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

To change these values like TextShares.Value you have to wait till they have been loaded (initialized has completed).

Instead of using UserForm_Initialize you want to use UserForm_Activate (activate is called after all of the form and its subcomponents have been initialized).

Re: Userform Initialize vs Userform Show

Welcome to the forums, RhinoT.

Thank you for providing a possible solution, but have you noticed the thread is over 5 years old? However, the original poster is still active on the board so perhaps he will have a solution at last

One other thing. All code listings must be wrapped in code tags. You agreed to these rules when you joined the forum, but it might be useful if you click here and have another read. I will add the code tags for you, this time.

Re: Userform Initialize vs Userform Show

Hi everyone,
New to these forums. However did have the same problem that the "userform_initialise()" sub did not seem to activate. Solved it as below, probably not supposed to paste code into this box.

Regards John Connelly

Sub WAGES_SHOW()
'
test = initialise()
WagesCalculator.Show

End Sub

Function initialise()
WagesCalculator.ComboBoxRates.AddItem "40.33" 'ListIndex = 0
WagesCalculator.ComboBoxRates.AddItem "41.31" 'ListIndex = 1
WagesCalculator.ComboBoxRates.AddItem "42.41" 'ListIndex = 2
WagesCalculator.ComboBoxRates.AddItem "44.48" 'ListIndex = 0
WagesCalculator.ComboBoxRates.AddItem "46.16" 'ListIndex = 1
WagesCalculator.ComboBoxRates.AddItem "36.74" 'ListIndex = 2
'Combo box values are column(1) values.
WagesCalculator.ComboBoxRates.BoundColumn = 1
'Set combo box to first entry
WagesCalculator.ComboBoxRates.ListIndex = 0

End Function