Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

UserForms and Msgbox

0

The following function is called by on the _Exit event of a textbox control:

P
Private Function CheckDate(ctl As control) As Boolean
Dim v As Variant
Dim b As Boolean
v = ctl.Value
b = (v = "") Or (IsDate(v)) ' null entries permitted here
If b Then
   <do something not relevant to the question

Else
    ctl.SetFocus
    MsgBox "You must enter a valid date", vbOKOnly + vbExclamation, "Date filter"
    ctl.SelStart = 0
    ctl.SelLength = Len(v)

End If
CheckDate = b
End Function

The calling event sets Cancel :

.

Cancel = Not CheckDate(Me.txtMytextbox)

However ctl.Setfocus does not set the focus to the ctl and the text in the control is not selected.  All that happens is that the cursor disappears entirely and the textbox still has to be selected manually.  Why doesn't it work? 

And, as it doesn't, how do get the cursor to return to the relevant textbox?   Anybody any idea.

excel
MS Office Version: Office 365 64 bit
posted by: MartinB on Thu Feb 18, 2021 at 11:07 am
Answer
Discuss

Answers

0

Hi Martin and welcome to the Forum

Here's an approach which worked recently. Instead of using the _Exit event, use this code (here shown for the control TextBox1 so change bits in bold to suit):

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

If Not (KeyCode = 13 Or KeyCode = 9) Then Exit Sub 'do nothing if key pressed isn't 13(Enter) or 9(Tab)

If CheckDate(TextBox1) = False Then KeyCode = 0 ' cancel the Enter or Tab

End Sub
Hope this works for you.
posted by: John_Ru (rep: 6142) on Thu Feb 18, 2021 at 12:07 pm
Discuss

Discussion

Troed that, as you suggest and thanks for the suggestion but it does exactly the same thing.  No cursor appears in the TextBox.  Still have to re-select the text box manually to edit it.
MartinB Feb 18, '21 at 12:30 pm
Martin

I don't have your file (it's always a good idea to upload a sample Excel file- you can use the Add Files... button when writing editting the Question) but if I try with two textboxes and type 11 in TextBox1 (then Enter), focus stays in that control after the MsgBox, I can then just overtype the 11 with a date. Admittedly I'm not using the Cancel event or making the cursor appear.

Don't have any more time today to look at this (sorry) but others might.   
John_Ru (rep: 6142) Feb 18, '21 at 12:44 pm
Add to Discussion
0

Your code works perfectly for me on condition that the text box being checked is named "txtMytextbox". In the code below the name is TextBox1.

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Cancel = Not CheckDate(Me.TextBox1)
End Sub

The effect that baffles you must therefore be created either by additional code in the event procedure itself or perhaps there is an event procedure from the next control in the selection or tap order that wreaks havoc.

Just bear in mind that Cancel doesn't terminate code execution in the event procedure. If there is code following the function call you may have to disable it with something like ...

If Not Cancel Then
    ' do your styuff here
End If
posted by: Variatus (rep: 4889) on Thu Feb 18, 2021 at 8:20 pm
Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login