Hey all first time posting here hope i make sense.
Im looking to make a new rota to help reduce confusion and mistakes with colleagues shifts so started making a rota. Im pretty savvy with excell but have never really branched into VBA to further my knowledge but know ill need code to achieve what im after.
Essentially need to be able to populate each week with peoples names but if someone is entered into the rota lets say 4 times it will prompt the user that they have entered this person for to many shifts that week but only have this prompt appear once. I got the box to display but it continues to display no matter whos name i put into the rota, for example Ive put Tom into the rota 4 times the box appears warning me that ive entered him for too many shifts but this was overtime, i then add billy to the rota but the "Youve added Tom too many times this week" will appear again.
My current code is :
If thats not made any sense then i apologise, but essentially i just want that warning message to appear once.
Any help greatly apprieciated
Tom
Im looking to make a new rota to help reduce confusion and mistakes with colleagues shifts so started making a rota. Im pretty savvy with excell but have never really branched into VBA to further my knowledge but know ill need code to achieve what im after.
Essentially need to be able to populate each week with peoples names but if someone is entered into the rota lets say 4 times it will prompt the user that they have entered this person for to many shifts that week but only have this prompt appear once. I got the box to display but it continues to display no matter whos name i put into the rota, for example Ive put Tom into the rota 4 times the box appears warning me that ive entered him for too many shifts but this was overtime, i then add billy to the rota but the "Youve added Tom too many times this week" will appear again.
My current code is :
Code:
Private Sub Worksheet_Calculate()
If Range("B1") > 3 Then
MsgBox "Tom has too many shifts this week unless hes doing extra?"
End If
End Sub
If thats not made any sense then i apologise, but essentially i just want that warning message to appear once.
Any help greatly apprieciated
Tom