Userform Textbox SetFocus - No Cursor Displayed

[Ark68]

I have this piece of code that checks the appropriateness of the value entered by the user in a userform textbox. It checks on whether a time value had been entered and happens things when a non time entry is made.

    If IsDate(Me.tb_s1_lwr.Value) Then
        ' enter code here for a properly provided time
    Else
        MsgBox "Please enter time as h:mm using 24 hour clock.", vbExclamation, "INVALID TIME ENTRY"
        'reset textbox to default
        Me.tb_s1_lwr.Value = ""
        tb_s1_lwr.BackColor = RGB(206, 234, 232)
        tb_s1_lwr.SetFocus
        mbevents = True
        Exit Sub
    End If

This woks for the better part except for one slight annoyance. I am trying to get the cursor to show up in the textbox tb_s1_lwr. The SetFocus command doesn't appear to be doing that for me.

 

[Norie]

Which event is the code in?

 

[Ark68]

Oh hi Nori, it's in a textbox exit event ...

Private Sub tb_s1_lwr_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Stop
    Dim bkg_start As Double
    If Not mbevents Then Exit Sub
    mbevents = False
    If IsDate(Me.tb_s1_lwr.Value) Then
        ' code
    Else
        MsgBox "Please enter time as h:mm using 24 hour clock.", vbExclamation, "INVALID TIME ENTRY"
        Me.tb_s1_lwr.Value = ""
        tb_s1_lwr.BackColor = RGB(206, 234, 232)
        tb_s1_lwr.SetFocus
        mbevents = True
        Exit Sub
    End If
    tb_s1_upr.Enabled = True
    tb_s1_upr.BackColor = RGB(206, 234, 232)
    Label5.Enabled = True
    tb_s1_upr.SetFocus
    
    mbevents = True
End Sub

 

[Norie]

Have you tried setting Cancel to True?

 

[Ark68]

No. I don't know where in the code that would go. I'm assuming at the top?

 

[Norie]

No, it would replace this.

tb_s1_lwr.SetFocus

Setting Cancel to true cancels the Exit event and should keep focus on the textbox.

 

[Ark68]

Hi Norie.
Yes, that suggestion seems to have worked!!! Thank you!
I've never really understood what Cancel does, so I suppose a bit more research on my part is needed.

 

[Ark68]

Hi all,
I still having somewhat of a related problem. I'm not sure what code is at fault ...
Here is my revised code that Norie kindly helped me out with.

Private Sub tb_s1_lwr_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    'Stop
    Dim svc_start As Double
    If Not mbevents Then Exit Sub
    mbevents = False
    If IsDate(Me.tb_s1_lwr.Value) Then
        tb_s1_lwr.Value = Format(Me.tb_s1_lwr.Value, "H:MMA/P")
        tb_s1_lwr.BackColor = RGB(255, 255, 255) 'white
        'is time within booking range
        svc_start = TimeValue(Me.tb_s1_lwr.Value)
        slwr_time = bkg_date + svc_start
        If slwr_time <= bkg_dst Or slwr_time >= bkg_det Then
            MsgBox "The service time entered is outside the booking time.", vbExclamation, "INVALID TIME ENTRY"
            Me.tb_s1_lwr.Value = ""
            Me.tb_s1_lwr.BackColor = RGB(206, 234, 232)
            Cancel = True 'Me.tb_s1_lwr.SetFocus
            mbevents = True
            Exit Sub
        End If
    Else
        MsgBox "Please enter time as h:mm using 24 hour clock.", vbExclamation, "INVALID TIME ENTRY"
        Me.tb_s1_lwr.Value = ""
        tb_s1_lwr.BackColor = RGB(206, 234, 232)
        Cancel = True 'Me.tb_s1_lwr.SetFocus
        mbevents = True
        Exit Sub
    End If
    tb_s1_upr.Enabled = True
    tb_s1_upr.BackColor = RGB(206, 234, 232)
    Label5.Enabled = True
    tb_s1_upr.SetFocus
   
    mbevents = True
End Sub

If everything checks out with the user's entry in this textbox, the next textbox (tb_s1_upr) becomes accessible to the user for similar input. The code set up some default formatting, and then sets focus to it (highlighted line in blue). The desired result is to have the blinking cursor in that textbox. This isn't happening. I have a "beforeupdate" event associated with textbox tb_s1_upr in case that matters (this is also causing me problems ... new post to come ... would welcome help with it also!)

 

[mikerickson]

I also have that issue on my Mac. Whenever I send the focus to a TextBox with code, the focus gets there, but the cursor doesn't blink or show at all. Its been that way with a couple generations of Mac Excel. I have assume it has something to do with the implementation on a Mac.

 

[Jaafar Tribak]

You could use a hacky workaround by delaying setting the focus until after the exit event is finished

Something along these lines :

In a Standard Module :

Option Explicit

Public OCtrl As Control

Public Sub DelayedSetFocus()
    OCtrl.SetFocus
End Sub

And then add this Private routine in the UserForm Module:

Private Sub ForceFocus(ByVal Ctrl As Control)
    Set OCtrl = Ctrl
    Application.OnTime Now, "DelayedSetFocus"
End Sub

Now back to the TextBox Exit event, Use ForceFocus to set the focus back to the TextBox (instead of using the focus the normal way via the SetFocus Method)

So, for example this :
tb_s1_lwr.SetFocus
Will become thhis :
ForceFocus tb_s1_lwr