Link to home
Start Free TrialLog in
Avatar of James C
James C

asked on

How to have a condition in one sheet that isn't overridden in another (VBA)

I have the following code in one sheet that hides certain rows depending on selections - this is entered by the administrator, the page then hidden before sending to public users:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 3 Then Exit Sub
If Target.Address(0, 0) = "I11" Then
        If Target = "Yes" Then
        Application.EnableEvents = False
        ActiveSheet.Shapes("Buttons").Visible = True
        Application.EnableEvents = True
    
        ElseIf Target = "No" Then
        Application.EnableEvents = False
        ActiveSheet.Shapes("Buttons").Visible = False
        Application.EnableEvents = True
End If

ElseIf Target.Address(0, 0) = "I15" Then
        Application.EnableEvents = True
        If Target = "No" Then
        ActiveSheet.Shapes("Button 16").Visible = False
        ActiveSheet.Shapes("Button 18").Visible = False
        ActiveSheet.Shapes("Button 19").Visible = False
        ActiveSheet.Shapes("Button 20").Visible = False
        ActiveSheet.Shapes("Button 23").Visible = False
        ActiveSheet.Shapes("Button 30").Visible = False
        ActiveSheet.Shapes("Button 34").Visible = False
        ActiveSheet.Shapes("Buttons1").Visible = False
        ActiveSheet.Shapes("Down Arrow 12").Visible = False
        ActiveSheet.Shapes("Down Arrow 11").Visible = False
        ActiveSheet.Shapes("Down Arrow 15").Visible = False
        Worksheets("SC").Visible = xlSheetVisible
        Worksheets("QSC").Visible = xlSheetVisible
        
        With Sheets("Calculation")
        .Unprotect Password:="hello01"
        .Rows("13:17").Hidden = False
        .Rows("29:29").Hidden = False
        .Protect Password:="hello01"
        End With

        With Sheets("INSERT")
        .Unprotect Password:="hello01"
        .Rows("20:21").Hidden = True
        .Protect Password:="hello01"
        End With

      ElseIf Target = "Yes" Then
        ActiveSheet.Shapes("Button 16").Visible = True
        ActiveSheet.Shapes("Button 18").Visible = True
        ActiveSheet.Shapes("Button 19").Visible = True
        ActiveSheet.Shapes("Button 20").Visible = True
        ActiveSheet.Shapes("Button 23").Visible = True
        ActiveSheet.Shapes("Button 30").Visible = True
        ActiveSheet.Shapes("Button 34").Visible = True
        ActiveSheet.Shapes("Buttons1").Visible = True
        ActiveSheet.Shapes("Down Arrow 12").Visible = True
        ActiveSheet.Shapes("Down Arrow 11").Visible = True
        ActiveSheet.Shapes("Down Arrow 15").Visible = True
        Worksheets("SC").Visible = xlSheetVeryHidden
        Worksheets("QSC").Visible = xlSheetVeryHidden
        Application.EnableEvents = True
    
     
        With Sheets("Calculation")
        .Unprotect Password:="hello01"
        .Rows("13:17").Hidden = True
        .Rows("29:29").Hidden = True
        .Protect Password:="hello01"
        End With
        
        With Sheets("INSERT")
        .Unprotect Password:="hello01"
        .Rows("20:21").Hidden = False
        .Protect Password:="hello01"
        End With
        End If
        
        Application.EnableEvents = True
          
ElseIf Target.Address(0, 0) = "I20" Then
    
    Application.EnableEvents = False
    
    If Range("I20").Value = "2-5 years" Then
        Sheets("Calculation").Unprotect Password:="hello01"
        Sheets("Calculation").Columns("Q:V").Hidden = True
        Sheets("Calculation").Protect Password:="hello01"
    ElseIf Range("I20").Value = "2-6 years" Then
        Sheets("Calculation").Unprotect Password:="hello01"
        Sheets("Calculation").Columns("T:V").Hidden = True
        Sheets("Calculation").Columns("Q:S").Hidden = False
        Sheets("Calculation").Protect Password:="hello01"
    ElseIf Range("I20").Value = "2-7 years" Then
        Sheets("Calculation").Unprotect Password:="hello01"
        Sheets("Calculation").Columns("Q:V").Hidden = False
        Sheets("Calculation").Protect Password:="hello01"
    End If
    
    Application.EnableEvents = True
    
End If
Application.EnableEvents = True
End Sub

Open in new window


And then the following code in another sheet (which is visible to public users).  The problem is that when they change cell D3, certain selections will unhide rows.  These rows may have been set to hide by the administrator on the 'Insert' page and I don't want the public user to be able to override selections in the 'Insert' sheet - is there any way to get around this?

Also, there's a lot of Protect/Unprotect going on - I vaguely remember some sort of coding that protects users from changing anything but VBA can work in the background?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
Dim dws As Worksheet
Dim sh
Set dws = Sheets("Calculation")
If Target.Address(0, 0) = "D8" Then
    Application.EnableEvents = False
    Sheets("Calculation").Unprotect Password:="hello01"
    Rows("31:33").Hidden = False
    Sheets("Calculation").Protect Password:="hello01"
    If Range("D8").Value = "No" Then
       Sheets("Calculation").Unprotect Password:="hello01"
       Rows("31:33").Hidden = True
       Sheets("Calculation").Protect Password:="hello01"
    ElseIf Range("D8").Value = "Yes" Then
       Sheets("Calculation").Unprotect Password:="hello01"
       Rows("31:32").Hidden = False
       Sheets("Calculation").Protect Password:="hello01"
    End If
    Application.EnableEvents = True
    
ElseIf Target.Address(0, 0) = "D3" Then
    Application.EnableEvents = False
    Sheets("Calculation").Unprotect Password:="hello01"
    Rows("12:12").Hidden = False
    Sheets("Calculation").Protect Password:="hello01"
    If Range("D3").Value = "Hire Purchase" Then
       Sheets("Calculation").Unprotect Password:="hello01"
       Rows("10:10").Hidden = True
       Rows("12:12").Hidden = False
       Rows("21:21").Hidden = False
       Rows("25:26").Hidden = True
       Rows("31:31").Hidden = False
       Sheets("Calculation").Protect Password:="hello01"
        With Sheets("CQ")
        .Unprotect Password:="hello01"
        .Rows("32:32").Hidden = False
        .Protect Password:="hello01"
        End With
        With Sheets("CQ 2 options")
        .Unprotect Password:="hello01"
        .Rows("32:32").Hidden = False
        .Protect Password:="hello01"
        End With
        With Sheets("CQ 3 options")
        .Unprotect Password:="hello01"
        .Rows("32:32").Hidden = False
        .Protect Password:="hello01"
        End With
    
    ElseIf Range("D3").Value = "Lease" Then
       Sheets("Calculation").Unprotect Password:="hello01"
       Rows("10:10").Hidden = False
       Rows("12:12").Hidden = True
       Rows("21:21").Hidden = False
       Rows("31:33").Hidden = True
       Sheets("Calculation").Protect Password:="hello01"
        With Sheets("CQ")
        .Unprotect Password:="hello01"
        .Rows("32:32").Hidden = False
        .Protect Password:="hello01"
        End With
        
        With Sheets("CQ 2 options")
        .Unprotect Password:="hello01"
        .Rows("32:32").Hidden = False
        .Protect Password:="hello01"
        End With
        With Sheets("CQ 3 options")
        .Unprotect Password:="hello01"
        .Rows("32:32").Hidden = False
        .Protect Password:="hello01"
        End With
        
    ElseIf Range("D3").Value = "L" Then
       Sheets("Calculation").Unprotect Password:="hello01"
        Range("D8:G8").Select
        ActiveCell.FormulaR1C1 = "0"
       Rows("8:10").Hidden = True
       Rows("12:12").Hidden = True
       Rows("21:21").Hidden = True
       Rows("25:26").Hidden = True
       Rows("31:31").Hidden = False
       Rows("32:35").Hidden = True
    Sheets("Calculation").Protect Password:="hello01"
    
        With Sheets("CQ")
        .Unprotect Password:="hello01"
        .Rows("32:32").Hidden = True
        .Protect Password:="hello01"
        End With
        With Sheets("CQ 2 options")
        .Unprotect Password:="hello01"
        .Rows("32:32").Hidden = True
        .Protect Password:="hello01"
        End With
        With Sheets("CQ 3 options")
        .Unprotect Password:="hello01"
        .Rows("32:32").Hidden = True
        .Protect Password:="hello01"
        End With
       
    End If
    Application.EnableEvents = True
    

ElseIf Target.Address(0, 0) = "D4" Then
    Application.EnableEvents = False
    Sheets("Calculation").Unprotect Password:="hello01"
    Rows("5:5").Hidden = False
    Sheets("Calculation").Protect Password:="hello01"
    If Range("D4").Value = "RA" Then
       Sheets("Calculation").Unprotect Password:="hello01"
       Rows("5:5").Hidden = True
       Rows("13:13").Hidden = True
       Sheets("Calculation").Protect Password:="hello01"
    ElseIf Range("D4").Value = "EC" Then
       Sheets("Calculation").Unprotect Password:="hello01"
       Rows("5:5").Hidden = False
       Rows("13:13").Hidden = False
       Sheets("Calculation").Protect Password:="hello01"
    End If
    Application.EnableEvents = True

ElseIf Target.Address(0, 0) = "I4" Then
    If Target <> "" Then
        On Error Resume Next
        For Each sh In Array("CQ", "CQ 2 options", "CQ 3 options")
            Sheets(sh).Visible = False
        Next sh
        Select Case Target.Value
            Case "1 option"
                Sheets("CQ").Visible = True
            Case "2 options"
                Sheets("CQ 2 options").Visible = True
            Case "3 options"
                Sheets("CQ 3 options").Visible = True
        End Select
    Else
        For Each sh In Array("CQ", "CQ 2 options", "CQ 3 options")
            Sheets(sh).Visible = True
        Next sh
    End If
    Application.EnableEvents = True
End If
End Sub

Open in new window


Thanks
SOLUTION
Avatar of Rob Henson
Rob Henson
Flag of United Kingdom of Great Britain and Northern Ireland 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

Thanks @Rob Henson
Does that mean I can delete all the Unprotect / Protect lines in the code if I insert your code at the top?
ASKER CERTIFIED SOLUTION
Avatar of Roy Cox
Roy Cox
Flag of United Kingdom of Great Britain and Northern Ireland 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

Thanks Roy.

How do I change the code from:
    Dim oWs As Worksheet
    'protect all worksheets with UserInterfaceOnly
    For Each oWs In ThisWorkbook.Worksheets
        oWs.Protect Password:="secret", UserInterFaceOnly:=True
    Next oWs

Open in new window


so that it protects all sheets, not just open worksheets?  Some of the sheets are hidden upon opening but may be visible depending upon selections.

To be honest, I'm more interested in the answer to the IF AND question above.  Thanks.
Avatar of Roy Cox
Roy Cox
Flag of United Kingdom of Great Britain and Northern Ireland image
That code will protect hidden and unhidden sheets.

can you attach an example of your workbook and I'll have a look at the code.
Avatar of Tracy
Tracy
Flag of United States of America image
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.