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.

 

[TheJay]

Ouch, that code added the text to each cell that had VLOOKUP in it, so I lose my validation...

 

[davesexcel]

I can't see a reason for all the code.
If you want to stop the worksheet events when change a cell.

application.enableevents=false
range("B2")="Something"
application.enableevents=true

 

[TheJay]

I need to display "Property Reference Guide" in B2 (where the dropdown list is held) when the sheet is first displayed and when the cell contents of B2 are cleared.

 

[davesexcel]

Yes, the code in previous post is how to stop worksheet events when you don't want it to kick in.

 

[TheJay]

I don't quite follow, the code I proposed was problematic and you've indicated that there is an alternative to all of that. The code you have referred to won't work in isolation, how do you imagine it is used within the entirety of the other code written for the sheet?

 

[davesexcel]

Yes, and you asked how to stop worksheet_change event from kicking in.
I showed you how to stop it from kicking in, run your code, then turn it on again.

Your original question has been answered.

 

[TheJay]

This works, but it stops working if I merge & centre:

Option Explicit

Private Sub Worksheet_Activate()
'    Call DropDownListToDefault
    Range("B2").Select
    With Worksheets("Property Numbering")
    With ActiveWindow
        .DisplayFormulas = False
        .DisplayHeadings = False
        .DisplayGridlines = False
        .DisplayHorizontalScrollBar = False
        .DisplayVerticalScrollBar = False
    End With
    With Application
        .DisplayFullScreen = True
        .DisplayFormulaBar = False
        .DisplayStatusBar = False
    End With
    With Application
        .CommandBars("Full Screen").Visible = True
        .CommandBars("Worksheet Menu Bar").Enabled = False
        .CommandBars("Standard").Visible = False
        .CommandBars("Formatting").Visible = False
    End With
End With
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim xObjV As Validation
    On Error Resume Next
    Set xObjV = Target.Validation
    If xObjV.Type = xlValidateList Then
    If IsEmpty(Target.Value) Then Target.Value = "Property Reference Guide (Click Right Side Arrow to Start)"
    End If
    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

 

[TheJay]

This code adds the message to any cell where the user presses delete, so again it's not optimal.