James C
asked on
How do I hide sheets based on cell selection with code already in the object?
I've got some code already in an object and want to hide different sheets depending on cell selection.
My code is as follows (example attached).
Thanks in advance!
My code is as follows (example attached).
Thanks in advance!
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
Dim dws As Worksheet
Set dws = Sheets("Calculation")
If Target.Address(0, 0) = "D8" Then
dws.Rows("31:32").Hidden = False
Application.EnableEvents = False
If Range("D8").Value = "No" Then
dws.Rows("31:32").Hidden = True
ElseIf Range("D8").Value = "Yes" Then
dws.Rows("31:32").Hidden = False
End If
Application.EnableEvents = True
ElseIf Target.Address(0, 0) = "I4" Then
Sheets("Customer Quote").Select
ActiveWindow.SelectedSheets.Visible = True
Sheets("Customer Quote 2 options").Select
ActiveWindow.SelectedSheets.Visible = True
Sheets("Customer Quote 3 options").Select
ActiveWindow.SelectedSheets.Visible = True
Application.EnableEvents = False
If Range("I4").Value = "1 option" Then
Sheets("Customer Quote 2 options").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Customer Quote 3 options").Select
ActiveWindow.SelectedSheets.Visible = False
ElseIf Range("I4").Value = "2 options" Then
Sheets("Customer Quote 3 options").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Customer Quote").Select
ActiveWindow.SelectedSheets.Visible = False
ElseIf Range("I4").Value = "3 options" Then
Sheets("Customer Quote 2 options").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Customer Quote").Select
ActiveWindow.SelectedSheets.Visible = False
End If
Application.EnableEvents = True
End If
End Sub
Book1-JC.xlsm
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
No that's not working either :(
What's not working? Do you get an error or what?
ASKER
It's ok, My 'Customer Quote' page had a space at the end of it 'Customer Quote ' - my error, thank you once again!!
You're welcome James! Glad to help.
ASKER
Also, strangely, the code above is giving me an error when changing D8 from 'No' to 'Yes'.
Thank you