Excel macro to create a command button and assign macro to the button

Question

I am trying to build a main page for my costing model. On this page I have created a drop down list using a combo box and then I want to assign a macro that creates a list of different buttons/command buttons once an option is selected from the list. Then I want to build another macro that is assigned to those buttons which then take the user to another tab/sheet within the same workbook depending on the option they selected from the drop down.

Can someone please give me an idea as to what code I should be using, first to create a command button that refers to the selected option from the drop down and then assign a simple macro to that button which then takes me to the specified tab/sheet?

So far, I have got the following:

Option Explicit

Sub Select_Change()
    With ThisWorkbook.Sheets("Main Page").Shapes("Select").ControlFormat
        Select Case .List(.Value)
            Case "Vehicle1": All_States1
            Case "Vehicle2": All_States2
            Case "Vehicle3": All_States3
            Case "Vehicle4": All_States4
            Case "Vehicle5": All_States5
            Case "Vehicle6": All_States6
            Case "Vehicle7": All_States7
        End Select
    End With
End Sub

I then tried to use the name All_States1 to create various buttons but it's not working properly, as all selected options are showing the same button and the button won't go away either. Also, I can't seem to assign a macro to the created button.

It is not clear what you are trying to do. In general, creating user interface elements and assigning code can get messy veryk quickly (if it works at all). Consider instead having all possible elements created once, and hide / unhide them as needed. Much cleaner. This might even take the form of a tabbed interface, and you just pick the tab you want (that essentially does the grouping / hiding etc for you, it just doesn't feel like as much work). See if that gets you somewhere... — Floris
– Floris, Commented Jul 12, 2014 at 14:11
what i'm really trying to do is to create a front page of a costing model that takes the user to different tabs hidden within that worksheet. you are right by saying it would get messy if i assign codes to each element but i just want to assign macros for the tabs i already have created. — user3832353
– user3832353, Commented Jul 13, 2014 at 5:33
Your problem is still not clear to me. You can assign a macro to an object by setting its OnAction property as Gary's answer shows. Perhaps you can make a sketch ("story board") of what you want to happen when an element of the drop down is selected, showing when and where you want to create buttons etc. — Floris
– Floris, Commented Jul 13, 2014 at 11:13
Yes i have made a rough sketch of how i want the main page to look like. starts with dropdown at the top then a few buttons. I have now made it a bit simpler by placing the buttons instead of creating them via VBA. i have done the coding for what the buttons would do but still can seem to come up with a code that uses the if condition to match the selection from the dropdown when button is clicked and opens the relevant sheet (as per the dropdown value). I have the button code to open the sheet but not based on the if criteria. Sry am new to macros mate. — user3832353
– user3832353, Commented Jul 14, 2014 at 9:44
OK - so your problem now is "when a button is clicked, how do I run a specific macro depending on the state of a dropdown menu". Is that accurate - because that's a problem I could help you with... — Floris
– Floris, Commented Jul 14, 2014 at 13:36

Gary's Student · Accepted Answer · 2014-07-12 14:55:21Z

3

This is just an example of :

creating a Button
assigning a macro to it

.

Sub button_maker()
    Dim r As Range
    Set r = Selection
        ActiveSheet.Buttons.Add(94.5, 75.75, 51, 27.75).Select
        With Selection
            .OnAction = "mooney"
            .Characters.Text = "Bump"
        End With
    r.Select
End Sub


Sub mooney()
    Range("A1").Value = Range("A1").Value + 3
End Sub

answered Jul 12, 2014 at 14:55

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Floris · Accepted Answer · 2014-07-14 14:44:28Z

If I understand the problem correctly, you want to have a dropdown (combo box) on your sheet, and when a button is clicked you want to run a macro based on the selection. The following does that - see if it helps you.

First - create a combobox, and a range for the inputs (names in the combobox) and output (value selected). For example, you could call the input selectionIn and the result selectionOut.

enter image description here

Exact steps:

Wrote values of combobox selections in E1:E4 . Selected the four cells, then typed selectionIn in the name box (to the left of the formula bar). That creates a named range (there are other ways to create named ranges, but this is my preferred method).

Called cell F1 selectionOut

Created a combobox, and referenced these two ranges for its input and output:

enter image description here

Created a button, gave it the label "Go" and linked it to the action runIt.

Finally, I created the following code in a workbook module:

Sub runIt()
  Dim whatToDo, makeName As Boolean
  ' look up the name of the combo based on the value:
  whatToDo = Range("selectionIn").Cells([selectionOut].Value, 1)
  MsgBox "have to do '" & whatToDo & "'"
  makeName = False

  Select Case whatToDo
    Case "one"
      ' example of putting the code you need right in the select:
      MsgBox "doing the first thing"
    Case "two"
      ' example of calling a specific routine:
      Call caseTwo
    Case "three"
      Application.Run "case" & whatToDo ' making the name of the function on the fly
    Case "four"
      makeName = True
  End Select

  If makeName Then
    Dim nameToRun
    nameToRun = "case" & whatToDo
    Application.Run nameToRun
  End If

End Sub

Sub caseTwo()
MsgBox "called the code for case two"
End Sub

Sub caseThree()
MsgBox "doing case three here"
End Sub

Sub caseFour()
MsgBox "even four can be done"
End Sub

This shows a few different ways to handle different cases depending on what was selected. Of course you can have a macro run every time the combobox selection is changed - but it sounds from your description like that is not what you want.

Let me know how you get on with this code example - I tried to keep it simple but show some options at the same time.

One alternative (which might be simpler) would be to have an array with the names of the functions that you want to call:

Sub otherMethod()
    Dim functionList()
    functionList = Array("caseOne", "caseTwo", "caseThree", "caseFour")
    Application.Run functionList([selectionOut].Value - 1)
End Sub

That's certainly the most compact way I can think of to do this... you need the offset of -1 because the array index is base 0 (by default anyway) and the combobox returns 1 for the first selection. You could make your code more robust by writing

functionIndex = [selectionOut].Value + LBound(functionList) - 1
Application.Run functionList(functionIndex)

This ensures that if you change the base index of the functionList array to another value, it will all still work correctly.

Floris your beauty!! your code worked. That was exactly what i was looking for. I really appreciate the time and effort you made to give different scenarios. it worked like a charm. I used the code in the first example and took the call option so that i can call the macro and it worked exactly the way i wanted. Thanks again for the help, you really are brilliant with coding. thanks!!
Glad it worked. In cases like this the hardest part is understanding what it is you are trying to do... Once you can boil your problem down to a very concrete step, there is usually somebody here who can help.

Collectives™ on Stack Overflow

Excel macro to create a command button and assign macro to the button

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related