Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prevent user moving to next control
hello, I have a box used exclusivley for date entry. If someone does not enter it in this format "DD/MM/YYYY" then a msgbox appears saying "Date must be entered in this format...". When this incorrect entry occurs I would like to force the user to stay in the date box until he has entered correctly. I have tried date1.setfocus, date1.tabstop and everything else I can think of but nothing is working. This is my code at the minute Public Sub Date1_AfterUpdate() If Date1.Value = Format(Date1.Value, "DD/MM/YYYY") Then Else MsgBox "Date must be entered in this format: DD/MM/YYYY" End If End Sub Please help!!! Thank You -- johncassell ------------------------------------------------------------------------ johncassell's Profile: http://www.excelforum.com/member.php...o&userid=25016 View this thread: http://www.excelforum.com/showthread...hreadid=391628 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prevent user moving to next control
Hi John,
Rather than endeavouring to constrain the user to a designated date format and then throwing up message boxes asking the user to re-enter the data, two suggestions: (1) Accept the user's data in no matter what data format, and have your code format it as you wish (2) If the textbox's only function is to accept a date entry, why not replace it with a Calendar control, which is visually more appealing and has the added advantage of removing the need to verify that the entry represents a valid date. --- Regards, Norman "johncassell" wrote in message ... hello, I have a box used exclusivley for date entry. If someone does not enter it in this format "DD/MM/YYYY" then a msgbox appears saying "Date must be entered in this format...". When this incorrect entry occurs I would like to force the user to stay in the date box until he has entered correctly. I have tried date1.setfocus, date1.tabstop and everything else I can think of but nothing is working. This is my code at the minute Public Sub Date1_AfterUpdate() If Date1.Value = Format(Date1.Value, "DD/MM/YYYY") Then Else MsgBox "Date must be entered in this format: DD/MM/YYYY" End If End Sub Please help!!! Thank You -- johncassell ------------------------------------------------------------------------ johncassell's Profile: http://www.excelforum.com/member.php...o&userid=25016 View this thread: http://www.excelforum.com/showthread...hreadid=391628 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prevent user moving to next control
To follow up on Norman's suggestion 2, see this link
http://www.rondebruin.nl/calendar.htm Mike F "Norman Jones" wrote in message ... Hi John, Rather than endeavouring to constrain the user to a designated date format and then throwing up message boxes asking the user to re-enter the data, two suggestions: (1) Accept the user's data in no matter what data format, and have your code format it as you wish (2) If the textbox's only function is to accept a date entry, why not replace it with a Calendar control, which is visually more appealing and has the added advantage of removing the need to verify that the entry represents a valid date. --- Regards, Norman "johncassell" wrote in message ... hello, I have a box used exclusivley for date entry. If someone does not enter it in this format "DD/MM/YYYY" then a msgbox appears saying "Date must be entered in this format...". When this incorrect entry occurs I would like to force the user to stay in the date box until he has entered correctly. I have tried date1.setfocus, date1.tabstop and everything else I can think of but nothing is working. This is my code at the minute Public Sub Date1_AfterUpdate() If Date1.Value = Format(Date1.Value, "DD/MM/YYYY") Then Else MsgBox "Date must be entered in this format: DD/MM/YYYY" End If End Sub Please help!!! Thank You -- johncassell ------------------------------------------------------------------------ johncassell's Profile: http://www.excelforum.com/member.php...o&userid=25016 View this thread: http://www.excelforum.com/showthread...hreadid=391628 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prevent user moving to next control
Hi John,
(1) Accept the user's data in no matter what data format, and have your code Should read: (1) Accept the user's data in no matter what date format, and have your code (data === date) --- Regards, Norman "Norman Jones" wrote in message ... Hi John, Rather than endeavouring to constrain the user to a designated date format and then throwing up message boxes asking the user to re-enter the data, two suggestions: (1) Accept the user's data in no matter what data format, and have your code format it as you wish (2) If the textbox's only function is to accept a date entry, why not replace it with a Calendar control, which is visually more appealing and has the added advantage of removing the need to verify that the entry represents a valid date. --- Regards, Norman "johncassell" wrote in message ... hello, I have a box used exclusivley for date entry. If someone does not enter it in this format "DD/MM/YYYY" then a msgbox appears saying "Date must be entered in this format...". When this incorrect entry occurs I would like to force the user to stay in the date box until he has entered correctly. I have tried date1.setfocus, date1.tabstop and everything else I can think of but nothing is working. This is my code at the minute Public Sub Date1_AfterUpdate() If Date1.Value = Format(Date1.Value, "DD/MM/YYYY") Then Else MsgBox "Date must be entered in this format: DD/MM/YYYY" End If End Sub Please help!!! Thank You -- johncassell ------------------------------------------------------------------------ johncassell's Profile: http://www.excelforum.com/member.php...o&userid=25016 View this thread: http://www.excelforum.com/showthread...hreadid=391628 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prevent user moving to next control
Thanks for the replies but the application i use sometimes uses dates going very far back and the calender form is long-winded for this purpose. We have this program at work where it treats the date box as 3 seperate number sections so if the date was "25/02/2005", 25 would be highlighted and you could use the up and down keys to move to 26 or 24 then press the right key and it would move to "02" etc... Is there a way I could replicate this as it is exactly what I need. At the moment I am using this code for the date1.change function but would love it if i could get the code above working. Public Sub Date1_Change() Select Case Len(Date1.Text) Case 2, 5 Date1.Text = Date1.Text & "/" End Select End Sub but this forces the user to re-type the whole date. thanks again for your original replies!! -- johncassell ------------------------------------------------------------------------ johncassell's Profile: http://www.excelforum.com/member.php...o&userid=25016 View this thread: http://www.excelforum.com/showthread...hreadid=391628 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prevent user moving to next control
Hi John,
Thanks for the replies but the application i use sometimes uses dates going very far back and the calender form is long-winded for this purpose In that case, use my first suggestion and format the user's entry as you want / need it. We have this program at work where it treats the date box as 3 seperate number sections so if the date was "25/02/2005", 25 would be highlighted and you could use the up and down keys to move to 26 or 24 then press the right key and it would move to "02" etc... Replace your textbox with three comboboxes. Use the Userform initialize event to fill the textboxes with the required values: 1-31 for the day box; 1 to 12 for the month box; in the year box include only the years you require. Add suitable identification labels and construct the required date, in your code, from the three separate input values. --- Regards, Norman "johncassell" wrote in message ... Thanks for the replies but the application i use sometimes uses dates going very far back and the calender form is long-winded for this purpose. We have this program at work where it treats the date box as 3 seperate number sections so if the date was "25/02/2005", 25 would be highlighted and you could use the up and down keys to move to 26 or 24 then press the right key and it would move to "02" etc... Is there a way I could replicate this as it is exactly what I need. At the moment I am using this code for the date1.change function but would love it if i could get the code above working. Public Sub Date1_Change() Select Case Len(Date1.Text) Case 2, 5 Date1.Text = Date1.Text & "/" End Select End Sub but this forces the user to re-type the whole date. thanks again for your original replies!! -- johncassell ------------------------------------------------------------------------ johncassell's Profile: http://www.excelforum.com/member.php...o&userid=25016 View this thread: http://www.excelforum.com/showthread...hreadid=391628 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prevent user moving to next control
As an added thought:
For years I sometimes use entries for the year box like the following. Either use the add method to fill the list, or put the list on a worksheet and reference it. Year(Now)-1 Year(Now) Year(Now)+1 -- steveB Remove "AYN" from email to respond "Norman Jones" wrote in message ... Hi John, Thanks for the replies but the application i use sometimes uses dates going very far back and the calender form is long-winded for this purpose In that case, use my first suggestion and format the user's entry as you want / need it. We have this program at work where it treats the date box as 3 seperate number sections so if the date was "25/02/2005", 25 would be highlighted and you could use the up and down keys to move to 26 or 24 then press the right key and it would move to "02" etc... Replace your textbox with three comboboxes. Use the Userform initialize event to fill the textboxes with the required values: 1-31 for the day box; 1 to 12 for the month box; in the year box include only the years you require. Add suitable identification labels and construct the required date, in your code, from the three separate input values. --- Regards, Norman "johncassell" wrote in message ... Thanks for the replies but the application i use sometimes uses dates going very far back and the calender form is long-winded for this purpose. We have this program at work where it treats the date box as 3 seperate number sections so if the date was "25/02/2005", 25 would be highlighted and you could use the up and down keys to move to 26 or 24 then press the right key and it would move to "02" etc... Is there a way I could replicate this as it is exactly what I need. At the moment I am using this code for the date1.change function but would love it if i could get the code above working. Public Sub Date1_Change() Select Case Len(Date1.Text) Case 2, 5 Date1.Text = Date1.Text & "/" End Select End Sub but this forces the user to re-type the whole date. thanks again for your original replies!! -- johncassell ------------------------------------------------------------------------ johncassell's Profile: http://www.excelforum.com/member.php...o&userid=25016 View this thread: http://www.excelforum.com/showthread...hreadid=391628 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prevent user moving to next control
Cheers everyone, i think i'll be able to fathom something out using all your ideas. thanks again john -- johncassell ------------------------------------------------------------------------ johncassell's Profile: http://www.excelforum.com/member.php...o&userid=25016 View this thread: http://www.excelforum.com/showthread...hreadid=391628 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to prevent data from moving to a different cell | Excel Worksheet Functions | |||
how do i prevent a row from moving, like subheader that is visibl | New Users to Excel | |||
Can I prevent a sheet from moving within a workbook? | Excel Programming | |||
Can I prevent a sheet from moving within a workbook? | Excel Programming | |||
Prevent control & break | Excel Programming |