Unhide rows based on cell value

Question

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

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

How is your sub called? Also should it re-hide rows if the value changes? D8 == Target? what is target otherwise? — NickSlash
I see that each step hides and unhides rows now, can you update your question to show how you're calling your sub, and what Target is. — NickSlash

NickSlash NickSlash · Accepted Answer · 2013-04-02T19:56:45

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

Example Workbook Here

Open the VBA Editor (Alt+F11)

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

showing module1 in vbaeditor

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.

showing sheet1 code in vbaeditor

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

Update

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.

Notes

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)

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

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

HelloWorld "Im Alive!" (press enter)

Update 2

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

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.

Code like the following should work.

Private Sub ScrollBar1_Change()
    Toggle_Rows
End Sub

Unhide rows based on cell value

1 Answers