Private Sub Workbook_Open() With ActiveSheet .Unprotect “Chief2" End If Worksheets("Sheet2").Activate Range("A1").Select Range("B16").Activate If Target.Address = "$B$16" Then Call Reset_Year End If .Protect "Chief2" End With End Sub MACRO Private Sub Worksheet_Change(ByVal Target As Range) Dim i As Long Range("B16") = InputBox("Type in the new value for Range(""B16"")") Sheets("BEGIN YEAR").Range("C10") = Sheets("26").Range("N28").Value 'Total Remaining Vacation (Days) Sheets("BEGIN YEAR").Range("C11") = Sheets("26").Range("N29").Value 'Total Remaining Sick Leave (Days) For i = 1 To 26 If i <= 9 Then With Sheets("0" & i) Union(.Range("B18:O22"), .Range("C33:C36"), .Range("C28:C29")).CLEARCONTENTS End With Else With Sheets(i) Union(.Range("B18:O22"), .Range("C33:C36"), .Range("C28:C29")).CLEARCONTENTS End With End If Next End Sub
Bookmarks