根据单元格值取消隐藏行 [英] Unhide rows based on cell value

问题描述

我的代码遇到困难.我正在尝试做的是，当单元格D8中的数字增加时，它将取消隐藏一行行.这是以下代码:

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:

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

我遇到的另一个问题是，当我尝试在VBA中运行代码时，它打开了一个宏框，希望我选择一个宏，但是我不想将代码连接到宏...?

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

推荐答案

这有点猜测，因为我不确定您的代码示例中的所有变量都用于什么.

This is a little bit of a guess as I'm not quite sure what all the variables in your code example are for.

此处的示例工作簿

打开VBA编辑器(Alt + F11)

Open the VBA Editor (Alt+F11)

将以下Sub插入模块(在VBA编辑器的主菜单中，插入->模块)

Insert the following Sub into a module (in the VBA Editor, in the main menu, Insert->Module)

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

现在，在项目资源管理器"(通常在VBA编辑器的左侧)中，打开您正在使用的工作表的代码模块(在我的示例中为Sheet1)，添加以下代码.

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.

Private Sub Worksheet_Change(ByVal Target As Range)

Msgbox Prompt:="Target.Address=" & Target.Address ' remove this line after debugging.

If Target.Address = "$D$8" Then
    Toggle_Rows
End If

End Sub

更新

请确保您已将代码复制到正确的模块中！我已将一行添加到 Worksheet_Change 子中以进行调试.请把它添加到您的代码中，更改 D8 中的值，然后告诉我消息框中显示的内容.

Please ensure you've copied the code into the correct modules! I've added one line to the Worksheet_Change sub for debugging purposes. Please add it to your code, change the value in D8 and tell me what is displayed in the message box.

注释

在您的代码示例中，我认为您可能已经将 Worksheet_Change 重命名为 Unhide_Rows ，但是您不能这样做.(您可以，但是它将不再像以前那样起作用)

I think you may have re-named Worksheet_Change to Unhide_Rows in your code example, which you cannot do. (you can, but it will no longer work as it did)

此外，可以从VBA代码编辑器运行不带参数的Subs.带有参数的子项(例如您的子项)不能，因为除非您使用立即窗口或有另一个子项(不带参数)为您调用，否则无法指定该参数.

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.

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

您还可以在即时"窗口中调用"HelloWorld".

You could also call "HelloWorld" using the Immediate window.

HelloWorld "Im Alive!" (press enter)

更新2

您的滚动条没有触发 Worksheet_Change 事件，我不确定您可以使它们执行此操作.

Your scrollbar isn't triggering the Worksheet_Change event, im not sure that you can make them do this.

但是，滚动条具有其自己的change event子项.

However, the scroll bar has its own change event sub.

打开滚动条所在的工作表的代码模块(我相信，Sheet2)在左上角的下拉框(显示(常规)")中，将有一个滚动条项目("ScrollBar1"，除非您重命名了)它).选择此项将添加更改事件代码，否则，您需要从右侧的下拉框中选择更改".

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.

下面的代码应该可以工作.

Code like the following should work.

Private Sub ScrollBar1_Change()
    Toggle_Rows
End Sub

这篇关于根据单元格值取消隐藏行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！