Macro Pop Up

[Smonczka]

I am looking for a way to create a pop up that would ask the user if
they would like to run a macro.

The worksheet has several macros that will alter the sheet in several
ways, either by saving as a new file or altering the formatting.
Users will only want to run one of these macros at any one time. I'm
trying to find a reference that would show me how to create a pop up
window that would list the available macros in the sheet and ask the
user which one they would like to run.

Thanks for any help you can give,
Steve

 

[Bob Flanagan]

The quick way to see the macros is to press ALT-F8. If you want to control
what they see, you can create a macro that displays a list box of macros, or
a set of option buttons that list the macros. Once a selection is made, it
can execute the macros.

You can assign the above macro to a button or to a menu item on say the
Tools menu for easy access.

If it is likely that you would be modifying the macros, you should consider
putting the macros in an workbook that is an add-in (a property setting of a
workbook) so that you can do replacements easily.

Bob Flanagan
Macro Systems
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

 

[merjet]

When is it suppose to pop up?

Merjet

 

[Smonczka]

When the worksheet is first opened. I figured i would use an On Open
comand to invoke the pop up, but I'm still not sure how to create the
pop up in the first place and then how to get the pop up to reference
the embeded macros in that workbook.

Thanks,
Steve

 

[Smonczka]

Bob Yes I am trying to control what the the users sees and the list
box of available macros would be perfect but I have yet to find any
reference on how to do this.

 

[merjet]

Add a UserForm. Put a ListBox on it. Put a Label to
suit. In the UserForm's code module put:

Private Sub UserForm_Activate()
myArray = Array("Macro1", "Macro2", "Macro3")
ListBox1.List = myArray
End Sub

Private Sub ListBox1_Change()
If ListBox1.ListIndex > -1 Then
Application.Run ListBox1.Value
End If
End Sub

In the ThisWorkbook code module put:

Private Sub Workbook_Open()
UserForm1.Show
End Sub

Hth,
Merjet

 

[Smonczka]

Merjet

Thank you very much, that works perfectly. I mixed that with a few
other things i found in the group and now I have a full blown app.

Thanks so much for your help.

Steve

 

[Smonczka]

Merjet, is it posible to use a ComboBox instead of a ListBox?

thanks,
Steve

 

[merjet]

Merjet, is it posible to use a ComboBox instead of a ListBox?

Yes, but the code would need to deal with the user entering something
you don't want. Other than that, the coding is much the same. It would
probably be better to set its Style property to 2.

Merjet

 

[Smonczka]

Merjet thank you for your time, you have been very helpful.

Steve