Thanks! Keep in mind that: Excel doesn't have "dates". What it DOES is allows us to DISPLAY a number (the number of days since 1/1/1900) in multiple ways. SOME of which represents a date!
That being said, your userform doesn't have a Date. It has a Text box. the contents of this text box is a TEXT STRING. We (or, you) want this TEXT STRING to REPRESENT a date. Each part of a userform can have "events" associated with it. (right-click on the text box and select "View Code".. then, use the upper-right pull down to see the list of events available to for the object) In this case, I would recommend creating an "exit" event for the text box. (a "change" event is awkward because every keystroke represents a "change" and therefore triggers the event macro) In the event, you can test to make sure the value of the textbox "represents" (or can be interpreted as) a date. If it does not, then you can issue a warning. If it DOES, then you can check to see if the string is the same as the same string converted to the desired "date" format. Then modify as appropriate. Like: '---------------------------------------------------------------------------------------------------------------------- Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean) If (Not IsDate(UserForm1.TextBox1.Value)) Then MsgBox "Value must be a date" Else If (UserForm1.TextBox1.Value <> Format(UserForm1.TextBox1.Value, "dd-mmm-yyyy")) Then UserForm1.TextBox1.Value = Format(UserForm1.TextBox1.Value, "dd-mmm-yyyy") End If End If End Sub '---------------------------------------------------------------------------------------------------------------------- Also, if you want the "default" value of the text box to be the current date, you can make it part of the "initialize" event for the userform: Private Sub UserForm_Initialize() UserForm1.TextBox1.Value = Format(Now(), "dd-mmm-yyyy") End Sub let me know if this is what you're looking for. Paul ----------------------------------------- “Do all the good you can, By all the means you can, In all the ways you can, In all the places you can, At all the times you can, To all the people you can, As long as ever you can.” - John Wesley ----------------------------------------- ________________________________ From: Hilary Lomotey <resp...@gmail.com> To: excel-macros@googlegroups.com Sent: Thu, December 13, 2012 9:47:37 AM Subject: Re: $$Excel-Macros$$ FORMAT DATES IN USERFORM * sorry my bad PFA On Thu, Dec 13, 2012 at 2:27 PM, Paul Schreiner <schreiner_p...@att.net> wrote: This message is eligible for Automatic Cleanup! (schreiner_p...@att.net) Add cleanup rule | More info > > >Hilary, > >Your file is in .xlsx format. >As such, it does not contain your userform or macros. > >Please attach a .xlsb (or .xlsm) file so that we can assist you. > >Paul >----------------------------------------- >“Do all the good you can, >By all the means you can, >In all the ways you can, >In all the places you can, >At all the times you can, >To all the people you can, >As long as ever you can.” - John Wesley >----------------------------------------- > > > > > ________________________________ From: Hilary Lomotey <resp...@gmail.com> >To: excel-macros <EXCEL-MACROS@googlegroups.com> >Sent: Thu, December 13, 2012 8:55:04 AM >Subject: $$Excel-Macros$$ FORMAT DATES IN USERFORM > >Hello > > > > > > >In the attached file, i need assistance to be able to enter dates in the >format >dd-mmm-yy, and also to alert the user to enter dates in that format, i want >the >textbox1 to always pick the current date but date should be editable. thanks-- > >Join official Facebook page of this forum @ >https://www.facebook.com/discussexcel > >FORUM RULES > >1) Use concise, accurate thread titles. Poor thread titles, like Please Help, >Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get >quick attention or may not be answered. >2) Don't post a question in the thread of another member. >3) Don't post questions regarding breaking or bypassing any security measure. >4) Acknowledge the responses you receive, good or bad. >5) Jobs posting is not allowed. >6) Sharing copyrighted material and their links is not allowed. > >NOTE : Don't ever post confidential data in a workbook. Forum owners and >members >are not responsible for any loss. >--- >You received this message because you are subscribed to the Google Groups "MS >EXCEL AND VBA MACROS" group. >To post to this group, send email to excel-macros@googlegroups.com. >To unsubscribe from this group, send email to >excel-macros+unsubscr...@googlegroups.com. >Visit this group at http://groups.google.com/group/excel-macros?hl=en. > > >-- >Join official Facebook page of this forum @ >https://www.facebook.com/discussexcel > >FORUM RULES > >1) Use concise, accurate thread titles. Poor thread titles, like Please Help, >Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get >quick attention or may not be answered. >2) Don't post a question in the thread of another member. >3) Don't post questions regarding breaking or bypassing any security measure. >4) Acknowledge the responses you receive, good or bad. >5) Jobs posting is not allowed. >6) Sharing copyrighted material and their links is not allowed. > >NOTE : Don't ever post confidential data in a workbook. Forum owners and >members >are not responsible for any loss. >--- >You received this message because you are subscribed to the Google Groups "MS >EXCEL AND VBA MACROS" group. >To post to this group, send email to excel-macros@googlegroups.com. >To unsubscribe from this group, send email to >excel-macros+unsubscr...@googlegroups.com. >Visit this group at http://groups.google.com/group/excel-macros?hl=en. > > > > -- Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en. -- Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en.
