Steve_nyc92
New Member
- Joined
- Dec 17, 2020
- Messages
- 23
- Office Version
- 2016
- Platform
- 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
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