VBA using drop down lists on other worksheets to hide rows automatically

Question

Afternoon all!

I am using the results of various drop down lists to hide the relevant rows on a spreadsheet as the value on the drop down list is changed (so automatically).

With some googling, I have come across the below set up which works nicely, although I have now hit a snag when trying to specify drop down list cell from another worksheet.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim disabled As Boolean
    If Range("D2") = "Yes" Then
        disabled = True
    End If

    If disabled Then
        Rows("3:8").EntireRow.Hidden = False
    Else        
        Rows("3:8").EntireRow.Hidden = True
    End If

End sub

Using the following has not worked, and googling for a solution has lead me up many a dead end:

If Sheets("Topsheet").Range("D27") = "Yes" Then

Am I unable to use values from a neighbouring sheet when declaring a variable due to it being a private sub?

Any help would be much appreciated as I have been stumped on this for a couple of hours!

Tom Tom · Accepted Answer · 2017-11-01T17:29:29

Your code can be massively simplified. Try using this (You'll have to update the sheet name if it's not the same as yours)

Private Sub Worksheet_Change(ByVal Target As Range)
    Sheets("SheetWithRowsToBeHidden").Rows("3:8").EntireRow.Hidden = IIf(Me.Range("D27").Value2 = "Yes", True, False)
End Sub

Also, where have you put this code? Have you put it inside a Module or is it in the Sheet Object? It needs to be in the Sheet Object that has the dropdowns on.

VBA using drop down lists on other worksheets to hide rows automatically

1 Answers