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]

If this works for you it's fine then.

In my previous post I showed you exactly how you could do it.
My script showed no sheet name and I always used just Combobox1

But if you want to name all your comboboxes that's fine

Now there is no need to put in sheet name

The reason I used Sheet name in my original post was because I thought you were only doing this in one sheet and wanted it to happen on Workbook Open

 

[Shadkng]

I thought when you gave me the new code that I was to change it to my needs, and not exactly how it should be. OK so far so good. You'll probably here from me!

 

[My Aswer Is This]

The bottom line is. If you do it your way and it works then that's fine.

Get it working on one sheet and then you should know how to do it on all sheets.

Their is no absolute way this has to be done

 

[My Aswer Is This]

Well you obviously know all the Macro names and the sort would not be exactly like mine.

I thought when you gave me the new code that I was to change it to my needs, and not exactly how it should be. OK so far so good. You'll probably here from me!

 

[My Aswer Is This]

Now see here is another way we could load all the Comboboxes at one time.

When the Workbook is opened we tell this script to run.

This really would be the best way. That way the script only runs once each time you open the Workbook.
This would mean you do not need all the sheet activate portions of your sheet code.

As it is now you said you wanted the script to run when the sheet was activated.

So if the sheet is activated 10 times during the day while you have the workbook open the same script in that sheet runs ten times.


This script if Put into the ThisWook Open

It would only run when the Workbook is opened.
And that would be OK.

Look at my example below

You can Modify sheet names as needed

And Combobox names as needed
You Will see example where one Combobox has a different name.

Sub All_Combo_Boxes()
'Modified 1/31/2019 9:15 AM  EST
'Modify sheet names and Combobox names if you want
With Sheets("One").ComboBox1
.Clear
.AddItem "Alpha"
.AddItem "Bravo"
.AddItem "Charlie"
.Value = "Choose Macro"
End With
With Sheets("Two").ComboBox1
.Clear
.AddItem "Delta"
.AddItem "Echo"
.AddItem "Foxtrot"
.AddItem "Golf"
.Value = "Choose Macro"
End With
'See next Combobox name has a diffent name
With Sheets("Three").ComboxBoxHelp
.Clear
.AddItem "Hotel"
.AddItem "India"
.AddItem "Juliet"
.AddItem "Kilo"
.Value = "Choose Macro"
End With

End Sub

 

[Shadkng]

I understand this logic. I have to think about it because when the workbook is initially opened we don't work on all of the sheets. Later the workbook is opened again and those sheets are worked on. So maybe the best way right now is sheet activate. But at least now you gave me this option to switch to if necessary. Thanks again!

 

[My Aswer Is This]

Yes I understand. I see no reason why it would matter if you worked on them or not.

The Combobox being loaded with values really even if not used would hurt nothing.

But I just provided a new way I thought of.

So is every thing now working OK

 

[Shadkng]

Ok then I'll switch to your suggestion. So the "All Combo" code goes into the "this workbook" module, correct?

 

[My Aswer Is This]

Ok then I'll switch to your suggestion. So the "All Combo" code goes into the "this workbook" module, correct?

Edit

 

[My Aswer Is This]

You would need to remove all you Private Sheet Activate scripts
Leave your All Comboboxes script in your workbook

You need to understand when you use Call that means run this script So:

Call George would run a script named George

Private Sub Workbook_Open()
Call All_Combo_Boxes
End Sub

Do all this on a Copy of your Workbook