Data Validation using Worksheet_Change

[liampog]

Hi

Is there any way to validate data by using the Worksheet_Change option...

I'd like for some cells to only be allowed data to be entered in this exact format:

hh:mm - hh:mm

Could someone give me some code to work with that might do this for me?

Basically, if the user DOESN'T enter it in this format, I'd like a warning to popup and then they have to try again.

I would use the standard Data Validation but I'm already using that to create a dropdown list.

Thanks in advance,
Liam

 

[liampog]

Anybody help with this one?

 

[Peter_SSs]

You haven't said what range of cells you want validated. This is for B2:B20. Please test in a copy of your workbook.

<font face=Courier New><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#00007F">Dim</SPAN> rChanged <SPAN style="color:#00007F">As</SPAN> Range, c <SPAN style="color:#00007F">As</SPAN> Range, rErr <SPAN style="color:#00007F">As</SPAN> Range<br><br>    <SPAN style="color:#00007F">Const</SPAN> sDV <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "B2:B20"<br>    <br>    <SPAN style="color:#00007F">Set</SPAN> rChanged = Intersect(Range(sDV), Target)<br><br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> rChanged <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> rErr = Cells(Rows.Count, Columns.Count)<br>        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> rChanged<br>            <SPAN style="color:#00007F">If</SPAN> Len(c.Value) > 0 And _<br>                    <SPAN style="color:#00007F">Not</SPAN> c.Value <SPAN style="color:#00007F">Like</SPAN> "##:## - ##:##" <SPAN style="color:#00007F">Then</SPAN><br>                <SPAN style="color:#00007F">Set</SPAN> rErr = Union(rErr, c)<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> c<br>        <SPAN style="color:#00007F">Set</SPAN> rErr = Intersect(rErr, rChanged)<br>        <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> rErr <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>            Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN><br>            rErr.ClearContents<br>            Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN><br>            MsgBox "Invalid entries cleared from " & rErr.Address(0, 0)<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>