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

 

[Shadkng]

Ok I understand the new way but do I need code you put in your last post? Or is it just an example explaining “call”. I assume we don’t need that code because we are removing the activate portion from the worksheet code and putting your code in post #55 into “this workbook” and this will load the combos, correct?

 

[My Aswer Is This]

You need all the code I showed in a Module Script.

Ok.

Now if you want this code to Run when you Open The Workbook all you put in the Workbook Open in this

Private Sub Workbook_Open()
Call AlllScript
End Sub

I'm not sure why your having a hard time understand this.

The workbook open Call Allscript mean run a script named All scripts or what ever name it is I have forgotten now the exact name.

 

[My Aswer Is This]

In Post 60 I shpwed you this:

[LEFT][COLOR=#333333][FONT=monospace]Private Sub Workbook_Open()
Call All_Combo_Boxes
End Sub[/FONT][/COLOR][/LEFT]

If you want show me your script and how you named it and I will tell you how to do it.

If your script with all the code is named HelpMe

Your This Workbbok script would look like this:

Private Sub Workbook_Open()
Call HelpMe
End Sub

 

[My Aswer Is This]

Here is where we were several days ago and I gave you this example:

[LEFT][COLOR=#333333][FONT=monospace]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[/FONT][/COLOR][/LEFT]

So in your This workbook you should enter this:

Private Sub Workbook_Open()
Call All_Combo_Boxes
End Sub

 

[My Aswer Is This]

If you still do not understand.

Show me the code you have written.

 

[My Aswer Is This]

I'm assuming you know what a Module Script is:
It looks like This:

Sub Hello_World()
MsgBox "Wake Up"
End Sub

Now if you want this script to run when you open your Workbook you need this:

Private Sub Workbook_Open()
Call Hello_World
End Sub

Try this in a new Work Book and see how it works.

I want you to use both Of these codes exactly as seen here.

You have to enter the code save your Workbook and Then every time you open the workbook you will see a Message Box popup saving Wake Up

 

[My Aswer Is This]

Here is my logic.

Most people when writing code would write their code and put the code into a Module and then run the code and test it out several times. Then if all works well they may say I want this code to run when I open my workbook.

So instead of copying all the code from the module and put it into the Thisworkbook Open they would just go and put

Call and the name of their Macro.
Into Thisworkbook Open




Another example

Lets say I write some code and want to run the same code in 20 different buttons in my Workbook.

Do I want to copy all that code and put it into 20 different buttons. I would think not.

So if the name of my Macro is George

Then all I have to do is put:

Call George in all of those Buttons


And lets say next week I say I want to change my code a little.
And if My Macro is named George I go into the code and make the change

Now the next time a person presses a button with the Call George in it the Macro Named George runs

But if I put all the code into 20 different buttons and I decide I want to make a change to the code I would have to go into all 20 buttons and change the code.

Test it out and see if what I say is true.

Write some simple code in a Module

Lets say something like this:

Sub Good_Morning_World()
MsgBox "Today is Monday"
End Sub

OK when this Runs it will give you a Message Box Popup Which says "Today is Monday"

So now go put:

Call

Good_Morning_World​

Into 10 different buttons in your Workbook

Now any time you press that buttons it's going to Give you a Popup message saying

"Today is Monday"


Ok now a week later your wife says I do not like that I think the message should say My Baby is Smart

So all you need to do is go back into The module script and change the Message to My Baby is Smart

You only have to do this in the Module script

But next time you click on any of those buttons your going to get a Message saying My Baby is Smart

Can you now see why sometimes it's just best to write some code in a Module and then call it from a button.


​

 

[Shadkng]

Hi, I read through all your posts carefully and I have a better understanding. As yOu realized by now, code is not my strong point yet. One day I will start from scratch and learn it the right way. Anyway, I have it working your new way - see below all my code.

Question, any code that runs when the workbook is opened must go in the "thisworkbook" module? Any you can have many subs in there?

Private Sub ComboBoxMast_change()
 With Application
    Select Case ComboBoxMast.Value
    Case "HIDE ADAM 4 FORMS"
        .Run "HIDE_ADAM_4_FORMS"
    Case "UN-HIDE ADAM 4 FORMS"
        .Run "UNHIDE_ADAM_4_FORMS"
    Case "PRINT ADAM 4 FORMS"
        .Run "PRINT_ADAM_4_FORMS"
    Case "PRINT RANGE"
        .Run "PRINT_RANGE"
    Case "AUTO PRINT RANGE"
        .Run "PRINT_RANGE_AUTO_PREVIEW_ADAM"
    Case "HIDE"
        .Run "MAST_PROD_FORM_HIDE_ALL"
    Case "UNHIDE"
        .Run "MAST_PROD_FORM_UNHIDE_ALL"
    Case "COPY ROW"
        .Run "MAST_PROD_FORM_COPYROW"
    End Select
End With
ComboBoxMast.Value = "CHOOSE MACRO"
End Sub

Private Sub ComboBoxTab_change()
 With Application
    Select Case ComboBoxTab.Value
    Case "HIDE JIM 4 FORMS"
        .Run "HIDE_JIM_4_FORMS"
    Case "UN-HIDE JIM 4 FORMS"
        .Run "UNHIDE_JIM_4_FORMS"
    Case "PRINT JIM 3 FORMS"
        .Run "PRINT_JIM_3_FORMS"
    Case "PRINT RANGE"
        .Run "PRINT_RANGE"
    Case "AUTO PRINT RANGE"
        .Run "PRINT_RANGE_AUTO_PREVIEW_JIM"
    Case "HIDE"
        .Run "TABLE_PROD_HIDE_ALL"
    Case "UNHIDE"
        .Run "TABLE_PROD_UNHIDE_ALL"
    Case "COPY ROW"
        .Run "TABLE_PROD_COPYROW"
    End Select
End With
ComboBoxTab.Value = "CHOOSE MACRO"
End Sub

Sub All_COMBO_BOXES()
'Modified 1/31/2019 9:15 AM  EST
'Modify sheet names and Combobox names if you want
With Sheets("MASTER FORM").ComboBoxMast
.Clear
.AddItem "HIDE ADAM 4 FORMS"
.AddItem "UN-HIDE ADAM 4 FORMS"
.AddItem "PRINT ADAM 4 FORMS"
.AddItem "PRINT RANGE"
.AddItem "AUTO PRINT RANGE"
.AddItem "HIDE"
.AddItem "UNHIDE"
.AddItem "COPY ROW"
.Value = "Choose Macro"
End With
With Sheets("TABLE PROD").ComboBoxTab
.Clear
.AddItem "HIDE JIM 4 FORMS"
.AddItem "UN-HIDE JIM 4 FORMS"
.AddItem "PRINT JIM 3 FORMS"
.AddItem "PRINT RANGE"
.AddItem "AUTO PRINT RANGE"
.AddItem "HIDE"
.AddItem "UNHIDE"
.AddItem "COPY ROW"
.Value = "Choose Macro"
End With
End Sub

Private Sub Workbook_Open()
Call All_COMBO_BOXES
End Sub

 

[My Aswer Is This]

Yes what you have now looks correct. And are saying everything works well now?

I want to know if everything now works well before we talk about other things.

So when you go into your Combobox and choose a Name the proper Macro runs is that correct?

 

[My Aswer Is This]

Now give me a example of another script that you may want to run when workbook is opened.

Show me the code.

And Explain what this code is suppose to do.
See it may be dependent on what the code is suppose to do and how the code is written.

But please be sure and answer my previous question about are your combobox scripts doing what they are suppose to do.

It now sounds like you are talking about a completely different thing.


I want to be sure we have your previous question which we have been working on now for nearly a week completed before we get involved in something else.