Custom data validation

[leopardhawk]

Is it possible to have the data validation in a cell NOT permit any entry at all unless there is a valid date in another cell? I tried this...

=if(H9,0,0)

...but it still allows data to be entered in the cell I am trying to validate.

Cheers!

 

[lrobbo314]

Re: Need help with custom data validation

You can use this UDF.

Function DATEVER(s As String) As Boolean
DATEVER = IsDate(s)
End Function

Then in 'Name Manager' add a named range called ISDATE, which refers to =DATEVER(Sheet1!H9).

Then, in the cell that you want to have the data validation, select 'Custom', and use the formula '=ISDATE'.

This is working on my test data.

 

[leopardhawk]

Re: Need help with custom data validation

Thanks for the post. I did everything exactly as you suggested and now, with or without a valid date in H9, I am getting an error that says "the value doesn't match the data validation restrictions defined for this cell."

 

[lrobbo314]

Re: Need help with custom data validation

I forgot to mention that you need to make the 'Ignore Blank' is not checked in the 'Data Validation' window.

 

[leopardhawk]

Re: Need help with custom data validation

I unchecked the 'ignore blank' and I am still getting the same error, with or without a date in H9.

I assume the code you sent in your first post was meant to be copied into VB, correct? Does it matter if it's in a window by itself or should it be with some other code?

 

[lrobbo314]

Re: Need help with custom data validation

Try making sure that in the data validation that you don't have '$' in the cell reference. So, =DATEVER(Sheet1!H9), instead of =DATEVER(Sheet1!$H$9).

As long as you pasted the code for the UDF into a new module in the VB editor, that part should be good to go.

 

[leopardhawk]

Re: Need help with custom data validation

Yes, there are no $$$ in the data validation window. The only difference in my formula is that I referenced the name of the worksheet, =DATEVER(personal_info!H9).

Is this the problem?

The code is definitely in a new module in the VB editor.

 

[lrobbo314]

Re: Need help with custom data validation

I referenced this post to get it to work. Maybe following the steps from the post will work better.

 

[leopardhawk]

Re: Need help with custom data validation

I figured it out but thanks for your help in getting me set on the right course. Two things I think were causing the error. I copied the single quotes around =ISDATE into the data validation window and secondly, in Name Manager, I changed the 'scope' to the worksheet in question. After deleting that and creating a new one with the scope set to 'workbook', everything is working fine. Thank you so much for your efforts.

Cheers!

 

[lrobbo314]

Re: Need help with custom data validation

Sweet! Glad you got it squared away.