Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 18

Thread: Userform Initialize vs Userform Show

  1. #1
    Join Date
    30th January 2005
    Posts
    123

    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?

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

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Greychild Guest

    Re: Userform Initialize vs Userform Show

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

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    30th January 2005
    Posts
    123

    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

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

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,718

    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.

  5. #5
    Join Date
    30th January 2005
    Posts
    123

    Re: Userform Initialize vs Userform Show

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

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,718

  7. #7
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,718

    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
    VB:
    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.

  8. #8
    Join Date
    30th January 2005
    Posts
    123

    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)

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

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    7th March 2003
    Location
    Essex, England
    Posts
    11,310

    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.

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

    Cheers
    Andy


  10. #10
    Join Date
    30th January 2005
    Posts
    123

    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?

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Set Focus To TextBox On UserForm Initialize
    By Mentor in forum EXCEL HELP
    Replies: 2
    Last Post: March 8th, 2008, 04:25
  2. Set Up Multi Page To Mimic Userform Initialize
    By nu@this in forum EXCEL HELP
    Replies: 4
    Last Post: August 2nd, 2007, 02:37
  3. Cancel UserForm Initialize
    By Reafidy in forum EXCEL HELP
    Replies: 8
    Last Post: December 19th, 2006, 09:20
  4. Fill UserForm ListBox & Show Userform
    By JFrench in forum EXCEL HELP
    Replies: 7
    Last Post: November 14th, 2006, 04:31
  5. UserForm initialize code closes UserForm
    By RPIJG in forum EXCEL HELP
    Replies: 6
    Last Post: May 10th, 2006, 20:50

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno