hiding / unhiding columns across multiple sheets based on cell value

Steve_nyc92

New Member
Joined
Dec 17, 2020
Messages
23
Office Version
  1. 2016
Platform
  1. Windows
I'm having trouble getting the below code to work.

I have "dashboard" worksheet where the user selects a value from a drop-down in Cell C10. Based on the selections, Sheets 1-4 are selected together and Columns D through BA should all be hidden. If the selection is "Cars", then columns D:S, U:Z, and AB:AD should be unhidden across those worksheets.

My problem is that it is only hiding columns on Sheet 1 even though all sheets are selected. Then, when I want to unhide multiple columns (D:S, U:Z, AB:AD), I get a run-time error.

Any help is appreciated.

Thanks

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("C10")) Is Nothing Then Exit Sub
    Select Case Target.Value
        Case "Cars", ""
        Sheets(Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", )).Select
        Sheets("Sheet1").Activate
        ActiveSheet.Columns("D:BA").EntireColumn.Hidden = True
        ActiveSheet.Columns("D:S, U:Z, AB:AD").EntireColumn.Hidden = False
        
    
    
    Sheets("Dashboard").Select
            
    End Select

End Sub
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
With select case we normally use more than one choice.
I only see "Cars" as a choice.
Try this and then tell me what else we need.
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  7/14/2022  10:41:30 PM  EDT
If Target.Address = "$C$10" Then
    Dim i As Long
    Select Case Target.Value
        Case "Cars"
          For i = 1 To 4
            Sheets(i).Columns("D:BA").Hidden = True
            Next
            End Select
End If
End Sub
 
Upvote 0
Thanks...nothing seemed to happen.

I'm confused by your code. Basically the drop down filter is on a worksheet called "Dashboard". I want to hide columns D through BA on Worksheets 1, 2, 3, and 4 when someone select the value "Cars" from the drop down. If they select a different value like "Houses" it would hide different set of columns.
 
Upvote 0
Put my script on the sheet named Dashboard
When you enter Cars in Range ("C10") the script hides the rows shown in the script sheets 1 to 4

If you want this to also run when you enter other values, I need to know what other values.

You said:
If they select a different value like "Houses" it would hide different set of columns.
That is not specific. What values what different columns.
 
Upvote 0

Forum statistics

Threads
1,223,970
Messages
6,175,713
Members
452,667
Latest member
vanessavalentino83

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
Back
Top