vba Excel 宏来创建命令按钮并将宏分配给按钮

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

提问by user3832353

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_States1to 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.

然后我尝试使用该名称All_States1来创建各种按钮，但它无法正常工作，因为所有选定的选项都显示相同的按钮并且该按钮也不会消失。另外，我似乎无法为创建的按钮分配宏。

回答by Gary's Student

This is just an example of :

这只是一个例子：

1. creating a Button
2. assigning a macro to it

1. 创建一个按钮
2. 为其分配一个宏

.

.

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

回答by Floris

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 selectionInand the result selectionOut.

首先 - 创建一个组合框，以及输入（组合框中的名称）和输出（选择的值）的范围。例如，您可以调用 inputselectionIn和 result selectionOut。

Exact steps:

具体步骤：

Wrote values of combobox selections in E1:E4 . Selected the four cells, then typed selectionInin 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).

在 E1:E4 中写入组合框选择的值。选择四个单元格，然后selectionIn在名称框中键入（在公式栏的左侧）。这将创建一个命名范围（还有其他方法可以创建命名范围，但这是我的首选方法）。

Called cell F1selectionOut

被调用的单元格 F1selectionOut

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.

创建了一个按钮，给它贴上了标签“Go”并将它链接到 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 -1because the array index is base 0 (by default anyway) and the combobox returns 1for the first selection. You could make your code more robust by writing

这当然是我能想到的最紧凑的方法......你需要偏移量，-1因为数组索引是基数 0（无论如何默认）并且组合框返回1第一个选择。您可以通过编写使您的代码更健壮

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

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

这确保如果您将functionList数组的基本索引更改为另一个值，它仍然可以正常工作。