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]

Or do it like this:
The reason this keeps changing is because first you asked for this to happen when Workbook opened.
And I did not know you were going to have comboboxes on several different sheet all with different Macros.
But hey will get there.

Private Sub Worksheet_Activate()
'New
With ActiveSheet.ComboBox1
.Clear
.AddItem "Print Report"
.AddItem "Mom"
.AddItem "Bob"
.AddItem "George"
.AddItem "Stanley"
.AddItem "Julia"
.Value = "Choose Macro"
End With
End Sub
Private Sub Combobox1_Click()
'Modified  1/30/2019  6:56:14 AM  EST
'Emter the name of the Macro here
'So in this example if user clicks on Dad in list box then Macro named "One" will run
With Application
    Select Case ComboBox1.Value
    Case "Print Report"
        .Run "PRINT_RANGE"
        Case "Mom"
        .Run "One"
    End Select
End With
ComboBox1.Value = "Choose Macro"
End Sub

 

[Shadkng]

Now I am confused. This code in post#41 should replace my private worksheet code? It looks like you combined the code that I have in Listbox (reg module) with the private worksheet code. So then the listbox script gets eliminated? Does this avoid me having to have a new listbox script for each sheet?

 

[My Aswer Is This]

Yes if you do it this way you do not need

Listbox (reg module)​

All the code is in the sheet

You said:

This code in post#41 should replace my private worksheet code?

If you look at the code you see it's still here.

​

[COLOR=#ff0000][LEFT][FONT=Verdana][LEFT]Private Sub Worksheet_Activate()
'New[/LEFT]
[/FONT][/LEFT]
[/COLOR][LEFT][COLOR=#333333][FONT=Verdana][LEFT][COLOR=#333333][FONT=monospace]
With ActiveSheet.ComboBox1
.Clear
.AddItem "Print Report"
.AddItem "Mom"
.AddItem "Bob"
.AddItem "George"
.AddItem "Stanley"
.AddItem "Julia"
.Value = "Choose Macro"
End With
End Sub
Private Sub Combobox1_Click()
'Modified  1/30/2019  6:56:14 AM  EST
'Emter the name of the Macro here
'So in this example if user clicks on Dad in list box then Macro named "One" will run
With Application
    Select Case ComboBox1.Value
    Case "Print Report"
        .Run "PRINT_RANGE"
        Case "Mom"
        .Run "One"
    End Select
End With
ComboBox1.Value = "Choose Macro"
End Sub[/FONT][/COLOR][/LEFT]
[/FONT][/COLOR][/LEFT]

​

 

[Shadkng]

OK so Listbox code goes away, so I will code in Private sub worksheet and code in the "this workbook" module. I will try it later and let you know.

 

[My Aswer Is This]

I never said "This Workbook"

I said put the code in your sheet.

Right click on each sheet and enter the code you want for that sheet.

The Thisworkbook was back two days ago when you said you wanted the script to run when the workbook opened.

The new way you said you wanted was when the Worksheet was activated.
Which is what we must have now that each sheet will have different code

 

[My Aswer Is This]

You see here Worksheet Activate

Which means when this sheet is activated
When this sheet is activated do this:

Private Sub Worksheet_Activate()



​

 

[My Aswer Is This]

I normally think when users are not familiar with Vba they should start out with simple things.

Just so you will see how sheet activate script work.

Right click on your sheet tab select view code
And paste in this code

Now the next time you select activate or open how ever you want to call it This sheet you will be greeted with Hello

Private Sub Worksheet_Activate()
MsgBox "Hello"
End Sub

 

[Shadkng]

Yeah I got it now. I'm getting there.

 

[My Aswer Is This]

Well glad to see your learning how this works. Let me know when you have this all working. Or if you need more help.

Yeah I got it now. I'm getting there.

 

[Shadkng]

OK, I made the latest change and all good so far. See my newest code below, and questions next to lines.

Does the combobox need a unique name for each sheet since the code will now reside in each sheet?

Thanks

Private Sub Worksheet_Activate()
[B]With Sheets("MASTER FORM").ComboBoxMast  [/B]  Should this line read[COLOR=#333333][FONT=Verdana][COLOR=#333333][FONT=monospace] "With ActiveSheet.ComboBoxMast?  Do have to name the sheet if we are in it?[/FONT][/COLOR][/FONT][/COLOR]
.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
End Sub
[B]Private Sub ComboBoxMast_change() [/B]  Should this line have  [I]_change[/I] or [I]_click[/I]?
 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 ROWS"
        .Run "MAST_PROD_FORM_COPYROW"
    End Select
End With
ComboBoxMast.Value = "CHOOSE MACRO"
End Sub