Making a rota need a bit of help with a VBA code

[inman09]

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 :

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

 

[seguin85]

Can you provide an example of your rota? It will help to see what columns or rows the data is in

 

[inman09]

Can you provide an example of your rota? It will help to see what columns or rows the data is in

Where can i upload a copy of the excel file? or a screen shot ? or would you like me to use the table builder ?

 

[Joe4]

There are tools you can use to post screen images. They are listed in Section B of this link here: Guidelines for Forum Use.

Also, there is a Test Here forum on this board that you can use to test out these tools to make sure they are working correctly before using them in your question.

 

[inman09]

[URL="http://

Excel 2010
	B	C	D	E	F	G	H	I
3	Week One
4	Date		01/01/1900	Excel 2010BCDEFGHI3Week One4Date01/01/190002/01/190003/01/190004/01/190005/01/190006/01/19005Res/DaySundayMondayTuesdayWednesdayThursdayFridaySaturday6Res 1,2,37Res 1,2,38Res 59Res 610Kinnon 111Kinnon HCA1213Annual Leave14Training
	B	C	D	E	F	G	H	I
3	Week One
4	Date		01/01/1900	02/01/1900	03/01/1900	04/01/1900	05/01/1900	06/01/1900
5	Res/Day	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday
6	Res 1,2,3
7	Res 1,2,3
8	Res 5
9	Res 6
10	Kinnon 1
11	Kinnon HCA
12
13	Annual Leave
14	Training
Rota

[/URL]

 

[seguin85]

So if an employee's name is listed 4 or more times in cells C6:I11 you want the message to pop-up?

 

[inman09]

I used the 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

This will make it pop up with my message but i want it to only display this message once so not re test the IF statement when i type another employees name into the rota cause at present if i have "Tom" in the rota 4 times then close the message and type "Billy" it will pop up again and say youve added Tom to many times this week.

 

[seguin85]

This code will only activate when the B1 is changed and will only count the value that is located in B1:

Private Sub Worksheet_Change(ByVal Target As Range)    
If Not Intersect(Target, Target.Worksheet.Range("B1")) Is Nothing Then
    If Application.CountIf(Range("B5:H11"), Range("B1").Value) > 3 Then
         MsgBox Range("B1").Value & " has too many shifts this week unless he is doing extra?"
    End If
End If
End Sub

 

[inman09]

Hey sorry for the late reply wasnt at my computer till this afternoon

Ive tried out this code and im probably doing something wrong but nothing is diplayed when i input Tom more than 4 times. If it makes any difference on the second sheet i have a list of names with COUNTIF's referenced to the rotas and there names so B1 is on another sheet and counts how many toms are in the rota on sheet 1

 

[seguin85]

The code is design to only activate when B1 is changed. What happens if you change cell B1 to Tom (even if it is already labelled as Tom).