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

Ok.

Your correct script here is a Module scripit

So if you use my code I provided today.

PRINT_RANGE Is the name of the Macro


So do you understand how to do what I provided earlier today?




​

 

[My Aswer Is This]

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

So here is how your two script should look

In the list Box you will see Print Report

And the Macro Print_RANGE will run

Change Print Report to what you want

Private Sub CommandButton1_Click()
'Modified  1/28/2019  8:39:15 AM  EST
'Enter the value you want to see in the list box here
With ListBox1
.Clear
.AddItem "Print Report"
.AddItem "Mom"
.AddItem "Bob"
.AddItem "George"
.AddItem "Stanley"
.AddItem "Julia"
End With
End Sub

Private Sub ListBox1_Click()
'Modified  1/28/2019  8:39:15 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 ListBox1.Value
    Case "Print Report"
        .Run "PRINT_RANGE"
    End Select
End With
End Sub

 

[Shadkng]

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

Hi, so I went ahead and followed your last set of instructions, and it's working as you explained. Question - when I enter the code into the command button, and then I go enter the code in the listbox, I see the other code there. So I assume both codes will be in each of those buttons? Also, I noticed that when right click on the sheet name and view code - the codes are there also. Is that correct?

I also experimented using an activeX combo box instead of a listbox by changing the code from listbox1 to combobox1 and it seemed to work OK. I like the combobox better.

Below is my code as modified using the combobox. OK so there is a way to eliminate the command button? I think I have read that when you open the workbook it populates the list or when you go to the sheet?

Thanks

Private Sub CommandButton1_Click()
'Modified  1/28/2019  8:39:15 AM  EST
'Enter the value you want to see in the list box here
With ComboBox1
.Clear
.AddItem "HIDE"
.AddItem "UNHIDE"
.AddItem "PRINT RANGE"

End With
End Sub

Private Sub ComboBox1_Click()
'Modified  1/28/2019  8:39:15 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 "HIDE"
        .Run "MAST_PROD_FORM_HIDE_ALL"
    Case "UNHIDE"
        .Run "MAST_PROD_FORM_UNHIDE_ALL"
    Case "PRINT RANGE"
        .Run "PRINT_RANGE"
    End Select
End With
End Sub

 

[My Aswer Is This]

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

You question was:

I noticed that when right click on the sheet name and view code - the codes are there also. Is that correct?​

Yes that's how it should look.

I thought a Combobox would be best myself.

Here is how I think you should eliminate the Command Button.

Put this script in a Module Macro

I believe you now know what a Module Macro is

Sub Load_ListBox()
'Modified  1/29/2019  8:44:16 AM  EST
'This assumes yor Combobox is in a sheet named "Mom"
'Modify sheet name as needed
With Sheets("Mom").ComboBox1
.Clear
.AddItem "Print Report"
.AddItem "Mom"
.AddItem "Bob"
.AddItem "George"
.AddItem "Stanley"
.AddItem "Julia"
End With
End Sub

Now Right click on sheet Tab choose View code

Double click on ThisWorkbbok

And put in this code:

Private Sub Workbook_Open()
Call Load_ListBox
End Sub

When the Workbook is opened the Macro Named Load_Listbox will run

You will notice script name is load listbox

I made that earlier when using listbox

It will still work. I should have changed name but you can if you want.

It loads the combobox

 

[My Aswer Is This]

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

By doing things this way if you want to later modify the names going into the Combobox
Then the Open script will not need to be changed. It always looks for Macro named Load_Listbox

 

[Shadkng]

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

Hi, I modified the code to my sheets and I have 8 items in my combo and all is working. But I have a few questions and observations.

I need to create more combo boxes in other sheets. So I changed the name of "load listbox" to "load listboxMast" in the macro so it will specific to that sheet. And then I renamed it as well in the code in "this workbook" module. Then I will name the combo something else for the next sheet. Sounds good?

Is it possible to have the combobox default to a title in the box such as " Choose Macro" and return to that after the macro is run? Now the last macro name stays in place.

Can the listbox be made to load when the sheet is activated instead of when the workbook is opened? Mainly because when modifying things it's easier to do that then close and re-open.

I have a much better understanding of how this all works now so thank you.

 

[My Aswer Is This]

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

To have the script run when sheet is activated instead of When workbook is opened.
Remove the code that says Workbook Open

And in each sheet you will need to enter script like this:
Modify each one as needed for specific sheet

Private Sub Worksheet_Activate()
Call Load_ListBox
End Sub

 

[My Aswer Is This]

Your next request was Put Choose Macro at top of list.

Try this:

Sub Load_ListBox()
'Modified  1/30/2019  6:19:32 AM  EST
'This assumes yor Combobox is in a sheet named "Mom"
'Modify sheet name as needed
With Sheets("Mom").ComboBox1
.Clear
.AddItem "Choose Macro"
.AddItem "Print Report"
.AddItem "Mom"
.AddItem "Bob"
.AddItem "George"
.AddItem "Stanley"
.AddItem "Julia"
End With
End Sub

 

[My Aswer Is This]

I changed my mind.

Your next request was Put Choose Macro at top of list.

Try this:​

Sub Load_ListBox()
'Modified  1/30/2019  6:50:43 AM  EST
'This assumes yor Combobox is in a sheet named "Mom"
'Modify sheet name as needed
With Sheets("Mom").ComboBox1
.Clear
'.AddItem "Choose Macro"
.AddItem "Print Report"
.AddItem "Mom"
.AddItem "Bob"
.AddItem "George"
.AddItem "Stanley"
.AddItem "Julia"
.Value = "Choose Macro"
End With
End Sub

 

[My Aswer Is This]

And lets use this one for other Part.

Be sure and modify combobox names as needed

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

If you ever get confused about which script is latest version look at top line of code for date and time