vba 创建一个宏，该宏将根据下拉列表中的选择隐藏和显示列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3184697/
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
Create a macro that would hide and show columns based on selection from a dropdown
提问by ThomB
I am totally new to the world of macros but have done some VBScript on QTP before. I am looking to create a macro which would hide certain columns depending on the users selection from a drop down. I am unsure of the syntax and how to identify the columns I wish to hide and how to identify the cell with the drop-down.
我对宏世界完全陌生，但之前在 QTP 上做过一些 VBScript。我正在寻找创建一个宏，它会根据用户从下拉列表中的选择来隐藏某些列。我不确定语法以及如何识别我希望隐藏的列以及如何识别带有下拉列表的单元格。
Here's roughly how I would see it looking -
这大致是我如何看待它的样子 -
Sub HideColumns()
If cell(ViewType).Value = "Option 1" Then
Cells(Column_1, Column_2).EntireColumn.Hidden = True
ElseIf cell(ViewType).Value = "Option 2" Then
Cells(Column_2, Column_3).EntireColumn.Hidden = True
ElseIf cell(ViewType).Value = "Option 3" Then
Cells(Column_3, Column_4).EntireColumn.Hidden = True
End If
End Sub
I have named the cell with the drop-down ViewTypein Excel - would the VBA recognize the object that way, or would I need to declare it?
我已经用ViewTypeExcel 中的下拉菜单命名了单元格- VBA 会以这种方式识别对象，还是需要声明它？
How do I identify the columns I wish to hide?
如何识别要隐藏的列？
回答by Trefex
The names you define in Excel are available in Excel as normal variables, so this should not be of any issue.
您在 Excel 中定义的名称在 Excel 中可用作普通变量，因此这应该没有任何问题。
In your case, I would however suggest using a Switch Casestatement. This would look as follows:
但是，在您的情况下，我建议使用Switch Case声明。这将如下所示：
Select Case ActiveWorkbook.Names("ViewType").RefersToRange
Case "Option 1"
' Hide Column X
Case "Option 2"
' Hide Column Y
End Select
Also keep in mind that for the macro to be called once you change a cell, you would need to put this code into
还要记住，要在更改单元格后调用宏，您需要将此代码放入
Private Sub Worksheet_Change(ByVal Target As Range)
End Sub
This Sub has to be placed in the code part of the Sheet itself and will be executed every time a cell is changed in the Sheet.
这个 Sub 必须放在 Sheet 本身的代码部分，并且每次在 Sheet 中更改单元格时都会执行。
Let me know if this is enough for you to go on or if you require more help.
让我知道这是否足以让您继续下去，或者您是否需要更多帮助。
回答by Dick Kusleika
I like to use Custom Views when hiding and unhiding columns. Custom views is on the View tab (>=2007) and under the View menu (<=2003). Here's an example:
我喜欢在隐藏和取消隐藏列时使用自定义视图。自定义视图位于“视图”选项卡 (>=2007) 和“视图”菜单下 (<=2003)。下面是一个例子：
- Hide columns 1 and 2
- View - Custom Views - Add - name it "Option1"
- Unhide, then hide columns 2 and 3
- View - Custom Views - Add - name it "Option2"
- Unhide, then hide columns 3 and 4
- View - Custom Views - Add - name it "Option3"
- Unhide
- Create Data Validation in a cell (that doesn't get hidden) and make is a List with "Option1, Option2, Option3"
- Name that cell ViewType
Right click on the sheet tab and choose View Code and put the below code in that code pane
Private Sub Worksheet_Change(ByVal Target As Range) 'Only work on the cell named ViewType 'The Me keyword refers to the sheet whose code module you're in If Target.Address = Me.Range("ViewType").Address Then 'Show the custom view that corresponds to the value selected 'in the dropdown ActiveWorkbook.CustomViews(Target.Value).Show End If End Sub
- 隐藏第 1 列和第 2 列
- 视图 - 自定义视图 - 添加 - 将其命名为“Option1”
- 取消隐藏，然后隐藏第 2 列和第 3 列
- 视图 - 自定义视图 - 添加 - 将其命名为“Option2”
- 取消隐藏，然后隐藏第 3 列和第 4 列
- 视图 - 自定义视图 - 添加 - 将其命名为“Option3”
- 取消隐藏
- 在单元格中创建数据验证（不会被隐藏）并且 make 是一个带有“Option1、Option2、Option3”的列表
- 将该单元格命名为 ViewType
右键单击工作表选项卡并选择查看代码并将以下代码放入该代码窗格中
Private Sub Worksheet_Change(ByVal Target As Range) 'Only work on the cell named ViewType 'The Me keyword refers to the sheet whose code module you're in If Target.Address = Me.Range("ViewType").Address Then 'Show the custom view that corresponds to the value selected 'in the dropdown ActiveWorkbook.CustomViews(Target.Value).Show End If End Sub
When the user selection Option1 from the drop down, the Option1 view is shown that hides columns 1 and 2. It's a nice way to manage hiding and unhiding because you can just adjust the Custom View if you ever want to change it, rather than editing the code.
当用户从下拉列表中选择 Option1 时，会显示 Option1 视图，该视图隐藏了第 1 列和第 2 列。这是管理隐藏和取消隐藏的好方法，因为如果您想更改自定义视图，而不是编辑它，您只需调整它编码。
