CODEWITHSUNDEEP
exceluserformvba
Abigal
Abigal

Reputation: 143

Hide and unhide specific rows depending on a number selected.

I am creating a user form on Excel where I would like to hide and unhide entire rows depending on a data validation list which goes from 1 to 4. (data validation in cell M5)

Here what I want to do: If number 1 is selected in M5, I want to hide rows 9:11 and 17:20 If number 2 is selected in M5, I want to hide rows 10:11 and 18:20 If number 3 is selected in M5, I want to hide rows 11 and 20

All the rows I would like to hide contain a date picker option in column E. (I am happy to drop the date picker option if it is a must)

I would really appreciate your help.

Thank you all. A

Upvotes: 1

Views: 1376

Answers (1)

Raystafarian
Raystafarian

Reputation: 3022

You can use a worksheet change event by putting something like this in the worksheet object in the VBE

Sub worksheet_change(ByVal target As Range)
If Not Intersect(target, Range("M5")) Is Nothing Then
    ActiveSheet.Rows("9:20").EntireRow.Hidden = False
    If target = 1 Then
        ActiveSheet.Rows("9:11").EntireRow.Hidden = True
        ActiveSheet.Rows("17:20").EntireRow.Hidden = True
    ElseIf target = 2 Then
        ActiveSheet.Rows("10:11").EntireRow.Hidden = True
        ActiveSheet.Rows("18:20").EntireRow.Hidden = True
    ElseIf target = 3 Then
        ActiveSheet.Rows(11).EntireRow.Hidden = True
        ActiveSheet.Rows(20).EntireRow.Hidden = True
    ElseIf target = 4 Then
    End If
End If
End Sub

It checks that the change was in your validation, then checks the value, hiding what it should hide. I have it unhide everything at first so you don't have to worry about something remaining hidden after changing the value.

Be sure it's in the sheet module of the sheet you want this to happen on -

enter image description here

Upvotes: 1

Related Questions