Announcement

Collapse
No announcement yet.

Userform Initialize vs Userform Show

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Userform Initialize vs Userform Show



    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?

    Code:
    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

  • #2
    Re: Userform Initialize vs Userform Show

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

    Comment


    • #3
      Re: Userform Initialize vs Userform Show

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

      Code:
      Private Sub CommandButton11_Click()
      UserForm2.Show
      End Sub

      Comment


      • #4
        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.

        Comment


        • #5
          Re: Userform Initialize vs Userform Show

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

          Comment


          • #6
            Re: Userform Initialize vs Userform Show

            Wherever you have the code to Hide UserForm2

            Comment


            • #7
              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
              Code:
              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.

              Comment


              • #8
                Re: Userform Initialize vs Userform Show

                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)

                Code:
                Private Sub CommandButton11_Click()
                Unload UserForm1
                Unload UserForm2
                Unload UserForm4
                UserForm2.Show
                End Sub
                Code:
                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

                Comment


                • #9
                  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.

                  Code:
                  Option Explicit 
                  Dim wsActive As Worksheet 
                   
                  ''Private Sub Userform2_Initialize() 
                  Private Sub Userform_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

                  Cheers
                  Andy

                  Comment


                  • #10
                    Re: Userform Initialize vs Userform Show

                    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?

                    Comment


                    • #11
                      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?

                      Cheers
                      Andy

                      Comment


                      • #12
                        Re: Userform Initialize vs Userform Show

                        When I use commandbutton with the code Userform2.Show I get a runtime error '13'. Type mismatch.
                        If I change it to userform1.show then it shows Userform1 no problem.
                        I would love to post the workbook, but unfortunately its a 2MB file!

                        Comment


                        • #13
                          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.

                          Cheers
                          Andy

                          Comment


                          • #14
                            Re: Userform Initialize vs Userform Show

                            Andy, thanks for your efforts to help me. I think I'm going to give up on this one now though. I have been through every conceivable solution and nothing works.
                            The issue is simply that when I use the command button to call Userform2, unless I have the number 2 in Userform2_Initialize() then the commandbutton does not find Userform2. So when I have Userform_Initialize() as the subroutine, clicking the command button results in a Run Time error '13' with the line UserForm2.Show line highlighted in yellow. If I put the 2 into Userform2_Initialize() then it loads userform2 fine, but the Initialize routine is bye-passed.
                            Anyhow, thanks for your time, very much appreciated.

                            Comment


                            • #15


                              Re: Userform Initialize vs Userform Show

                              bbromley, set up a standard module (Insert>Module) with the code
                              Code:
                              Sub ShowNum2Form()
                                  UserForm2.Show
                              End Sub
                              Then for the CommandButton (If ActiveX) that Shows UserForm2 use
                              Code:
                              Private Sub CommandButton1_Click()
                                  Run "ShowNum2Form"
                              End Sub

                              Comment

                              Working...
                              X