Unable to set focus on Textbox

[ipbr21054]

Morning,
The code in use is supplied below.

I enter some text in TextBox8 to be searched for but none was found.
I then see the Msgbox saying NO CUSTOMER WAS FOUND USING THAT INFORMATION
I then click OK to the Msgbox BUT now i wish for TextBox8 to have the text removed of which it does BUT in respect of TextBox8 having the focus applied to it i dont see the flashing vetical line at all.

    Private Sub TextBox8_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If KeyCode = vbKeyReturn Then
      Dim r As Range, f As Range, cell As String, added As Boolean
      Dim sh As Worksheet
      
      Set sh = Sheets("POSTAGE")
      sh.Select
      With ListBox1
        .Clear
        .ColumnCount = 4
        .ColumnWidths = "220;110;130;10"
        If TextBox8.Value = "" Then Exit Sub
        Set r = Range("B8", Range("B" & Rows.Count).End(xlUp))
        Set f = r.Find(TextBox8.Value, LookIn:=xlValues, lookat:=xlPart)
        If Not f Is Nothing Then
          cell = f.Address
          Do
            added = False
            For i = 0 To .ListCount - 1
              Select Case StrComp(.List(i), f.Value, vbTextCompare)
                Case 0, 1
                  .AddItem f.Value, i                 'col B
                  .List(i, 1) = f.Offset(, 2).Value   'col D
                  .List(i, 2) = f.Offset(, 5).Text    'col G .TEXT is used as date in Listbox was shown as 12/17/2020 as opposed 17/12/2020
                  .List(i, 3) = f.Row                 'row
                  added = True
                  Exit For
              End Select
            Next
            If added = False Then
              .AddItem f.Value                                 'col B
              .List(.ListCount - 1, 1) = f.Offset(, 2).Value   'col D
              .List(.ListCount - 1, 2) = f.Offset(, 5).Text    'col G .TEXT is used as date in Listbox was shown as 12/17/2020 as opposed 17/12/2020
              .List(.ListCount - 1, 3) = f.Row                 'row
            End If
            Set f = r.FindNext(f)
          Loop While Not f Is Nothing And f.Address <> cell
          TextBox8 = UCase(TextBox8)
          .TopIndex = 0
        Else
          MsgBox "NO CUSTOMER WAS FOUND USING THAT INFORMATION", vbCritical, "POSTAGE SHEET CUSTOMER NAME SEARCH"
        End If
      End With
        TextBox8.Value = ""
        TextBox8.SetFocus
      End If
    End Sub

 

[Zot]

This morning ive removed the setFocus with TextBox8.Value = 123 and it does as shown.
SetFocus not working so i will just put up with it

Thanks for the help

Sorry. Forget about the problem here. I go through your code again line by line ?

The reason behind not being able to SetFocus is because the control has return to worksheet after failed search. When you show your UserForm, the control is passed to UserForm from Worksheet. The line sh.Select in code

Set sh = Sheets("POSTAGE")
sh.Select
With ListBox1

will pass control to Sheets("POSTAGE"), thus causing the control default back to Sheet once execution end. Normally, you will try to avoid something like this. Since you defined sh as Sheets("POSTAGE") already, you do not need to select the sheet. Instead of

Set r = Range("B8", Range("B" & Rows.Count).End(xlUp))

you can just use

Set r = sh.Range("B8", sh.Range("B" & Rows.Count).End(xlUp))

If you want to do copy also you can just use

sh.Range("B8").Copy

instead of selecting sh and use

Range("B8").Copy

I guess you understood what I meant. This way to will not pass control to worksheet until you unload UserForm. Unloading will pass control to worksheet automatically.

In your case even after I tried removing sh.Select I was still unable to keep the control. My guess is that the

Set f = r.Find(TextBox8.Value, LookIn:=xlValues, Lookat:=xlPart)

is passing the control over. I posted your code again with very minute modification.

Anyway, I found the trick to pass the control back to Userform using SendKey.
Note that I'm replacing your code a bit by not deleting the value in TextBox8 so that you can see the last parameter you put. The parameter would be replace once you key in new parameter.

    Private Sub TextBox8_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If KeyCode = vbKeyReturn Then
      Dim r As Range, f As Range, cell As String, added As Boolean
      Dim sh As Worksheet
    
      Set sh = Sheets("POSTAGE")
      sh.Select
      With ListBox1
        .Clear
        .ColumnCount = 4
        .ColumnWidths = "220;110;130;10"
        If TextBox8.Value = "" Then Exit Sub
        Set r = Range("B8", Range("B" & Rows.Count).End(xlUp))
        Set f = r.Find(TextBox8.Value, LookIn:=xlValues, Lookat:=xlPart)
        If Not f Is Nothing Then
          cell = f.Address
          Do
            added = False
            For i = 0 To .ListCount - 1
              Select Case StrComp(.List(i), f.Value, vbTextCompare)
                Case 0, 1
                  .AddItem f.Value, i                 'col B
                  .List(i, 1) = f.Offset(, 2).Value   'col D
                  .List(i, 2) = f.Offset(, 5).Text    'col G .TEXT is used as date in Listbox was shown as 12/17/2020 as opposed 17/12/2020
                  .List(i, 3) = f.Row                 'row
                  added = True
                  Exit For
              End Select
            Next
            If added = False Then
              .AddItem f.Value                                 'col B
              .List(.ListCount - 1, 1) = f.Offset(, 2).Value   'col D
              .List(.ListCount - 1, 2) = f.Offset(, 5).Text    'col G .TEXT is used as date in Listbox was shown as 12/17/2020 as opposed 17/12/2020
              .List(.ListCount - 1, 3) = f.Row                 'row
            End If
            Set f = r.FindNext(f)
          Loop While Not f Is Nothing And f.Address <> cell
          TextBox8 = UCase(TextBox8)
          .TopIndex = 0
        Else
          MsgBox "NO CUSTOMER WAS FOUND USING THAT INFORMATION", vbCritical, "POSTAGE SHEET CUSTOMER NAME SEARCH"
        End If
      End With
    Application.SendKeys ("{TAB}")
    End If
    End Sub

 

[ipbr21054]

Thanks,
I will take a look later once home from vacation.