VBA to Prevent Data Entry in Cell with Data Validation List

Afternoon, all!

I'm after a little help and guidance from the VBA wizards, please.

I have the following set-up (this is just a simple example, but properly reflects the real layout):

Excel 2016 (Windows) 32 bit

	A	B	C	D	E	F	G	H	I	J
1	Pupil	Subject				A	No. A		Subject	Capacity
2	1	Ar				Ar	1		Ar	10
3	2	Gm				BS	0		BS	5
4	3	MS				Ge	5		CP	5
5	4	Ge				Gm	1		ClasCi	5
6	5	Ge				Hi	0		DT	5
7	6	Ge				MS	1		Dr	5
8	7	Ge				SS	0		FT	5
9	8	Ge							Fr	5
10	9								GPE	5
11	10								Ge	5
12									Gm	5
13									Hi	5
14									La	5
15									MS	5
16									Mu	5
17									RE	5
18									SS	5
19									Sp	5

Sheet: Sheet1

Column B contains subject choices with drop-down list selection.

F2:G8 are conditionally formatted to turn red when capacity is reached for that subject (see the capacity grid far right).

Once capacity is reached in column B, if someone tries to change any existing entries to that subject OR add another entry of that subject (e.g. in B10), I would like a pop-up message ("Subject full - choose a different option") and for the user to be stopped from entering the subject in question into that column.

In other words, there cannot be more that 5 instances of Ge in the subject column.

However, if someone wishes to change any existing Ge to something else, the drop-down still needs to work, so I don't want the cell locking.

Is this something that is easy to do with VBA?

Something like this?

Please Login or Register  to view this content.

Thanks, Rory. I am about to try this. I will let you know how I get on.

OK - so I have probably done something wrong, as it isn't doing anything.

Where and how should I add the code? I tried right-clicking Sheet1 in the VBA editor and pasting the code - no luck. Then I did the same for This Workbook - still nothing. What have I not done correctly, please?

If it's not to lock but just not allow to change then you could probably add a warning when defining the dropdown list which will only offer the options retry or cancel but it will still allow to lower the value entered

You can use this "Custom" formula in with Data Validation in B2:B10: =(COUNTIF($B$2:$B2, $B2)<6)
In the Data Validation "Error Alert" tab you can enter Subject full - choose a different option.

I did it in the red cell, the max is linked to the value in the J column

Originally Posted by Keebellah

If it's not to lock but just not allow to change then you could probably add a warning when defining the dropdown list which will only offer the options retry or cancel but it will still allow to lower the value entered

I don't understand what you are suggesting. The drop-down entry is not a numeric value.

Originally Posted by Keebellah

I did it in the red cell, the max is linked to the value in the J column

Thanks, but that's not what I am looking for. The data entry is in column B - that's where I need the VBA to work. I can't use data validation as these cells already have a data validation list. I do not just want a message, either - I want to stop the data entry.

Originally Posted by Mumps1

You can use this "Custom" formula in with Data Validation in B2:B10: =(COUNTIF($B$2:$B2, $B2)<6)
In the Data Validation "Error Alert" tab you can enter Subject full - choose a different option.

No, the cells in question already have data validation (a drop-down list).

If you can tell me what I've done wrong with Rory's VBA, I'd be grateful.

You had put the code into a normal module, not the sheet module.

The code goes in the worksheet's module but I don't know what it's supposed to do

Rory - thanks. I knew it would be a silly error. I'll try again!

Noticed it throws an error when you clear a cell with a value
Add the red line of code to the code

Please Login or Register  to view this content.

That seems to be working fine now. Thanks again, Rory!

Noticed it throws an error when you clear a cell with a value

It's not throwing any errors here, but thanks for the advice.

Originally Posted by AliGW

It's not throwing any errors here, but thanks for the advice.

I actually tweaked the version in your amended file when I uploaded it as I noticed the problem!

Okay, it only happens if you enter a value and later decide to remove it.

Ah, Rory - that explains it!

Sorry to be a dimwit here, but can someone just talk me through how to get the code in the right place. I have it working in Rory's sample file, but when I try to apply it to the real file, it does nothing.

It looks as though it is in exactly the right place.

I cannot share the real file.

Right click the relevant worksheet tab, choose view code, then paste it in.

It's OK - I made a moronic mistake - I forgot to change the range from column B to column D. It works perfectly - thank you!

Sorry - one more question. In the real sheet I have four columns for which I need to do the same check.

I have amended the code like this:

Please Login or Register  to view this content.

Am I right to change the Sub names? Is this the correct way to do it? To be clear, I need each of the four monitored ranges to work independently of each other, but they use the same lookup matrix.

Thanks again!

My 2 cents worth:

Please Login or Register  to view this content.

Are you able to give a quick explanation of this line, please?

Please Login or Register  to view this content.

It replaces the range represented by the variable "MonitorRange". As well, Excel doesn't allow more than one Worksheet_Change event so they have to be combined.

I see - thank you. I will give this a try.

Thank you again - this seems fine now.

You are very welcome.