toolbar button for user form

[Diane]

Group,
Normally when creating vba code, I'll start with a recorded macro and then
customize the code as needed. Once code is completed, (if needed) I will add
a toolbar button for the macro, i.e....tools, customize, commands, and from
the list of macros, create my button - works great. In this situation, I did
not "record" my macro, but instead within the VBA editor, created a user
form, completed my code and all is good. Now....I need to assign this user
form to a toolbar button, although, with using the same steps as above, my
form is not listed in the macro list, this makes sense to me since I did not
initially create a "macro" but instead developed a user form. I'm not sure
how to create my toolbar button when the user form is not in any list to
choose from. Creating the toolbar button will complete my project.

Any suggestions will be appreciated.

 

[Jay Freedman]

Creating a userform is like creating a template: you have to make an actual
object based on the userform, and you do that in a regular macro.* That
regular macro is what you can assign to a toolbar button.

I'll assume you kept the default name for a userform, "UserForm1", although
it's a good idea to rename the userform to reflect its purpose.

So... in the same template where you saved the userform, insert a regular
module (Insert > Module). In that module, create a macro -- you can't record
this, because there's no action to record -- named to reflect what the
userform does. It should have code like this:

Sub LaunchMyUserForm()
Dim myForm As Userform1
Set myForm = new UserForm1 ' this makes the actual object

myForm.Show ' this displays the object

' the macro is suspended here while the userform code executes
' until the userform does either Unload Me or Me.Hide

Set myForm = Nothing ' this clears the memory set up by the 'new'
End Sub

Then assign a toolbar button to the LaunchMyUserForm macro.

* The VBA editor confuses the issue by letting you run a userform directly
from the editor without a macro. But that's the only way to run a userform
without a separate macro.

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.

 

[Diane]

Jay,
This is exactly what I needed and I could easily follow your example. Thank
you for providing such detailed information!!
Diane