如何制作工作表的下拉列表 [英] How to make a drop-down list for worksheets

问题描述

我在工作簿中共有五张。我的任务是在第一张表中创建一个可以指向其他四个的组合列表按钮。如果用户选择其中一个工作表名称，则按钮将自动激活所选的工作表。这张表不太可能被删除，尽管可能会添加这些工作表。

我甚至不知道如何将表格名称显示在组合列表中。

解决方案

为了使组合框更改活动表单，我相信您将需要使用VBA（因为我不知道如何使用验证列表）。

要做到这一点，您必须：

1st - 将一个组合框添加到第一张表中，并正确命名（我叫它cmbSheet）。我建议使用ActiveX组合框（在Excel 2007中的开发人员选项卡下）。

第二 - 打开VBA并将以下代码添加到工作簿代码中。该代码将在每次打开工作簿时使用工作表名称填充组合框。

  Private Sub Workbook_Open（）
 
 Dim oSheet As Excel.Worksheet 
 Dim oCmbBox As MSForms.ComboBox 
 
设置oCmbBox = ActiveWorkbook.Sheets（1）.cmbSheet 
 
 oCmbBox.Clear 
 
对于每个oSheet在ActiveWorkbook.Sheets 
 
 oCmbBox.AddItem oSheet.Name 
 
下一个oSheet 
 
 End Sub 
  

第3 - 现在，转到第一张表（已添加组合框）的代码，然后添加该代码将激活在组合框中选择的工作表。代码是

  Private Sub cmbSheet_Change（）
 
 ActiveWorkbook.Sheets（cmbSheet.Value）.Activate 
 
 End Sub 
  

现在，当组合框值更改时，被激活。

如果有什么不清楚，我们会帮助您了解。

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.

解决方案

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

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

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.

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.

这篇关于如何制作工作表的下拉列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！