Count and warn if number is exceeded

[Eric Carolus]

Hi folks.
I am setting up an invigilation roster in which an educator can only be assigned once per day per room. If you attempt to assign the educator again for the same day, then Excel needs warns you and delete your second assignment of the educator. I can do this for one day, and it works for one day, but I I struggle have this done for, say, 10 days (with different dates). In other words in Range(“C5:R5”) if there is an attempt to place any teacher more than once, then Excel must warn me.
I have managed this with the code below. I wish to do the same for the ranges (C6:R6, C7:R7, C8:R8), etc.

The abbreviations on the right are the codes referring to teachers. The numbers on the RIGHT simply refers to the total of times the teacher had been assigned on the whole grid.
My code for the topmost row (C5:R5), as follows:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim iVal As Integer 'Correcct
Dim myRange As Range 'Correct


Set myRange = Application.ActiveWorkbook.Sheets("CountPeriods").Range("C5:R5")


iVal = Application.WorksheetFunction.CountIf(myRange, "AC") Or Application.WorksheetFunction.CountIf(myRange, "ZAM") Or Application.WorksheetFunction.CountIf(myRange, "ZS") _
Or Application.WorksheetFunction.CountIf(myRange, "SF") Or Application.WorksheetFunction.CountIf(myRange, "LJ") Or Application.WorksheetFunction.CountIf(myRange, "NL") _
Or Application.WorksheetFunction.CountIf(myRange, "LM") Or Application.WorksheetFunction.CountIf(myRange, "AMJ") Or Application.WorksheetFunction.CountIf(myRange, "AMV") _
Or Application.WorksheetFunction.CountIf(myRange, "VN") Or Application.WorksheetFunction.CountIf(myRange, "MN") Or Application.WorksheetFunction.CountIf(myRange, "NQ") _
Or Application.WorksheetFunction.CountIf(myRange, "SR") Or Application.WorksheetFunction.CountIf(myRange, "GS") Or Application.WorksheetFunction.CountIf(myRange, "DS") _
Or Application.WorksheetFunction.CountIf(myRange, "CS") Or Application.WorksheetFunction.CountIf(myRange, "PS") Or Application.WorksheetFunction.CountIf(myRange, "JT") _
Or Application.WorksheetFunction.CountIf(myRange, "MY")


If iVal > 1 Then
On Error Resume Next
MsgBox "This teacher had ALREADY been entered once!"
Selection.Offset(0, -1).ClearContents
Selection.Offset(0, -1).Select
Exit Sub
End If

End Sub

Please help.

Thanks in advance.

 

[Andrew Fergus]

Hello Eric

What you want to do can be done without macros. You can use a helper cell and/or conditional formatting to highlight entries that meet certain criteria (e.g. the name count for a specific day exceeds 1).

Can you post an image of your data layout?

 

[Eric Carolus]

Hallo Andrew

If you would be so kind and provide me
with an email address I can mail you the excel
workbook.

Thank you.

Eric

 

[Andrew Fergus]

Hello Eric
There are tools available to show your sheet online.
Andrew

 

[Michael M]

Have a look at my tag for the HTML Maker or upload the workbook to Dropbox and post a lnk back here.

 

[Macropod]

If you would be so kind and provide me
with an email address I can mail you the excel
workbook.

As per Rule 4 (https://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html) kindly don't invite people to take the question off the forum by emailing content relevant to a discussion here.

 

[Eric Carolus]

Hi folks

I apologize unreservedly. I did not know how to upload
the image of the workbook and that is why I asked for an email address.
Again, my apologies.
Please accept my apology.

I will try to upload the image.

Eric

 

[Eric Carolus]

Hi folks

I have managed to make a link to
spreadsheet.

https://www.dropbox.com/s/xx688n950aek89q/invigilation.xlsm?dl=0

I hope this helps'

Eric

 

[Andrew Fergus]

Hello Eric

I went for a slight redesign on your spreadsheet so that no macros are required. Please download the file from here:
https://www.dropbox.com/s/9t9ofnh0qilhy1h/Invigilation.xlsx?dl=0

Notice I set up the teachers on the sheet 'Data (2)' as a table and I also created a named range for the list of abbreviations (see Ribbon > Formulas > Name Manager)

On 'Count Periods (2)' I changed the data validation to look at the list of abbreviations per the Name Manager so that the data validation is linked to the table (change the table and the names available in the drop down box also change).

I then used conditional formatting on each row in 'Count Periods (2)' - see Ribbon > Home > Conditional Formatting so that any time you have a teacher selected more than once on a row, then both entries are highlighted orange until you delete one of them.

The other thing I changed was the list of teachers and their count and load - all of this is now calculated automatically by Excel - so whenever you add a new teacher to the table on 'Data (2)' then this list will also update itself automatically.

I trust this helps.

Andrew

 

[Eric Carolus]

Hi Andrew
Thank you very much!
It works like a charm!

Eric