Dropdown box on user form to select macros

Hi -

I am new to the forum and between beginner to intermediate knowledge of VBA. I have been searching through the forum but have not been able to find my answer. I have a number of macros that I wrote (30 of them) and assigned them to buttons on my first sheet. I actually need to write many more, so I wanted to remove all of the buttons and use a Userform with a dropdown that showed the macros. The user would select the macro they want to run, be prompted for a confirmation and then the macro would continue to run.

I would really appreciate any help.

Thanks in advance.

An example of the macro names are CreditUnion1, Bank1, etc... The first page has all of the macro buttons, the second page contains data. When the user selects the macro it filters the data on the second page based on institution ID, association ID, Name, etc... After it filters the data it copies the data to a new workbook then saves that to text file on a network drive to be imported into another program then notifies the user that it has completed.

What I am looking to do, or am envisioning, is a Userform with a dropdown, the macros appear in the dropdown. The user selects the macro and has to press a Run button.

Thanks again.

One way to do it.



Create a userform with a single treeview control


Code:

Please Login or Register  to view this content.

Three columns in the Macros worksheet.

Group
Description
Macro

You could skip group if you want to and just go with description and macro. Or just macro for that matter.


The on error resume next is a little cheesy but it works. Would be cleaner to check if the node exists first and only add it if it doesn't exist.


The nice thing about doing it this way is that if you want to add macros, alll you need to do is add an entry in the macros worksheet. Just be careful because the macro name listed in the worksheet must be exact.

Cyclops and Jaslake - Thank you! I tested and will be using the Treeview as Cyclops suggested. I have one more question, how do I set up child nodes. At least that's what I believe they are called. Basically, if I several groups, an example of two groups are Banks and Credit Unions, and they need to be filtered in several different ways. I would like the parent node to say Bank then the child nodes that are related to them to say macro 1, macro 2, etc... and the same set up for Credit Unions.

Thanks in advance.

After a little more playing around with the Treeview and how it populates I figured it out. All I had to do was use the the same group name, put in whatever description I wanted and make sure I had a unique macro name.

I want to thank you all very much.

Sorry for not responding. Thought you had things worked out. The treeview control is pretty cool but there's not a lot of documentation readily available. It's not in the standard help.

The thing I like about pulling the names from the sheet is that it makes it possible for someone that doesn't know anything about code to update the list if new macros are added or old ones removed. It probably also makes it a little easier to update yourself.



I think you can add pictures fairly easily too.

Cyclops -

Thanks again. I ended up wit about 30+ macros and the treeview was the way to go.

FISMitch