VBA to remove data validation input message

[Faridwahidi]

Hi,

I have placed wrong input message in data validation. To remove it manual definitely will take a long time and I have to re-enter the data.

The input message as follow. I want to remove the one in red colour message.

Assessment scores
must be in absolute
number between 1 to 5

N/A defaulted to 1
(Consider the lowest)


can anyone help to create VBA code to remove it automatically. The cells range is H17 to QM119. Range(H17:QM119)

[abousetta]

Put this in a standard module and run:

Please Login or Register  to view this content.

abousetta

[Faridwahidi]

Hi abousetta,

Does it will affect another field in data validation and error alert message?


In validation criteria source, I have placed several a code depending on Range

e.g
Range("H17:QM43") , Source = SCORE (use namebox function)
Range("H108:QM119"), Source = SCORE (use namebox function)

Range("H48:QM103") , Source = IF(AND(H67:O86="",H90:H103="",H51=""),INDIRECT($D$128))

Please Login or Register  to view this content.

[abousetta]

Easiest way is for you to start the macro recorder. Click on the cell with the data validation >> Data >> Data Validation >> Cancel. Repeat this for all the different data unique validations your have and then copy/ paste the resulting code here and I will adjust it for you. Either that or upload a copy of your workbook and I can do that for you.

abousetta

[Faridwahidi]

Hi abousetta,

I found the way you do is much better by using

Please Login or Register  to view this content.

I dont need to use dropdown list (1,2,3,4,5) for that purpose.


Attached here is my workbook.

The following range shared the same validation criteria source and error alert message

1) Range("H17:QM43") and Range("H108:QM119")

2) Range("H48:QM49") and Range("H57:QM61")
3) Range("H51")
4) Range("H53:QM56")

5) Range("H67:QM68") and Range("H76:QM86")
6) Range("H70")
7) Range("H72:QM75")

8) Range("H90:QM93") and Range("H101:QM103")
9) Range("H96")
10) Range("H97:QM100")

[abousetta]

Hi,

Here is an updated macro:

Please Login or Register  to view this content.

I didn't know where you wanted to use the data validation method I proposed, but you can just paste that line instead of the lines in the above macro where you want it.

abousetta

[Faridwahidi]

Hi abousetta,

Thanks a lot.