Custom data validation

leopardhawk

Well-known Member
Joined
May 31, 2007
Messages
611
Office Version
  1. 2016
Platform
  1. Windows
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...

Code:
=if(H9,0,0)

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

Cheers!
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Re: Need help with custom data validation

You can use this UDF.


Code:
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.
 
Upvote 0
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."
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0
Re: Need help with custom data validation

Sweet! Glad you got it squared away.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top