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]

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

Hi, OK I understand a little better. To back up a little, I thought activex was a type of form control so now I see there are form controls and there are activex controls. So my first question is should I be using an activex list box for what I want to do? or is there another method? If I am to use a the activex box then here are my questions. I am not too clear on the concept of populating the list so if you could explain that. Also, what if I want to use different names than the names of the macros themselves.



So this code below gets entered into the code in the activex box itself? Which I did and I named a new range with a few macro names.

Private Sub Worksheet_Activate()
'Modified  1/27/2019  1:16:35 AM  EST
ListBox1.Clear
ListBox1.List = Range("A1:A4").Value
End Sub

And this code below gets entered into the sheet itself? Which I did, but it seemed to knockout the previous code that I entered into the activex box.

Private Sub ListBox1_Click()
'Modified 1/26/2019 9:09:38 PM EST
On Error GoTo M
Dim ans As String
ans = ListBox1.Value
Application.Run ans
Exit Sub
M:
MsgBox "The Macro named " & ans & " Does not exist"
End Sub

 

[My Aswer Is This]

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

You said:

So my first question is should I be using an activex list box for what I want to do?

My answer is Yes use a Activex listbox.

As far as the second question.

How do you want to load the Macro names into the listbox?

I earlier suggested using a activex command button but you said you did not want to do that.

And I asked how did you want to do that. We can have it done anytime the sheet is activated or many other ways. Not sure why you said no I do not want to use a Command Button.

This is the code that would go into the command button:

[LEFT][COLOR=#333333][FONT=monospace]ListBox1.Clear
ListBox1.List = Range("A1:A4").Value
[/FONT][/COLOR][/LEFT]

​

 

[My Aswer Is This]

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

If you look back at post 2 I showed you exactly what script goes into what control.

The only problem was you said I do not want use a command button

It looks like to me you should do it the way I gave you until you have it working and then if do not want the script in a command button put the script in some other place.

Some how this script has to run to load the Macro names into the list box.

And now your saying you do not want to load in the macro names into the listbox

You said in your last post I want to use some other name.

So if the macro is name Go_Home what do you want to put into the Listbox

This is now making things a lot more difficult. You said in the beginning you wanted to run macros by choosing them form a listbox

I can surely not understand why you would want to name your macro Soup but want to put Steak in the list box for example to run a macro named Soup

So please explain to me more what you want.

I'm trying to help here best I can

 

[Shadkng]

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

I appreciate you help, but I think your are assuming I know more than I do.

I don't understand the concept of the "command button loading the list". I see there is an option of a command button under activeX controls so I'm confused - Am I supposed to create a separate command button in addition to the list?. I just meant earlier that I did not want to use a command button and wanted to use a list. I did not mean to convey that I wanted to do it another way. I'll do it any way you tell me is the right way.

As far as the macro names, I only wanted to use another name because some of the macro names are long and not that descriptive. But if not, it's fine. I was asking because I was able to do that in the data validation list box and have the vba call the macro.

In your post #2 , you wrote - Load your listbox with Macro names like this - Assuming your list of Macro Names are in Range("A1:A4"). I don't know what load your list box means. I assume I should list the macro names I want and put them in a range which I specify in the code on the top in post #2 . If correct, then where do I insert this code? Private code in that sheet?

Then you said - Now put this script in your ListBox. OK so that code gets inserted into the activeX listbox? Right click on the listbox and click view code and insert there? I just don't know where to stick all these codes.

Thanks

 

[My Aswer Is This]

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

Show me how you do this:

I was able to do that in the data validation list box and have the vba call the macro.

Sounds like you know how to write scripts to me so not sure why your having trouble doing what I have show.


And I have asked before for you to show me one of your Macros.
And you have not done so.

And how did you make these Macros. Did you use the Macro recorder?
​

 

[Shadkng]

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

Hi, OK I will send back more information. I have been doing more research online to better understand the concepts.

 

[My Aswer Is This]

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

Here is a new way.

You will need a Activex Commmans Button

You will need a Activex Listbox

Right click on The Command button select View Code and paste in the below Code:

Private Sub CommandButton1_Click()
'Enter the value you want to see in the list box here
With ListBox1
.Clear
.AddItem "Dad"
.AddItem "Mom"
.AddItem "Bob"
.AddItem "George"
.AddItem "Stanley"
.AddItem "Julia"
End With
End Sub

Right click on The Listbox select View Code and paste in the below Code:​

Private Sub ListBox1_Click()
'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 "Dad"
        .Run "One"
    Case "Mom"
        .Run "Two"
    End Select
End With
End Sub

Now when you click on the command button the list box will not have all the values you want in the listbox
You only need to do this One time.
The values in the listbox will stay there until you close your Workbook.
Next time you open your workbook you will have to click the command Button again.

So now when you click on a value in the listbox the Macro will run

This assumes all you macros are Module Macros

I have asked more then once for you to show me one of your Macros but for some reason you have not done that.

Without seeing it or you knowing the different's I have no way of knowing.

 

[My Aswer Is This]

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

If you have a Private Macro at the top it will look something like this:

Private Sub CommandButton1_Click()


If you have a Module Macro it will look something like this

Sub One()

One being the name of the Macro


For my Macro to work you need to have Module Scripts

 

[My Aswer Is This]

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

Now if you do not like the ideal of needing to have a Command Button. Try what I have explained earlier using a Command Button and if it all works I will tell you how to eliminate the command Button.

 

[Shadkng]

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

Hi, see below one of my macros. Most of them are module macros, but I do have a couple of private macros that I use for a combobox that make my data validation list box better. I am understand now that a command button is needed to populate the list and I have read there are a few ways to do that. I see you are giving me the easy way first and then later a way to avoid having to hit the command button each time. I had help with the code below so don't think I have this knowledge...I've gotten a lot of help on this form from kind people like you. But I'm learning and I appreciate your time to help me - you're not giving up on me!

Sub PRINT_RANGE()
    Dim response As String
    Dim PrintAreaString As String
    response = InputBox("Enter column letter for 2nd part of range", "Enter Data")
     'With Worksheets("CLUTCH")
      Application.ScreenUpdating = False
      With ActiveSheet
        PrintAreaString = "$A$3:$" & Trim(UCase(response)) & "$" & .Range("A1").Value
        If response <> "" Then
            .PageSetup.PrintArea = PrintAreaString
            .PageSetup.Orientation = xlLandscape
            .PageSetup.Zoom = False
            .PageSetup.FitToPagesWide = 1
            .PageSetup.FitToPagesTall = False
            .PageSetup.LeftMargin = 36
            .PageSetup.TopMargin = 72
            .PageSetup.RightMargin = 36
            .PageSetup.BottomMargin = 36
             'ActiveSheet.UsedRange.Font.Size = 12
            '.UsedRange.RowHeight = 22.75
            '.PrintPreview
        End If
    End With
    Application.ScreenUpdating = True
End Sub