vba 根据单元格值取消隐藏行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15772686/
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
Unhide rows based on cell value
提问by David Van der Vieren
I am having difficulties with my code. What I am trying to do is when the Number in cell D8 goes up it will unhide a block of rows. Here is the following code:
我的代码有困难。我想要做的是当单元格 D8 中的数字上升时，它将取消隐藏行块。下面是以下代码：
Sub Unhide_Rows(ByVal Target As Range)
If Range("D8").Value > 1 Then
Select Case Target.Value
Case "2": Rows("17:36").Hidden = True: Rows("10:16").Hidden = False
Case "3": Rows("21:37").Hidden = True: Rows("10:20").Hidden = False
Case "4": Rows("25:37").Hidden = True: Rows("10:24").Hidden = False
Case "5": Rows("29:37").Hidden = True: Rows("10:29").Hidden = False
Case "6": Rows("33:37").Hidden = True: Rows("10:33").Hidden = False
Case "7": Rows("10:37").Hidden = False: Rows("55:56").Hidden = True
End Select
End If
End Sub
Another Issue I am running into is when I try to run the code in VBA it opens a macro box and wants me to select a macro, but I do not want to connect the code to a macro...?
我遇到的另一个问题是，当我尝试在 VBA 中运行代码时，它会打开一个宏框并希望我选择一个宏，但我不想将代码连接到宏...？
回答by NickSlash
This is a little bit of a guess as I'm not quite sure what all the variables in your code example are for.
这有点猜测，因为我不太确定代码示例中的所有变量的用途。
Open the VBA Editor (Alt+F11)
打开 VBA 编辑器 (Alt+F11)
Insert the following Sub into a module (in the VBA Editor, in the main menu, Insert->Module)
将以下 Sub 插入模块（在 VBA 编辑器中，在主菜单中，插入->模块）
Sub Toggle_Rows()
Dim Sheet As Worksheet: Set Sheet = ThisWorkbook.Worksheets("Sheet1") ' Change Sheet1 to the name of your sheet
Select Case CStr(Sheet.Range("D8").Value2)
Case "2"
Sheet.Rows("17:36").Hidden = True
Sheet.Rows("10:16").Hidden = False
Case "3"
Sheet.Rows("21:37").Hidden = True
Sheet.Rows("10:20").Hidden = False
Case "4"
Sheet.Rows("25:37").Hidden = True
Sheet.Rows("10:24").Hidden = False
Case "5"
Sheet.Rows("29:37").Hidden = True
Sheet.Rows("10:29").Hidden = False
Case "6"
Sheet.Rows("33:37").Hidden = True
Sheet.Rows("10:33").Hidden = False
Case "7"
Sheet.Rows("10:37").Hidden = False
Sheet.Rows("55:56").Hidden = True
Case Else
' none
End Select
End Sub
Now In the "Project Explorer" (usually on the left of the VBA Editor) open the code module for the worksheet you are working with (Sheet1 in my example) add the following code.
现在在“项目资源管理器”（通常在 VBA 编辑器的左侧）中打开您正在使用的工作表（在我的示例中为 Sheet1）的代码模块，添加以下代码。
Private Sub Worksheet_Change(ByVal Target As Range)
Msgbox Prompt:="Target.Address=" & Target.Address ' remove this line after debugging.
If Target.Address = "$D" Then
Toggle_Rows
End If
End Sub
Update
更新
Please ensure you've copied the code into the correct modules! I've added one line to the Worksheet_Changesub for debugging purposes. Please add it to your code, change the value in D8and tell me what is displayed in the message box.
请确保您已将代码复制到正确的模块中！Worksheet_Change为了调试目的，我在sub 中添加了一行。请将其添加到您的代码中，更改其中的值D8并告诉我消息框中显示的内容。
Notes
笔记
I think you may have re-named Worksheet_Changeto Unhide_Rowsin your code example, which you cannot do. (you can, but it will no longer work as it did)
我认为您可能在代码示例中重命名为Worksheet_Changeto Unhide_Rows，而您不能这样做。（你可以，但它将不再像以前那样工作）
Also, Subs that do not have arguments can be run from the VBA code editor. Subs WITH arguments (like yours) cannot, as there is no way to specify the argument unless you use the immediate window or have another sub (without arguments) that calls it for you.
此外，可以从 VBA 代码编辑器运行没有参数的 Sub。Subs WITH arguments (like yours) 不能，因为没有办法指定参数，除非你使用直接窗口或有另一个 sub （不带参数）为你调用它。
Sub HelloWorld(ByVal Text As String) ' cant be run directly
Debug.print "Hello World! " & Text)
End Sub
Sub CallHello() ' can be run directly in the vba editor
HelloWorld "Im Alive!"
End Sub
You could also call "HelloWorld" using the Immediate window.
您还可以使用立即窗口调用“HelloWorld”。
HelloWorld "Im Alive!" (press enter)
Update 2
更新 2
Your scrollbar isn't triggering the Worksheet_Changeevent, im not sure that you can make them do this.
您的滚动条没有触发Worksheet_Change事件，我不确定您是否可以让他们这样做。
However, the scroll bar has its own change event sub.
但是，滚动条有自己的更改事件子。
Open the code module for the worksheet the scrollbar is on (Sheet2 I believe) In the top left dropdown box (where it says "(general)") there will be an item for your scrollbar ("ScrollBar1" unless you renamed it). Selecting this should add the change event code, if not you will need to select "Change" from the right-hand dropdown box.
打开滚动条所在的工作表的代码模块（我相信是 Sheet2）在左上角的下拉框中（上面写着“（常规）”），滚动条会有一个项目（“ScrollBar1”，除非你重命名了它）。选择此项应添加更改事件代码，否则您需要从右侧下拉框中选择“更改”。
Code like the following should work.
像下面这样的代码应该可以工作。
Private Sub ScrollBar1_Change()
Toggle_Rows
End Sub
