Unable to set focus on Textbox

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
4,071
Office Version
  1. 2007
Platform
  1. Windows
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.


Rich (BB code):
    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

Well-known Member
Joined
Nov 26, 2020
Messages
1,519
Office Version
  1. 2016
Platform
  1. Windows
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
Rich (BB 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
Rich (BB code):
Set r = Range("B8", Range("B" & Rows.Count).End(xlUp))
you can just use
Rich (BB code):
Set r = sh.Range("B8", sh.Range("B" & Rows.Count).End(xlUp))
If you want to do copy also you can just use
Rich (BB code):
sh.Range("B8").Copy
instead of selecting sh and use
Rich (BB code):
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
Rich (BB code):
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.
VBA Code:
    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
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
4,071
Office Version
  1. 2007
Platform
  1. Windows
Thanks,
I will take a look later once home from vacation.
 

Forum statistics

Threads
1,140,932
Messages
5,703,234
Members
421,285
Latest member
Bebek

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top