Hello everyone,
I'm stuck on figuring out a way to pop a MsgBox alert if a cell input is not in the proper format. In my example, I'm creating a Worksheet_Change event for a range of cells where if a user enters an input into the cell that is not in the proper Date format ("mm/dd/yy"), then it will pop up the MsgBox alert. Any help would be most appreciated. Thanks a bunch!
Best regards,
munkayboi
-Right click on the cell you are wanting in a certain format
-Go to Format cell
-Click Date
-Set it to be the proper format
And then so people can still enter information into it
-in Format Cell go to Protection
-Uncheck the box that says "Locked"
-Then in the Tools menu go to Protection
-and Protect Sheet
If the cell is formated to be unlocked you can still enter information however, if the sheet is protected you will be unable to change the formating of the cell.
So If I enter a date different than your format it will change it to the proper format
Thanks a lot for your feedback, but that's not specifically what I'm looking for. Need a MsgBox pop-up and it would be ideal to be able to code it in VBA. Also, it needs to filter out alpha inputs (such as typing invalid inputs like "aoihoaig" or plain numbers like "57182"). I can probably figure that one out on my own though. Just need some kind of VBA statement that checks if the cell input is in a valid Date format. I think once I get that part, then I can take it from there.
I'm thinking maybe an If statement will work, but I'm not sure how I would write it to say something like "If Not Target.Value = Format(date, etc.) Then..." Anyone else have any ideas?
Last edited by munkayboi; 08-02-2007 at 04:06 PM.
Ah ha! Got it. The part that I was looking for was IsDate. So If Not IsDate(Target.Value) etc. etc. Simpler than I thought.
Isdate checks for a valid date not a date format.
You don't need VBA to do this use Data Validation to restrict inputs to date, add a custom message. Then fix the format as suggested earlier.
Hope that helps.
RoyUK
--------
For Excel Tips & Solutions, free examples and tutorials why not check out my web site
Free DataBaseForm example
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks