Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngArea As Range, ws As Worksheet
If Target.Address(0, 0) <> "M1" Then Exit Sub
If LCase(Target.Value) = "finish" Then
ActiveSheet.Unprotect Password:="pw"
For Each rngArea In Range("A3,f3,f4,A5,A7,a508:c508,a509:c509,a510:b510,A512:F512," & _
"a513:f513,c514,d514,c515,d515,c516,d516,a518:B518,a520:f521,a522:f522," & _
"A523:f525,a527:b527,c527,d527,e527,f527,a528:b528,c528,d528,e528," & _
"f528,a529:b529,c529,d529,e529,f529,a530:b530,c530,d530,e530," & _
"f530,a531:b531,c531,d531,e531,f531,a532:b532,c532,d532,e532," & _
"f532,a534:b534,c534,d534,e534,f534,a535:b535,c535,d535,e5235," & _
"f535,a537:f537,a539:f540,a541:f541,a542:f543,a545:f545,a546:f546," & _
"a547:f549,a550:e550,a551:e551,a552,a553:f553,a554,a555:b555,a556:b556," & _
"a557,a560,a561,a562,a563,a565").Areas
rngArea.Value = rngArea.Value
Next rngArea
With Range("A508:c508,M1").Validation
.Delete
.Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator:=xlBetween
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
With Range("A508:c508,d15,M1").Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
With Range("A508:c508").Borders(xlEdgeBottom).LineStyle = x1linestylenone
Range("A508:c508").Borders(xlEdgeTop).LineStyle = x1linestylenone
Range("A508:c508").Borders(xlEdgeRight).LineStyle = x1linestylenone
Range("A508:c508").Borders(xlEdgeLeft).LineStyle = x1linestylenone
Range("A508:c508").Borders(xlEdgeBottom).LineStyle = x1linestylenone
End With
Range("A5,A508:c508,c514:d514,C515:d515, c516:d516").Locked = True
With Range("m1").Font
.TintAndShade = 0
End With
With Range("M1").Clear
End With
Application.Goto Range("A1"), Scroll:=True
With Sheets("Summary Sheet")
.Unprotect Password:="pw"
.Range("a5").Value = .Value
.Range("a5").Locked = True
.Range("A8:F17").Locked = True
.Range("A8:F17").FormulaHidden = True
.Protect Password:="pw"
.EnableSelection = xlUnlockedCells
'Application.Goto .Range("A8:F17")
End With
'runit
For Each ws In Sheets(Array("AVF", "LUF", "MVF", "NTDD", "TDD"))
If ws.Visible Then
ws.Unprotect Password:="pw"
ws.Range("A7:F17").Locked = True
ws.Range("A8:F17").FormulaHidden = True
ws.Protect Password:="pw"
ws.EnableSelection = xlUnlockedCells
End If
Next ws
With Sheets("Letter")
.Protect Password:="pw"
.EnableSelection = xlUnlockedCells
End With
ChDrive "G:\Forms & Lists"
ChDir "G:\Forms & Lists"
ActiveWorkbook.SaveAs Filename:= _
"Please re-name me.xls", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
'Withint
ElseIf LCase(Target.Value) = "reveal" Then
Sheets("Info").Visible = True
Sheets("ci2").Visible = True
Sheets("Info").Select
End If
End Sub
Bookmarks