Running macros in a form control list box

[Shadkng]

I have been searching the internet for hours and I cannot seem to find a clear method for the following. I would like to have several macros listed in a list box that when selected will run. I currently have a bunch of form control buttons on my sheets and it's getting messy. I am able to do it easily using a data validation list box and naming the macros in the code which I found online. However, I would like to make the list using a form control list box - I guess an active-x list box so I can have more control over the size, font, etc. I know how to make the active-x list box and name the range where the macros are listed, but I don't know what code to use. If there is another approach to solve my issue I am certainly open to it. Thanks

 

[My Aswer Is This]

Re: Help with running macros in a form control list box

Assuming your using Activex listbox

Load you listbox with Macro names like this

Assuming your list of Macro Names are in Range("A1:A4")

Private Sub CommandButton1_Click()
'Modified  1/26/2019  9:09:38 PM  EST
ListBox1.Clear
ListBox1.List = Range("A1:A4").Value
End Sub

Now put this script in your ListBox

Private Sub ListBox1_Click()
'Modified  1/26/2019  9:09:38 PM  EST
On Error GoTo M
Dim ans As String
ans = ListBox1.Value
Application.Run ans
Exit Sub
M:
MsgBox "The Macro named  " & ans & "  Does not exist"
End Sub

When you click on a name in listbox1 that Macro will run

 

[My Aswer Is This]

Re: Help with running macros in a form control list box

My previous post assumes your Macros are Module Macros and not Private Macros.

I do not know how to do what you want using Private Macros.

 

[Shadkng]

Re: Help with running macros in a form control list box

I don't know the difference between module macros and private. I just go into design mode and select the active x list box.

Regarding your 2 sets of code, I don't know where to put them. I see you can enter code into the button itself, so does the code on top go in there? Then where does the bottom code go - into the sheet?

 

[My Aswer Is This]

Re: Help with running macros in a form control list box

The code I provided you copy and then paste it into your control or button as you may want to call it.

I really do not know how to explain it better.

Copy the code I provided and paste it into the control

And you said:

active x list box.​

But your subject title says

macros in a form control list box

So I'm not sure if your using activex controls or Form controls.

Show me some of your Macros.
​

 

[Shadkng]

Re: Help with running macros in a form control list box

I think maybe I am using the wrong terminology, but I am also asking what is the best type of list box to use. I mentioned active x because that seemes like the best one to use that would allow me to change the properties. You offered 2 sets of code so I wasn't sure if I an supposed to use one or both. It looks like one is for a command button which I don't think I want. If you could explain in more detail I would appreciate it.

Thanks a lot.

 

[My Aswer Is This]

Re: Help with running macros in a form control list box

Well if you want a listbox to be loaded with all your macro names. And you do not want to use a command button to load these macro names into the listbox. How do you plan to load the macro names into the listbox?

And I still need to see one of these macros.

 

[My Aswer Is This]

Re: Help with running macros in a form control list box

If you have ever used a listbox or combobox before and have values loaded into the Listbox and or Combobox before and not used a command button to load values into them I'm open for a new way of doing things.

Now there are a lot of other ways I'm sure. So tell me how do you want to load the listbox with Macro Names.

If you want to click on a value in a listbox to launch a Macro the Macro name has to some how get loaded into the listbox.

I always use Activex controls.

If you use the Macro Recorder or Visual Basic editor to create your macros these are referred to as Module Macros.

A Module script has a name like One or Two or what ever.

Private Macros have names like:

Private Sub CommandButton1_Click()

 

[Akuini]

Re: Help with running macros in a form control list box

You can use Private Sub ListBox1_GotFocus() to populate the list to the listbox.
Another option, use Private Sub Worksheet_Activate()

 

[My Aswer Is This]

Re: Help with running macros in a form control list box

Here is another way.

Right click on your sheet tab
Choose View code
Paste in the script

Now the listbox will be load when the sheet is activate
Which means you have to select this sheet,

Private Sub Worksheet_Activate()
'Modified  1/27/2019  1:16:35 AM  EST
ListBox1.Clear
ListBox1.List = Range("A1:A4").Value
End Sub