CODEWITHSUNDEEP
excelvba
arbitel
arbitel

Reputation: 321

Autohide Rows Based on DV Dropdown Menu - Excel VBA

I'm wondering if anyone has any ideas about this, I feel like something is going over my head as I believe this should work...

I have a data validation dropdown menu on cell A11 containing (Select Property, Hotel, Hostel, Cabin)

I've defined the names of Rows 22:52 as 'RLHotel', rows 53:85 as 'RLHostel' and rows 86:115 as 'RLCabin'. Essentially I'm trying to give the user the option to pick out what type of property they're trying to fill out a rooming list for.

If they select 'Hotel' on the drop down menu, I want Hostel/Cabin to hide. If they 'Hostel', I want Hotel/Cabin to hide, etc.

The code I'm using in VBA for the this specific Worksheet is:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("A11")) Is Nothing Then
    With Range("$A$11")
        Range("RLHotel, RLCabin").EntireRow.Hidden = .Value = "Hostel"
        Range("RLHostel, RLCabin").EntireRow.Hidden = .Value = "Hotel"
        Range("RLHostel, RLHotel").EntireRow.Hidden = .Value = "Cabin"
    End With
End If


End Sub

Currently, If I select 'Hotel', only the 'Cabin' rows hide. If I select 'Hostel', all rows are showing. If I select 'Cabin', the Hostel and Hotel rows hide as they should.

Anyone have any ideas? Would be greatly appreciated, been searching on this forever! Thank you!

Upvotes: 0

Views: 104

Answers (2)

arbitel
arbitel

Reputation: 321

This is in reference to the comments on the answer. This was the final solve to make it update on each tab so I can build out more and it will automatically populate.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal   Target As   Range)
Application.Volatile


With Sheet4.Range("$A$11")
    Sheet4.Range("RLHotel").EntireRow.Hidden = .Value <> "Hotel"
    Sheet4.Range("RLHostel").EntireRow.Hidden = .Value <> "Hostel"
    Sheet4.Range("RLCabin").EntireRow.Hidden = .Value <> "Cabin"
End With

With Sheet12.Range("$A$11")
    Sheet12.Range("RLHotel").EntireRow.Hidden = .Value <> "Hotel"
    Sheet12.Range("RLHostel").EntireRow.Hidden = .Value <> "Hostel"
    Sheet12.Range("RLCabin").EntireRow.Hidden = .Value <> "Cabin"
End With

End Sub

Upvotes: 0

David Zemens
David Zemens

Reputation: 53653

Try this instead, I think you were overthinking it by trying to operate on two non-contiguous ranges (e.g., Range("RLHotel", "RLHostel"). Just flip the operator in the boolean expression to <> and set the Hidden property for each type of property thusly:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("A11")) Is Nothing Then
    With Range("$A$11")
        Range("RLHotel").EntireRow.Hidden = .Value <> "Hotel"
        Range("RLHostel").EntireRow.Hidden = .Value <> "Hostel"
        Range("RLCabin").EntireRow.Hidden = .Value <> "Cabin"
    End With
End If


End Sub

If A11 is linked (by formula) to a cell on another worksheet, then instead of using the _Change event use the _Calculate event:

Private Sub Worksheet_Calculate()
    With Range("$A$11")
        Range("RLHotel").EntireRow.Hidden = .Value <> "Hotel"
        Range("RLHostel").EntireRow.Hidden = .Value <> "Hostel"
        Range("RLCabin").EntireRow.Hidden = .Value <> "Cabin"
    End With
End Sub

Upvotes: 2

Related Questions