Link to home
Start Free TrialLog in
Avatar of James C
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!

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

Open in new window

Book1-JC.xlsm
SOLUTION
Avatar of Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj)
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of James C
James C

ASKER

Almost - the 'Customer Quote' page is showing with whichever selection I make.
Also, strangely, the code above is giving me an error when changing D8 from 'No' to 'Yes'.
Thank you
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of James C

ASKER

No that's not working either :(
What's not working? Do you get an error or what?
Avatar of James C

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.