going from excel spreedsheet to vba userform

Topic

New Reply

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?

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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!
Computer Specs

Reply | Quote

Mark how would you hide the userform ? I have one I would like to hide while I check my sheet. Thank you , Lyn

Reply | Quote

From within the form:

Code:

Me.Hide

in whatever routine you want it.

Reply | Quote

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?

Reply | Quote

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!
Computer Specs

Reply | Quote

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!

Reply | Quote

(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!
Computer Specs

Reply | Quote

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

Reply | Quote

Yes, just the fingers lagging behind the cranium!

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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!
Computer Specs

Reply | Quote

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.

Reply | Quote