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]

I write scripts as users want. But when users add more to their request it may have been easier for the user if we did things some other way.

You never mentioned you would be having Comboboxes in more then one sheet.
The way we are doing it now is causing you to make numerous Macros list LoadListbox

And Load Listbox2 and Load Listbox3

And on and on depending on how many sheets you want to do this on.

So I have these question.

Will all your Comboboxes have the same Macro names added to the list?

Will all Comboboxes be named the same

So Each sheet will have a combobox Named Combobox1

And how many sheets will you be putting these Comboboxs in?
Will it be like one or two or Ten to Twenty and more as time passes.

See if this will be only one or two what were doing now is easy for you
But if its 10 or 20 there may be a easier way.

 

[My Aswer Is This]

Are you interested in another plan?

Is your overall objective to be able to run certain macros on your sheet.

Have you ever used a UserForm?

A Userform is like a Form that popsup on the screen with things like Comboboxes which you can click to do things like run Macros

This would mean you do not need the combobox on each sheet.

Your Combobox would be on this Form that pops ups and you click the combobox to run the script

The Userform can stay displayed on the screen as long as you want and you can still work on your sheet.

The form in your case could be very small with just a Combobox. You can Place this form where ever you want on the screen. You just click on the form and drag it where you want.

And this form can be activated when you activate your sheet.

Doing it this way requires very little code

Are you interested in knowing more?

If your want take a look.

On any sheet Right click Tab select view code look up on Menu Bar
you should see Insert Userform

Click that and you will see a small form popup.
Now this form looks a little ugly but we can give it some color if we want

 

[Shadkng]

I understand, but I was reluctant to ask for so many things in the beginning but I realize it adds extra work. So I apologize.

I would say maximum 10 sheets where I would be using the combo box. Right now I have several command buttons on those sheets which I want to get rid of.

The other combo boxes will share some macros and have some specific ones to that sheet.

I thought I could just give the combobox and list box unique names and add as I need, but I guess it's not that simple.

Below is the code as I modified it which is working nicely on my sheet called "MASTER FORM". You can see I changed the name to "ComboBoxMast" and I also used that name in the properties section of the Combobox.

Private Sub ComboBoxMast_Change()
'So in this example if user clicks on Dad in list box then Macro named "One" will run
 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
End Sub

The code below is in module "this workbook"

Private Sub Workbook_BeforePrint(Cancel As Boolean)
 ActiveSheet.PageSetup.CenterHeader = ActiveSheet.Range("B6") & "   JOB:" & ActiveSheet.Range("B7") & "    PO#  " & ActiveSheet.Range("B8")
End Sub

Private Sub Workbook_Open()
Call Load_ListBoxMAST
End Sub

Below is in a standard Module

Sub Load_ListBoxMAST()
'Modify sheet name as needed
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"
End With
End Sub

 

[My Aswer Is This]

That's great. So are we all done here or do you need something more.

I added to your Load Listbox script the value "Choose Macro" like you asked for but in your script I see you did not add that so I guess you change your mind

Glad to see your learning how to do this and not just taking scripts we write and using them not understanding the logic.

I believe it's best to:

To Teach a Man to Fish
And not always Fish for him.

Take care

 

[Shadkng]

I just saw your reply about the user form. It sounds interesting so I will take a look, but I will make sure I have an understanding before I ask you to do the work. Is it less overhead on the file to use forms?

 

[My Aswer Is This]

Glad you have what you need. Which Ideal is best Userform or this way is all in the eyes of the beholder.

 

[Shadkng]

I missed your post about adding "choose macro" so I did that and it works great. Your too fast for me haha. I will check out the user forms, but I understand this now so maybe I'll stock with it.

So I should go ahead and use unique names for combo / listbox on my additional sheets like I did above? I assume so, unless you tell me another way.

Thanks and I'll be in touch.

 

[My Aswer Is This]

Well I see in your previous posting you have this:

Private Sub Workbook_Open()
Call Load_ListBoxMAST
End Sub

I thought you said you did not want the script to run on workbook Open you said you wanted on Sheet activate

Which I gave you a script like that.

So am I confused?
​

 

[Shadkng]

That previous post was before I saw your answer on that. I changed it to worksheet activate but it's not loading the combobox.

 

[My Aswer Is This]

On each of your sheets you should have these two script.

That look something like this:

Now each one will need to be different because you want each sheet Combobox to have different Macros in them

I'm sure this gets complicate for you but see if you want every Combobox to run different scripts that's all we can do:

Private Sub Worksheet_Activate()
Call Load_Combobox
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