vba 根据下拉选择显示/隐藏行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16943797/
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
Show/Hide Rows Depending on Dropdown Selection
提问by pufAmuf
This is the first time I'm working with Macros. I've created a dropdown in B2 with a "Yes" and "No" options.
这是我第一次使用宏。我在 B2 中创建了一个带有“是”和“否”选项的下拉列表。
- If User selects "Yes", Row 10 Shows / Row 11 Hides
- If User Selects "No", Row 11 Shows / Row 10 Hides
- 如果用户选择“是”，第 10 行显示/第 11 行隐藏
- 如果用户选择“否”，第 11 行显示/第 10 行隐藏
I used this code:
我使用了这个代码：
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B" Then
If Range("B2") = Yes Then
ActiveSheet.Rows("10:10").EntireRow.Hidden = False
ActiveSheet.Rows("11:11").EntireRow.Hidden = True
ElseIf Range("B2") = No Then
ActiveSheet.Rows("10:10").EntireRow.Hidden = True
ActiveSheet.Rows("11:11").EntireRow.Hidden = False
End If
End If
End Sub
I Created a new Module in Sheet1, and put it there. I saved the excel as a Macro Enabled Tamplate, however nothing happens when I change the dropdown.
我在Sheet1 中创建了一个新模块，并将其放在那里。我将 excel 保存为启用宏的模板，但是当我更改下拉列表时没有任何反应。
Thanks for your help!
谢谢你的帮助！
采纳答案by JosieP
If Range("B2") = "Yes" Then
and similarly with the "No " option
与“否”选项类似
回答by enderland
Do yourself a huge favor and get in the habit of writing Option Explicitat the top of every module of VBA code you write.
帮自己一个大忙，养成Option Explicit在您编写的 VBA 代码的每个模块顶部编写代码的习惯。
I have added comments as well explaining your needed revisions.
我添加了评论以及解释您需要的修订。
'this requires you to dimension all variables
'when you used '= yes' VBA thought you were saying
'the same as, = aVariable
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B" Then
If Range("B2").Value = "Yes" Then
'You can reference the row directly on the same sheet
'and do not need ActiveSheet
Rows("10:10").EntireRow.Hidden = False
Rows("11:11").EntireRow.Hidden = True
ElseIf Range("B2").Value = "No" Then
Rows("10:10").EntireRow.Hidden = True
Rows("11:11").EntireRow.Hidden = False
End If
End If
End Sub
Also be aware this is only using "Yes" - using "yes" or "YES" will cause problems. You can use the UCasemethod as follows if you want to avoid these situations in the future:
另请注意，这仅使用“是” - 使用“是”或“是”会导致问题。UCase如果以后想避免这些情况，可以使用如下方法：
If UCase(Range("B2").Value) = "YES" Then
