Dropdown that shows and hides certain rows

TheJay

Active Member
Joined
Nov 12, 2014
Messages
354
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

davesexcel

Well-known Member
Joined
Feb 26, 2006
Messages
1,367
Place the dropdown in A1

Place this in the worksheet module

VBA Code:
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.
 
Upvote 0

TheJay

Active Member
Joined
Nov 12, 2014
Messages
354
Office Version
  1. 2019
Platform
  1. Windows
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?
 
Upvote 0

TheJay

Active Member
Joined
Nov 12, 2014
Messages
354
Office Version
  1. 2019
Platform
  1. Windows
Like this?

VBA Code:
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
 
Last edited:
Upvote 0

TheJay

Active Member
Joined
Nov 12, 2014
Messages
354
Office Version
  1. 2019
Platform
  1. Windows
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.

 
Upvote 0

TheJay

Active Member
Joined
Nov 12, 2014
Messages
354
Office Version
  1. 2019
Platform
  1. Windows
Oh, I guess we have it backwards.
Okay, so:

VBA Code:
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:

VBA Code:
=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?
 
Upvote 0

TheJay

Active Member
Joined
Nov 12, 2014
Messages
354
Office Version
  1. 2019
Platform
  1. Windows
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):
VBA Code:
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:

VBA Code:
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?
 
Upvote 0

Forum statistics

Threads
1,186,258
Messages
5,956,867
Members
438,272
Latest member
Drock74

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top