Sub resetDV()
'
' Kirk Rice 9/21/2022
' resetDV Macro is run to automatically reset Data Validation to enable dropdown selection of staff available for a specified shift and day
' Uses three Named Ranges in the Excel workbook
'
Dim AvailableNames, codeDS, cntAvail
'
' Overwrite top cells for each day with "1", a valid day everywhere...to prevent terminaton of code execution when an invalid blank "day" number is encounterd.
'
CalV2.Range("B4:H4,B9:H9,B14:H14,B19:H19,B24:H24,B29:H29").Value = 1
'
' Re-enter Data Validation formulas to re-activate dropdown selection
With CalV2.Range("B5:H8").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=OFFSET(INDEX(AvailableNames,MATCH(1,--(codeDS=B$4&""-""&$A5),0),),,,,INDEX(cntAvail,MATCH(1,--(codeDS=B$4&""-""&$A5),0)))"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
With CalV2.Range("B10:H13").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=OFFSET(INDEX(AvailableNames,MATCH(1,--(codeDS=B$9&""-""&$A10),0),),,,,INDEX(cntAvail,MATCH(1,--(codeDS=B$9&""-""&$A10),0)))"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
With CalV2.Range("B15:H18").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=OFFSET(INDEX(AvailableNames,MATCH(1,--(codeDS=B$14&""-""&$A15),0),),,,,INDEX(cntAvail,MATCH(1,--(codeDS=B$14&""-""&$A15),0)))"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
With CalV2.Range("B20:H23").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=OFFSET(INDEX(AvailableNames,MATCH(1,--(codeDS=B$19&""-""&$A20),0),),,,,INDEX(cntAvail,MATCH(1,--(codeDS=B$19&""-""&$A20),0)))"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
With CalV2.Range("B25:H28").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=OFFSET(INDEX(AvailableNames,MATCH(1,--(codeDS=B$24&""-""&$A25),0),),,,,INDEX(cntAvail,MATCH(1,--(codeDS=B$24&""-""&$A25),0)))"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
With CalV2.Range("B30:H33").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=OFFSET(INDEX(AvailableNames,MATCH(1,--(codeDS=B$29&""-""&$A30),0),),,,,INDEX(cntAvail,MATCH(1,--(codeDS=B$29&""-""&$A30),0)))"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Range("A1").Select
'
' Overwrite temporary "valid" 1's with formula to display the correct day numbers aligned under the days of the week headings
'
CalV2.Range("B4:H4,B9:H9,B14:H14,B19:H19,B24:H24,B29:H29").Formula = _
"=IF(OR(7*INT((ROWS($4:4)-1)/5)+COLUMNS($B:B)-WEEKDAY($B$1)+1>DAY(EOMONTH($B$1,0)),7*INT((ROWS($4:4)-1)/5)+COLUMNS($B:B)-WEEKDAY($B$1)+1<1),"""",7*INT((ROWS($4:4)-1)/5)+COLUMNS($B:B)-WEEKDAY($B$1)+1)"
'
End Sub