Dropdown that shows and hides certain rows

[TheJay]

Hello there, I am wondering how I would go about having a cell range appear or disappear depending on the selection made from a dropdown list.

Say for example, the drop down list contained two choices "Formatting" or "Example".

By default, "Formatting" would be the option displayed and that would show C5:I7. If the dropdown option was changed to "Example", it would hide C5:I7 and show C9:I10. Equally, if the choice was changed again to "Formatting", it would again show C5:I7 and hide C9:I10.

I also need to lock/protect the sheet so that values cannot be changed, whilst allowing the dropdown list to be used and being able to sort data via a filter list with headings in C12:I12. Not sure if it would make it more complicated.

Thank you for your help.

 

[davesexcel]

Place the dropdown in A1

Place this in the worksheet module

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim sh          As Worksheet
    Dim formatting  As Range
    Dim example     As Range
    If Target.Address = "$A$1" Then
        Set sh = ActiveSheet
        With sh
            Set formatting = .Range("A5:A7")
            Set example = .Range("A9:A10")
            Union(formatting, example).EntireRow.Hidden = FALSE
            If Target = "Formatting" Then formatting.EntireRow.Hidden = TRUE
            If Target = "Example" Then example.EntireRow.Hidden = TRUE
        End With
    End If
End Sub

Unlock A1 before you protect the sheet, there are lots of threads on how to unprotect then protect the sheet with vba.

 

[TheJay]

Thank you for your reply. How would it look if another choice or multiple additional choices were added?

For example, choice three "Instructions" for range C14:I15 and "Help" for range C19:I20?

 

[davesexcel]

Add them to the code.

 

[TheJay]

Like this?

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim sh          As Worksheet
    Dim formatting  As Range
    Dim example     As Range
    Dim instructions As Range
    Dim help        As Range
        If Target.Address = "$C$2" Then
        Set sh = ActiveSheet
        With sh
            Set formatting = .Range("C3:I4")
            Set example = .Range("C9:I10")
            Set instructions = .Range("C14:I18")
            Set help = .Range("C21:I24")
            Union(formatting, example, instructions, help).EntireRow.Hidden = False
            If Target = "Formatting" Then formatting.EntireRow.Hidden = True
            If Target = "Example" Then example.EntireRow.Hidden = True
            If Target = "Instructions" Then instructions.EntireRow.Hidden = True
            If Target = "Help" Then help.EntireRow.Hidden = True
        End With
    End If
End Sub

 

[TheJay]

It doesn't work quite as expected. I'm not sure why.

It should only ever show one cell range from the dropdown. At the moment, it doesn't hide all the others.

Example (1).xlsm

Property Numbering Formatting Formatting Example Instructions Help Formatting L,L#,PC,St,Pro,Ref,Ref L M,1,###,######,#####,1 ### ### #####,12 W,4,##,######,#####,4 ## ##### #####,13 Wy,3,###,##### (exclude preceding zero for parish numbers under 100),######,3 ### ### ######,13 Example L,L#,PC,...

docs.google.com

 

[davesexcel]

Oh, I guess we have it backwards.

 

[TheJay]

Oh, I guess we have it backwards.

Okay, so:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim sh          As Worksheet
    Dim formatting  As Range
    Dim example     As Range
    Dim instructions As Range
    Dim help        As Range
        If Target.Address = "$B$2" Then
        Set sh = ActiveSheet
        With sh
            Set formatting = .Range("B7:J10")
            Set example = .Range("B12:J15")
            Set instructions = .Range("B17:J20")
            Set help = .Range("B21:J24")
            Union(formatting, example, instructions, help).EntireRow.Hidden = True
            If Target = "Formatting" Then formatting.EntireRow.Hidden = False
            If Target = "Example" Then example.EntireRow.Hidden = False
            If Target = "Instructions" Then instructions.EntireRow.Hidden = False
            If Target = "Help" Then help.EntireRow.Hidden = False
        End With
    End If
End Sub

Let's say I want the dropdown menu to have a title. As there is nothing in A1, I can use the following:

=IF(A1="", "Property Reference Guide")

The moment I choose an option from the dropdown list, the formula disappears forever. If I protect the cell so the formula isn't removed, I can't use the dropdown list.

Can you please tell me what the solution is?

 

[davesexcel]

Change the data validation so the first selection is the title.

 

[TheJay]

When the worksheet is opened, it is blank. I don't want it to be blank, that's the problem.

I'm trying to put something together using the VBA from this worksheet but it's bringing up multiple errors and I am trying to combine two different things within
Private Sub Worksheet_Change(ByVal Target As Range):

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim sh          As Worksheet
    Dim formatting  As Range
    Dim example     As Range
    Dim instructions As Range
    Dim help        As Range
        If Target.Address = "$B$2" Then
        Set sh = ActiveSheet
        With sh
            Set formatting = .Range("B7:J10")
            Set example = .Range("B12:J15")
            Set instructions = .Range("B17:J20")
            Set help = .Range("B22:J22")
            Union(formatting, example, instructions, help).EntireRow.Hidden = True
            If Target = "Formatting" Then formatting.EntireRow.Hidden = False
            If Target = "Example" Then example.EntireRow.Hidden = False
            If Target = "Instructions" Then instructions.EntireRow.Hidden = False
            If Target = "Help" Then help.EntireRow.Hidden = False
        End With
    End If
            If Not Intersect(Target, Range("B2")) Is Nothing Then
            If Target.Cells.Count > 1 Then Exit Sub
            If Target.Value = "" Then Target.Offset(0, 3).Value = ""
                Call DropDownListToDefault
End If
End Sub

Call DropDownListToDefault is a module:

Sub DropDownListToDefault()
    Dim oCell As Range

    For Each oCell In ActiveSheet.UsedRange.Cells
        If HasValidation(oCell) Then
            oCell.Value = "Property Reference Guide (Click Right Side Arrow to Start)"
        End If
    Next
End Sub

Function HasValidation(cell As Range) As Boolean
    Dim t: t = Null

    On Error Resume Next
    t = cell.Validation.Type
    On Error GoTo 0

    HasValidation = Not IsNull(t)
End Function

How do I get
Private Sub Worksheet_Change(ByVal Target As Range) to run without errors please?