Data validation automatically set to first item in list

[dirtychinchilla]

Morning sirs,

I've been having a good look around for this, but am struggling to find a solution that works.

I have various data validation lists, and would like them automatically to be set to their first value when they are set up (uses a macro). Does anyone have any idea how this might be done?

Thanks in advance,

dirtychinchilla

 

[dirtychinchilla]

Cool, that's more like what I was thinking.

Unfortunately, I now get a 1004 error on "If .Validation.Type = xlValidateList Then"

 

[Andrew Poulsom]

Do all the cells in Range("DataValidationClear") have Data Validation applied?

 

[dirtychinchilla]

Hah no they don't!!! I changed the range so it didn't include the blank cells, and it works perfectly. Thank you is there a way to have it skip cells without data validation in them?

 

[Andrew Poulsom]

You can pass Cell to the HasValidation function here:

Spreadsheet Page Excel Tips: Ensuring That Data Validation Is Not Deleted

and do nothing if it returns False.

 

[dirtychinchilla]

Great, thanks so much for your help Andrew. The program is in much better shape now! Have a good afternoon.

 

[korhan]

Andrew,

I am trying to understand what this part of code do
.Value = Range(Replace(.Validation.Formula1, "=", "")).Cells(1, 1)

Can you explain a little bit if you don't mind? I just couldn't figure out but this is exactly the piece of code that I was looking for.