Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
date entry in userform textbox
Hello,VBA experts
how to validate date entries in TextBoxes (on Userform),user wants to enter date values in dd/mm/yy format,entry is must in textbox,after entry and exiting from textbox the textbox entry be displayed in dd-mmm-yy format.(without changing computer's Regional and language settings). -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200806/1 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
date entry in userform textbox
See if this code helps
'StrDate = Textbox1.Text StrDate = "6/9/08" 'get the 1 or 2 digits before 1st slash and convert to number MonthNum = Val(Left(StrDate, InStr(StrDate, "/") - 1)) 'get the string after the 1st slash StrDate = Mid(StrDate, InStr(StrDate, "/") + 1) 'get the month number before the slash and convert to number DayNum = Val(Left(StrDate, InStr(StrDate, "/") - 1)) 'get the year after the slash YearNum = Val(Mid(StrDate, InStr(StrDate, "/") + 1)) 'add 2000 to year number YearNum = YearNum + 2000 'convert date to serial format SerDate = DateSerial(YearNum, MonthNum, DayNum) 'Print the Date in required format Textbox1.Text = Format(SerDate, "dd-mmm-yy") "tkraju via OfficeKB.com" wrote: Hello,VBA experts how to validate date entries in TextBoxes (on Userform),user wants to enter date values in dd/mm/yy format,entry is must in textbox,after entry and exiting from textbox the textbox entry be displayed in dd-mmm-yy format.(without changing computer's Regional and language settings). -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200806/1 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
date entry in userform textbox
It is much easier to wrok with Textboxes when using Dates because they are
text strings and not a serial date which can change with regional settings. Try this code StrDate = Textbox1.text 'get the 1 or 2 digits before 1st slash and convert to number DayNum = Val(StrDate,Instr(StrDate,"/")-1) 'get the string after the 1st slash StrDate = mid(StrDate,Instr(StrDate)+1) 'get the month number before the slash and convert to number MonthNum = Val(StrDate,Instr(StrDate,"/")-1) 'get the year after the slash YearNum = val(mid(StrDate,Instr(StrDate)+1)) 'add 2000 to year number YearNum = YearNum + 2000 'convert date to serial format SerDate=dateserial(YearNum,MonthNum,DayNum) 'Print the Date in required format Textbox1.text = format(SerDate,"dd-mmm-yy") "tkraju via OfficeKB.com" wrote: Hello,VBA experts how to validate date entries in TextBoxes (on Userform),user wants to enter date values in dd/mm/yy format,entry is must in textbox,after entry and exiting from textbox the textbox entry be displayed in dd-mmm-yy format.(without changing computer's Regional and language settings). -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200806/1 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
date entry in userform textbox
I answered this in your previous thread.
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "tkraju via OfficeKB.com" <u16627@uwe wrote in message news:855b15be473b9@uwe... Hello,VBA experts how to validate date entries in TextBoxes (on Userform),user wants to enter date values in dd/mm/yy format,entry is must in textbox,after entry and exiting from textbox the textbox entry be displayed in dd-mmm-yy format.(without changing computer's Regional and language settings). -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200806/1 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
date entry in userform textbox
Mr.Bob,I didn't received any reply for this dates problem thread.
Bob Phillips wrote: I answered this in your previous thread. Hello,VBA experts how to validate date entries in TextBoxes (on Userform),user wants to [quoted text clipped - 4 lines] Regional and language settings). -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200806/1 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
date entry in userform textbox
Yes,just gone through that.Please ignore my earlier reply.thanks a lot..
Bob Phillips wrote: I answered this in your previous thread. Hello,VBA experts how to validate date entries in TextBoxes (on Userform),user wants to [quoted text clipped - 4 lines] Regional and language settings). -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200806/1 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
date entry in userform textbox
Mr.Bob,i just tested your code,it hasn't given me the desired results.I have
entered a date value in Textbox1, 04/06/08(4thJune2008) ,it converted to 06- Apr-08.You misunderstood my question,in India users used to enter date values in dd/mm/yy format. tkraju wrote: Yes,just gone through that.Please ignore my earlier reply.thanks a lot.. I answered this in your previous thread. [quoted text clipped - 3 lines] Regional and language settings). -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200806/1 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
date entry in userform textbox
Thank you Joel,code a bit too long.using Cdate can you make it?
Joel wrote: It is much easier to wrok with Textboxes when using Dates because they are text strings and not a serial date which can change with regional settings. Try this code StrDate = Textbox1.text 'get the 1 or 2 digits before 1st slash and convert to number DayNum = Val(StrDate,Instr(StrDate,"/")-1) 'get the string after the 1st slash StrDate = mid(StrDate,Instr(StrDate)+1) 'get the month number before the slash and convert to number MonthNum = Val(StrDate,Instr(StrDate,"/")-1) 'get the year after the slash YearNum = val(mid(StrDate,Instr(StrDate)+1)) 'add 2000 to year number YearNum = YearNum + 2000 'convert date to serial format SerDate=dateserial(YearNum,MonthNum,DayNum) 'Print the Date in required format Textbox1.text = format(SerDate,"dd-mmm-yy") Hello,VBA experts how to validate date entries in TextBoxes (on Userform),user wants to enter date values in dd/mm/yy format,entry is must in textbox,after entry and exiting from textbox the textbox entry be displayed in dd-mmm-yy format.(without changing computer's Regional and language settings). -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200806/1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date Format for Userform TextBox | New Users to Excel | |||
Date Format in UserForm textbox. | Excel Programming | |||
Copying TextBox entry from one UserForm to another | Excel Programming | |||
Ensuring UserForm Textbox Entry is numeric | Excel Programming | |||
Date Calculation in UserForm TextBox | Excel Programming |