vba 如何制作工作表的下拉列表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5601296/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to make a drop-down list for worksheets
提问by Jesse Smothermon
I have a total of five sheets in a workbook. My task is to create a combo list button in the first sheet that will be able to point to the other four. If a user chooses one of the sheet names then the button will automatically activate the chosen sheet. It is unlikely that sheets will be deleted, though likely that sheets will be added.
我在一本工作簿中总共有五张纸。我的任务是在第一个工作表中创建一个组合列表按钮，该按钮将能够指向其他四个。如果用户选择其中一个工作表名称，则该按钮将自动激活所选工作表。不太可能删除工作表，但可能会添加工作表。
I'm not even sure how to get the sheet names to show up on the combo list.
我什至不确定如何让工作表名称显示在组合列表中。
回答by Tiago Cardoso
In order to make the combobox change the active sheet, I believe you'll need to use VBA (as I don't know how to do it using validation lists).
为了使组合框更改活动表，我相信您需要使用 VBA（因为我不知道如何使用验证列表来做到这一点）。
To do it, you'll have to:
为此，您必须：
1st - Add a combobox into your first sheet and properly name it (I called it cmbSheet). I suggest to use an ActiveX Combobox (in Excel 2007, under Developer tab).
1st - 在您的第一张工作表中添加一个组合框并正确命名（我称之为 cmbSheet）。我建议使用 ActiveX Combobox（在 Excel 2007 中，在“开发人员”选项卡下）。
2nd - Open VBA and add the below code into your workbook code. This code will populate the combobox with the sheet names every time the workbook is opened.
2nd - 打开 VBA 并将以下代码添加到您的工作簿代码中。每次打开工作簿时，此代码都会用工作表名称填充组合框。
Private Sub Workbook_Open()
Dim oSheet As Excel.Worksheet
Dim oCmbBox As MSForms.ComboBox
Set oCmbBox = ActiveWorkbook.Sheets(1).cmbSheet
oCmbBox.Clear
For Each oSheet In ActiveWorkbook.Sheets
oCmbBox.AddItem oSheet.Name
Next oSheet
End Sub
3rd - Now, go to the code of your first sheet (where the combobox has been added) and add the code that will activate the sheet chosen in the combobox. The code is
第三 - 现在，转到第一个工作表的代码（已添加组合框的位置）并添加将激活在组合框中选择的工作表的代码。代码是
Private Sub cmbSheet_Change()
ActiveWorkbook.Sheets(cmbSheet.Value).Activate
End Sub
Now, when the combobox value changes, the respective sheet is activated.
现在，当组合框值改变时，相应的工作表被激活。
Let us know if something ins't clear and we'll help you out.
如果有什么不明白的地方，请告诉我们，我们会帮助您。
