Prevent user moving to next control

[johncassell]

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

[Norman Jones]

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" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>

[Mike Fogleman]

To follow up on Norman's suggestion 2, see this link

http://www.rondebruin.nl/calendar.htm

Mike F
"Norman Jones" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]>
> wrote in message
> news:[email protected]...
>>
>> 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
>>
>
>

[Norman Jones]

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]>
> wrote in message
> news:[email protected]...
>>
>> 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
>>
>
>

[johncassell]

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!!

[Norman Jones]

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" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>

[STEVE BELL]

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]>
> wrote in message
> news:[email protected]...
>>
>> 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
>>
>
>

[johncassell]

Cheers everyone, i think i'll be able to fathom something out using all your ideas.

thanks again

john