Data Validation - To Stop the User Inputting more than one Specified Value in a column

[JohnGow383]

Hi. I have a question about data validation. Is there a way to remove one of the strings from data validation list if it's already been used in a row above in the specified range?
A quick example of what I mean. Lets's say in column A1:A30 each cell has a data validation list of 'A', 'B', 'C', 'D' and 'E'. Each of these options are available to be selected in any cell in range A1:A30.
I want A, B and C to be allowed to be selected multiple times like normal, however, with options D and E, I want these to be exclusive. So if A, B or C are in A1 to A10 and repeat multiple times that's fine. However, if for example D is selected in A11, the in A12 to A30 D is no longer available. The same would then apply to E. Basically, A to C to be inclusive and be selected multiple times (normal data validation list), but D or E are one times only.
VBA solution is welcome if it can't be done within Excel. If this can't be done I'd like to know too.
Thanks

 

[JohnGow383]

I

I don't think it would either. After glancing at the code you posted I think you would have to provide a lot more detail about what you want a merged change event macro to do with some representative data posted with XL2BB.

Thanks for the reply. I was having issues with XL2BB as the file was too big. I'll keep trying. Bear in mind I'm on a ship and our internet is very very slow.
A lot of the code posted you can ignore. The range M4:M53 is being used already to call other macros. The list of data validation in each cell in the range is the same. The list is as follows :
NOON at SEA
NOON in PORT
NOON in TRANS
EOP
SOP
ROP
SOP2
ROP2

They are all triggers to call speech macros.
NOON in PORT, NOON in TRANS, ROP and ROP2 are used to clear contents from 3 cells.
SOP, ROP, SOP2 & ROP2 are used to call a macro that adds a comment in a cell when detected as lowest non empty cell in M4:M53 range.

I do not want a 2nd SOP, ROP, SOP2 or ROP2 being allowed to be selected by accident. If it is its not actually having any adverse affect immediately, it's only when the user continues to enter the counters it would so I may try this with the undo removed and see if I can get the message box appearing. I noticed control Z isn't always working on that sheet so there may not be anything wrong with the code placement. I was adding it to the start since I'd want it to run before trying to call the next macros. Will have another go at uploading the file.

 

[JoeMo]

If you are triggering macros that make changes you want to reverse, generally ctrl+z will not be able to do that.

 

[JohnGow383]

No I'm not worried about reversing the changes made by the macros, but if I select SOP for example and then press control z it doesn't undo.
There is an easier but less elegant way of detecting last value in column M range. By using excel formula in a hidden cell to return the value and using that cell as the change or trigger cell for the other macros. This would allow your code to work most likely.

If you are triggering macros that make changes you want to reverse, generally ctrl+z will not be able to do that.

 

[JohnGow383]

There is nothing wrong with the code or the integration of the placement of the code. I have just removed the line application.undo and I get the message box popping up.
I think what is happening is once the 2nd SOP/ROP/SOP2 or ROP2 is entered because it's then going on to trigger more macros, it's unable to undo. I honestlly don't think this can be fixed.
A possible workaround would be to rather than have undo, but have delete or clear the contents of the lowest non empty cell in the range.
I'm just not entiely sure of the code required or correct syntax, but something like this?

  Dim lastRow As Long  Dim lastColumn As Long 
lastRow = Range("M4:M53").End(xlDown).Row 
lastColumn = Range("M4:53").End(xlToRight).Column 
Cells(lastRow, lastColumn).Clear.Contents

If you are triggering macros that make changes you want to reverse, generally ctrl+z will not be able to do that.

 

[JohnGow383]

I think I've fixed it. Instead of Application.undo it calls this very simple macro

Sub DelWrongEvent()

 Range("M4:M53").End(xlDown).ClearContents
End Sub

Thanks so much for your help, very grateful. Everything appears to be working perfectly. Also, apologies for not being entiely clear. I'm very new to VBA and just learning.

 

[JoeMo]

You are welcome.

 

[JohnGow383]

You are welcome.

Hi. If you don't mind me asking, can you just explain your bit of the code for cell change value part? For example, using my range you have

If Not Intersect(Target, Range("M4:M53")) Is Nothing Then

With the other worksheet change codes I am using

If Not Application.Intersect(Range("M4:M53"), Target) Is Nothing Then

What is the difference between these lines of code?
Ty

 

[JoeMo]

Hi. If you don't mind me asking, can you just explain your bit of the code for cell change value part? For example, using my range you have

If Not Intersect(Target, Range("M4:M53")) Is Nothing Then

With the other worksheet change codes I am using

If Not Application.Intersect(Range("M4:M53"), Target) Is Nothing Then

What is the difference between these lines of code?
Ty

There is no fundamental difference between the two lines. Both are using the Application.Intersect Method to determine if the target range has any intersection with Range("M4:M53").
Intersect is one of more than a few properties and methods that can be used without the Application object qualifier. These include Application.Activecell and Application.Union as examples.
I am not proficient at typing so tend to use all of these w/o the Application object qualifier to minimize keystrokes required.

 

[JohnGow383]

Ah right, thanks. It is certainly more elegant

There is no fundamental difference between the two lines. Both are using the Application.Intersect Method to determine if the target range has any intersection with Range("M4:M53").
Intersect is one of more than a few properties and methods that can be used without the Application object qualifier. These include Application.Activecell and Application.Union as examples.
I am not proficient at typing so tend to use all of these w/o the Application object qualifier to minimize keystrokes required.