We help IT Professionals succeed at work.
Get Started

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

137 Views
Last Modified: 2017-11-07
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
Comment
Watch Question
Group Finance Manager
CERTIFIED EXPERT
Commented:
This problem has been solved!
Unlock 2 Answers and 6 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE