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.

Answer 1

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.

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:

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.

Answer 2

This is just an example of :

1. creating a Button
2. 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