Border around value in Listbox1

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,793
Office Version
  1. 2007
Platform
  1. Windows
Afternoon,

I type a word in TextBox1 & press the RETUN / ENTER button.
All that match are then shown in ListBox1

Ive noticed that certain words will show a border around the 14th value in the list.
This doesnt happen on every search but quite a few.

Do you see what i did wrong in the code ?

Thanks very much

I am using the code shown below.


Rich (BB code):
Private Sub ListBox1_Click()
  Set sh = Sheets("POSTAGE")
  sh.Select
  Range("C" & ListBox1.List(ListBox1.ListIndex, 3)).Select
  Unload PostageItemSoldSearch
End Sub
Private Sub TextBox1_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 = 6
    .ColumnWidths = "240;100;250;50;150;100"
    If TextBox1.Value = "" Then Exit Sub
    Set r = Range("C8", Range("C" & Rows.Count).End(xlUp))
    Set f = r.Find(TextBox1.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                 'Item
              .List(i, 1) = f.Offset(, -2).Value  'Date
              .List(i, 3) = f.Row                 'Row Number
              .List(i, 2) = f.Offset(, -1).Value  'Customers Name
              .List(i, 4) = f.Offset(, 6).Value   'Ebay User Name
              .List(i, 5) = f.Offset(, 1).Value   'Info

              added = True
              Exit For
          End Select
        Next
        If added = False Then
              .AddItem f.Value                                 'Item
              .List(.ListCount - 1, 1) = f.Offset(, -2).Value  'Date
              .List(.ListCount - 1, 3) = f.Row                 'Row Number
              .List(.ListCount - 1, 2) = f.Offset(, -1).Value  'Customer Name
              .List(.ListCount - 1, 4) = f.Offset(, 6).Value  'Ebay User Name
              .List(.ListCount - 1, 5) = f.Offset(, 1).Value  'Info
              
        End If
        Set f = r.FindNext(f)
      Loop While Not f Is Nothing And f.Address <> cell
      TextBox1 = UCase(TextBox1)
      .TopIndex = 0
    Else
      MsgBox "NO SOLD ITEM WAS FOUND USING THAT INFORMATION", vbCritical, "POSTAGE SHEET SOLD ITEM SEARCH"
      TextBox1.Value = ""
      TextBox1.SetFocus
    End If
  End With
  End If
End Sub
 

Attachments

  • 853.jpg
    853.jpg
    85.3 KB · Views: 7

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

EXCEL MAX

Board Regular
Joined
Nov 11, 2020
Messages
243
Office Version
  1. 2007
Platform
  1. Windows
First:
Change this line
VBA Code:
 Range("C" & ListBox1.List(ListBox1.ListIndex, 3)).Select
with
VBA Code:
Range("C" & ListBox1.ListIndex + 1).Select
Second:
What do you expect to happen? It's focus. Listbox now got focus.
What do you trying to do? Do you want to select item in the listbox or remove focus,
or only to display item in the listbox and return focus to the textbox?
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,793
Office Version
  1. 2007
Platform
  1. Windows
Hi
Why does it even select anything or put a border around it.

I type a word etc in the text box.
the code searches my worksheet and all the matches are put into the listbox.

nothing should have any boxer around it at all.
I then click on one value in the listbox & I am then taken to that record on the worksheet.
 

EXCEL MAX

Board Regular
Joined
Nov 11, 2020
Messages
243
Office Version
  1. 2007
Platform
  1. Windows
Through lines of code textbox1 losing focus and can't back focus to itself.
Temporary move focus to another control and then back him to textbox.

If you are changed line as I say before now do this.
Add one line in the code above message box.
VBA Code:
ListBox1.SetFocus
MsgBox "NO SOLD ITEM WAS FOUND USING THAT INFORMATION", vbCritical, "POSTAGE SHEET SOLD ITEM SEARCH"
TextBox1.SetFocus
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,793
Office Version
  1. 2007
Platform
  1. Windows
The change of code in post #2 no longer selects the correct row on the worksheet.

The extra line of code mentioned in post #4 makes no difference & the line / border around the listbox value still shown.

Thanks but i think i will just put up with it.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,776
Messages
5,574,170
Members
412,574
Latest member
shadowfighter666
Top