Sub Workbook_Open()
Dim MyUser, MyName, MyAccess As String
Dim MyTable As Range
Dim MySheet As Worksheet
Dim MyBook As Workbook
Application.ScreenUpdating = False
Get_User_Name lpBuff, 25
Set MyTable = Range("Tbl_Users")
MyUser = GetUserName
MyName = Application.WorksheetFunction.VLookup(MyUser, MyTable, 2, False)
MyAccess = Application.WorksheetFunction.VLookup(MyUser, MyTable, 3, False)
Set MyBook = ActiveWorkbook
UnprotectWorkbook
Select Case MyAccess
Case "Full"
For Each MySheet In MyBook.Worksheets
MySheet.Visible = True
MySheet.Unprotect Password:=DontTell
MySheet.Range("A:CB").EntireColumn.Hidden = False
MySheet.Protect Contents:=True, AllowFormattingColumns:=False, AllowFiltering:=True, userInterfaceOnly:=True, Password:=DontTell
Next MySheet
Case "All branches"
For Each MySheet In MyBook.Worksheets
MySheet.Visible = True
If MySheet.Name = "Summary" Or MySheet.Name = "List" Or _
MySheet.Name = "SYD_P" Or MySheet.Name = "SYD_R" Or MySheet.Name = "SYD_S" Or _
MySheet.Name = "SYD" Or MySheet.Name = "MEL" Or MySheet.Name = "BRS" Then
MySheet.Visible = True
MySheet.Unprotect Password:=DontTell
MySheet.Range("A:CB").EntireColumn.Hidden = False
MySheet.Protect Contents:=True, AllowFormattingColumns:=False, AllowFiltering:=True, userInterfaceOnly:=True, Password:=DontTell
Else
MySheet.Visible = False
End If
Next MySheet
Case "Summary"
For Each MySheet In MyBook.Worksheets
MySheet.Visible = True
If MySheet.Name <> "Summary" Or MySheet.Name <> "List" Then MySheet.Visible = False
Next MySheet
Sheets("List").Unprotect Password:=DontTell
Sheets("List").Cells.EntireColumn.Hidden = False
Sheets("List").Protect Contents:=True, AllowFormattingColumns:=False, AllowFiltering:=True, userInterfaceOnly:=True, Password:=DontTell
Case "SYD_All"
For Each MySheet In MyBook.Worksheets
MySheet.Visible = True
If MySheet.Name = "SYD_P" Or MySheet.Name = "SYD_R" Or MySheet.Name = "SYD_S" Or MySheet.Name = "SYD" Then
MySheet.Visible = True
MySheet.Unprotect Password:=DontTell
MySheet.Range("A:BQ,BX").EntireColumn.Hidden = False
MySheet.Range("BQ:BW,BY:BY,CA:CA").EntireColumn.Hidden = True
MySheet.Protect Contents:=True, AllowFormattingColumns:=False, AllowFiltering:=True, userInterfaceOnly:=True, Password:=DontTell
Else
MySheet.Visible = False
End If
Next MySheet
Case Else
For Each MySheet In MyBook.Worksheets
MySheet.Visible = True
If MySheet.Name <> MyAccess Then MySheet.Visible = False
MySheet.Unprotect Password:=DontTell
MySheet.Range("A:BQ,BX:BX").EntireColumn.Hidden = False
MySheet.Range("BQ:BW,BY:CA").EntireColumn.Hidden = True
MySheet.Protect Contents:=True, AllowFormattingColumns:=False, AllowFiltering:=True, userInterfaceOnly:=True, Password:=DontTell
Next MySheet
End Select
Sheets("Sheet1").Visible = False
ProtectAllSheets
ProtectWorkbook
'Activate timesheet
Sheets(Range("N_Branch").Value).Activate
ConditionFormat
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
Sub ConditionFormat(Optional CondiForm As String)
Sheets(Range("N_Branch").Value).Activate
UnprotectSheet
' Find First, Mid and Last rows
Set FindRow = Columns("A:A").Find(What:="Week ending:", LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True)
MyFirstRow = FindRow.Row + 2
Set FindRow = Columns("A:A").Find(What:="FROM OTHER BRANCHES:", LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True)
MyMidRow = FindRow.Row
Set FindRow = Columns("A:A").Find(What:="TOTAL", LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True)
MyLastRow = FindRow.Row - 2
' Delete all conditional formatting rules in sheet
Cells.FormatConditions.Delete
' Conditional formatting for duplicate names
With Range("A" & MyFirstRow, "A" & MyLastRow)
.FormatConditions.AddUniqueValues
With .FormatConditions.AddUniqueValues
.DupeUnique = xlDuplicate
.Interior.ColorIndex = 3 'Red
End With
End With
' Conditional formatting for other branch employees shown in branch
With Range("B" & MyFirstRow, "B" & MyMidRow - 1)
.FormatConditions.Add Type:=xlExpression, Formula1:="=isnumber(search(""Not"",$B" & MyFirstRow & "))"
.FormatConditions(1).Interior.ColorIndex = 3 'Red
End With
' Conditional formatting for Branch employees shown as From other branches
With Range("B" & MyMidRow + 1, "B" & MyLastRow)
.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=$B$2"
.FormatConditions(1).Interior.ColorIndex = 3 'Red
End With
' Conditional formatting for negative numbers
With Range("D" & MyFirstRow & ":" & "I" & MyLastRow _
& "," & "M" & MyFirstRow & ":" & "R" & MyLastRow _
& "," & "V" & MyFirstRow & ":" & "AA" & MyLastRow _
& "," & "AE" & MyFirstRow & ":" & "AJ" & MyLastRow _
& "," & "AN" & MyFirstRow & ":" & "AO" & MyLastRow _
& "," & "AS" & MyFirstRow & ":" & "AT" & MyLastRow _
& "," & "AX" & MyFirstRow & ":" & "BC" & MyLastRow)
.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:="=0"
.FormatConditions(1).Interior.ColorIndex = 3 'Red
.FormatConditions(1).Font.ColorIndex = 1 'Black
End With
' Conditional formatting for Normal hours
With Range("D" & MyFirstRow & ":" & "D" & MyLastRow _
& "," & "M" & MyFirstRow & ":" & "M" & MyLastRow _
& "," & "V" & MyFirstRow & ":" & "V" & MyLastRow _
& "," & "AE" & MyFirstRow & ":" & "AE" & MyLastRow _
& "," & "AX" & MyFirstRow & ":" & "AX" & MyLastRow)
.FormatConditions.Add Type:=xlExpression, Formula1:="=AND(D4<>0,D4<>$C4)"
.FormatConditions(2).Interior.ColorIndex = 44 'Orange
End With
' Conditional formatting for OTx1.5
With Range("E" & MyFirstRow & ":" & "E" & MyLastRow _
& "," & "N" & MyFirstRow & ":" & "N" & MyLastRow _
& "," & "W" & MyFirstRow & ":" & "W" & MyLastRow _
& "," & "AF" & MyFirstRow & ":" & "AF" & MyLastRow _
& "," & "AY" & MyFirstRow & ":" & "AY" & MyLastRow)
.FormatConditions.Add Type:=xlExpression, Formula1:="=AND(E4<>0,D4<>$C4)"
.FormatConditions(2).Interior.ColorIndex = 44 'Orange
End With
' Conditional formatting for OTx2
With Range("F" & MyFirstRow & ":" & "F" & MyLastRow _
& "," & "O" & MyFirstRow & ":" & "O" & MyLastRow _
& "," & "X" & MyFirstRow & ":" & "X" & MyLastRow _
& "," & "AG" & MyFirstRow & ":" & "AG" & MyLastRow _
& "," & "AZ" & MyFirstRow & ":" & "AZ" & MyLastRow)
.FormatConditions.Add Type:=xlExpression, Formula1:="=AND(F4<>0,D4<>$C4)"
.FormatConditions(2).Interior.ColorIndex = 44 'Orange
End With
' Conditional formatting for RDO
With Range("G" & MyFirstRow & ":" & "G" & MyLastRow _
& "," & "P" & MyFirstRow & ":" & "P" & MyLastRow _
& "," & "Y" & MyFirstRow & ":" & "Y" & MyLastRow _
& "," & "AH" & MyFirstRow & ":" & "AH" & MyLastRow _
& "," & "BA" & MyFirstRow & ":" & "BA" & MyLastRow)
.FormatConditions.Add Type:=xlExpression, Formula1:="=AND(G4<>0,D4<>0)"
.FormatConditions(2).Interior.ColorIndex = 44 'Orange
End With
' Conditional formatting for Sick
With Range("H" & MyFirstRow & ":" & "H" & MyLastRow _
& "," & "Q" & MyFirstRow & ":" & "Q" & MyLastRow _
& "," & "Z" & MyFirstRow & ":" & "Z" & MyLastRow _
& "," & "AI" & MyFirstRow & ":" & "AI" & MyLastRow _
& "," & "BB" & MyFirstRow & ":" & "BB" & MyLastRow)
.FormatConditions.Add Type:=xlExpression, Formula1:="=AND(H4<>0,D4<>0)"
.FormatConditions(2).Interior.ColorIndex = 44 'Orange
End With
' Data validation for up to 1 decimal
With Range("C" & MyFirstRow & ":" & "C" & MyLastRow _
& "," & "D" & MyFirstRow & ":" & "I" & MyLastRow _
& "," & "M" & MyFirstRow & ":" & "R" & MyLastRow _
& "," & "V" & MyFirstRow & ":" & "AA" & MyLastRow _
& "," & "AE" & MyFirstRow & ":" & "AJ" & MyLastRow _
& "," & "AN" & MyFirstRow & ":" & "AO" & MyLastRow _
& "," & "AS" & MyFirstRow & ":" & "AT" & MyLastRow _
& "," & "AX" & MyFirstRow & ":" & "BC" & MyLastRow).Validation
.Delete
.Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _
xlGreaterEqual, Formula1:="=MOD(10*D4,1)=0"
.IgnoreBlank = True
.InCellDropdown = False
.InputTitle = ""
.ErrorTitle = "Too many decimals"
.InputMessage = ""
.ErrorMessage = "Only one decimal allowed."
.ShowInput = False
.ShowError = True
End With
ProtectSheet
ProtectWorkbook
End Sub