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

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

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

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

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

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

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

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