Results 1 to 15 of 15
  1. #1
    2 Star Lounger
    Join Date
    Apr 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts
    i have a user form (vba) that inputs data into the spreedsheet. but when i exit out of the userform and go into the spreedsheet there is no way for me to bring back up the userform. I have to close out of what i'm doing and reopen it. there has got to be a better way of doing this?

  2. #2
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Depends where the Userform is and if it is still available to use.
    Is it part of the same spreadsheet or is it in another one.

    If it is in the same workbook, then a button with something like

    NameOfUserForm.Show as its click event would do

    Best way is to create a Manual Macro in a Module
    with that code in it and then assign it to a button on the sheet.
    e.g

    Code:
    Sub OpenUserForm
         NameOfUserForm.Show
    End Sub
    Cannot help more than that because I do not know from where the form is being called.
    Andrew

  3. #3
    New Lounger
    Join Date
    Dec 2009
    Location
    Woodbury, GA
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Andrew is right on about using show. *To add a thought to that, instead of closing the form as you apparently are now when you think you're through with it, use hide instead. *As Andrew said you can then click on a button to show it again and it should remain populated with the data it had when you hid it.

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    FYI, you should not really use the default userform instance - it's a class and should be handled as one. So rather than:

    Code:
    Userform1.Show
    you should really use something like:

    Code:
    Dim frm as Userform1
    
    Set frm = New Userform1
    
    frm.show



    For a casual one line bit of code, you can get away with using the former syntax, but for anything more complicated, it's better to do things properly, not least because it will make it easier to debug.

    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    New Lounger
    Join Date
    Aug 2010
    Location
    New Zealand
    Posts
    13
    Thanks
    11
    Thanked 1 Time in 1 Post
    Mark how would you hide the userform ? I have one I would like to hide while I check my sheet. Thank you , Lyn

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    From within the form:
    Code:
    Me.Hide
    in whatever routine you want it.
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    371
    Thanked 1,457 Times in 1,326 Posts
    Quote Originally Posted by rory View Post
    FYI, you should not really use the default userform instance - it's a class and should be handled as one.
    For a casual one line bit of code, you can get away with using the former syntax, but for anything more complicated, it's better to do things properly, not least because it will make it easier to debug.
    Rory,

    This intrigued me. I did a little testing and I now understand that a defined form is a Class a useful piece of information. So if I have a form named ufCarMenu I should:

    Code:
    Sub ShowMenu()
       Dim frm as ufCarMenu
       Set frm = New ufCarMenu
       frm.show
    End Sub
    This would also allow me to have a second copy {instance} of the form open as follows:

    Code:
    Sub ShowMenu()
       Dim frm2 as ufCarMenu
       Set frm2 = New ufCarMenu
       frm.show2
    End Sub
    Correct?

    The above is just for clarification for both myself and others who might have been confused in thinking that UserForm1 was a data type vs the default name for a newly created form {which should always be renamed since we're discussing good coding practices here}.

    Would it also be good coding practice to clear the object frm, i.e. Set frm = Nothing, upon closing the form?

    What I still don't understand is how this syntax assists in debugging? Could you please elaborate.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Correct. In fact, Userform1 is a class, an interface and the name of a default instance of that class. (strictly speaking, when you declare the variable As Userform1, you are saying that it implements the Userform1 interface, not that it necessarily is a Userform1 instance, but that's not really the point here!)

    Typical (simplified) example of the sort of problem I see frequently in the forums and newsgroups:
    Code that loads and uses the form:
    Code:
    Userform1.Caption = "my new form caption"
    Userform1.Show
    msgbox userform1.caption
    but the form itself either uses Unload rather than Hide, or the user clicks the X to close it, so the message actually says "Userform1" and the programmer can't figure out why. It is because the use of Userform1.Caption actually silently creates a new instance of the form. If you use a variable, you will get an error which lets you know your form has been destroyed, and you can then work out why. On that subject, when you use variables, you should ensure that the user can only hide the form, not unload it - you do that in the calling code by setting the variable equal to Nothing.

    Does that make sense?
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    371
    Thanked 1,457 Times in 1,326 Posts
    Quote Originally Posted by rory View Post
    you should ensure that the user can only hide the form, not unload it - you do that in the calling code by setting the variable equal to Nothing.

    Does that make sense?
    Rory,

    Yes it does make very good sense. A follow on question from the quote above. Why not allow the user to unload the form? Is this for efficiency or some other reason? If it is for efficiency of re-displaying the form would it them be acceptable {i.e. good coding practice} to allow the user to unload a form that is only used in the initialization of the setup, e.g. selecting a file or entering a password?
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Two reasons really:
    1. Your code created the form, therefore your code should destroy it.
    2. If you need to use any of the information from the form in your code after showing it, you need to keep it loaded. Even if you don't currently use the information, you may change the program later, and it's simpler if you have already ensured that your code is the only place it can be unloaded.

    Obviously this is just stuff that I consider to be "best practice" - others may have different opinions, and for quick and dirty apps, there may be no need to go to all that trouble (though I tend to find that even those apps tend to take on a life of their own, far beyond my original intentions!
    Regards,
    Rory

    Microsoft MVP - Excel

  11. #11
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    371
    Thanked 1,457 Times in 1,326 Posts
    Quote Originally Posted by rory View Post
    (though I tend to find that even those apps tend to take on a life of their own, far beyond my original intentions!
    Rory,

    I'll second that! I've built one-ups for quick and dirty answers that lasted for years while I was working. It's always best to plan for the future because it always comes!

    Thanks for sharing your insights I really appreciate it as I'm a self taught VBA guy and always looking for a "better way".
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  12. #12
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    And just for completeness

    Sub ShowMenu()
    Dim frm2 as ufCarMenu
    Set frm2 = New ufCarMenu
    frm.show2
    End Sub

    I think you meant:
    Sub ShowMenu()
    Dim frm2 as ufCarMenu
    Set frm2 = New ufCarMenu
    frm2.show
    End Sub


    zeddy

  13. #13
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    371
    Thanked 1,457 Times in 1,326 Posts
    Yes, just the fingers lagging behind the cranium!
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  14. #14
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    371
    Thanked 1,457 Times in 1,326 Posts
    Hello Again,

    To continue this discussion of Userform as Class...

    I was modifying one of my projects to use this coding technique when I noticed a significant difference in this technique.
    The first time you show the form the UserForm_Initialize event is fired. However, when you Me.Hide the form then once again do a frm.Show the UserForm_Intitialize event is NOT fired!

    I had to change to the UserForm_Activate event to get my form setup code to run every time the form is shown. Is this how it should be or did I do something else wrong?

    Update: Also...the userform retains it's data so you need to clear the fields before the next use, which can be done in the UserForm_Initialize event handler.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  15. #15
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    That's how it should be and is the same whichever syntax you use. The Initialize event occurs when the form instance is created and loaded into memory. When you show and hide the form, the activate and deactivate events are triggered. All you need to is set the form variable to nothing and reinitialise it to create a new "fresh" instance.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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