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:
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?
Thanks
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
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
Thanks
* CodingVBAMicrosoft ExcelMicrosoft Office
Last Comment
Tracy