ListBox doesnt always show result thus a blank space

I

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
5,226
Office Version
  1. 2007
Platform
  1. Windows
Hi,
I am using the code supplied below.

I also attach a screenshot of the issue.


I open the userform & in the TextBox Chassis Number i enter JMA
The results are they shown in the ListBox BUT with an issue.

As you can see that there is a blank space to the right of the row 54 number.
Looking at the screenshot you will see that there is a name in column A.

I can see this happen a few times when entering a different value.

Can you see why this might be happening.

Thanks

Rich (BB code): 
Private Sub TextBoxChassisNumber_Change()
TextBoxChassisNumber = UCase(TextBoxChassisNumber)
  Dim r As Range, f As Range, cell As String, added As Boolean
  Dim sh As Worksheet
  
  Set sh = Sheets("DATABASE")
  sh.Select
  With ListBox1
    .Clear
    .ColumnCount = 8
    .ColumnWidths = "180;240;80;180"
   If TextBoxChassisNumber.Value = "" Then Exit Sub
    Set r = Range("L6", Range("L" & Rows.Count).End(xlUp))
    Set f = r.Find(TextBoxChassisNumber.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
              .List(i, 1) = f.Offset(, -10).Value
              .List(i, 2) = f.Row
              .List(i, 3) = f.Offset(, -11).Value
              added = True
              Exit For
          End Select
        Next
        If added = False Then
          .AddItem f.Value
          .List(.ListCount - 1, 1) = f.Offset(, -10).Value
          .List(.ListCount - 1, 2) = f.Row
          .List(.ListCount - 1, 3) = f.Offset(, 113).Value
         
        End If
        Set f = r.FindNext(f)
      Loop While Not f Is Nothing And f.Address <> cell
      TextBoxSearch = UCase(TextBoxSearch)
      .TopIndex = 0
    Else
      MsgBox "NO CUSTOMER WAS FOUND USING THAT INFORMATION", vbCritical, "POSTAGE SHEET CUSTOMER NAME SEARCH"
      TextBoxChassisNumber.Value = ""
      TextBoxChassisNumber.SetFocus
    End If
  End With
End Sub
 

Attachments

  • 3416.jpg
    3416.jpg
    222.5 KB · Views: 9

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Sort by date Sort by votes
In your Do /Loop While insert the following two Debug.Print lines:
VBA Code: 
              added = True
              Debug.Print "AA", I, f.Row, f.Offset(, -11).Value, "A#"     '****
              Exit For
          End Select
        Next
        If added = False Then
          .AddItem f.Value
          .List(.ListCount - 1, 1) = f.Offset(, -10).Value
          .List(.ListCount - 1, 2) = f.Row
          .List(.ListCount - 1, 3) = f.Offset(, 113).Value
          Debug.Print "BB", I, f.Row, f.Offset(, -11).Value, f.Offset(, 113).Value, "B#"  '****
        End If
When you get the error take a screenshot that shows the error; then open the vba editor, open the "Immediate window" (typing Contr-g should do the job; or Menu /View /Immediate window); copy all what is listed in the window and paste it in your next message. This way we shall be able to understand if that space is from cell offset(,-11) or offset(,113)

Bye
 
Upvote 0
Hi,
113 should be 11

Ive changed it so its correct.
I then did your advice and info supplied

BB 2 215 PAUL GILES 001 B#
AA 2 227 PETE HIGGS 001 A#
AA 2 253 ROB DALBY 002 A#
AA 2 260 ROD JONES 001 A#
AA 0 6 RICHARD WEDLOCK 001 A#
BB 0 7 A BAKALI 001 B#
BB 1 10 ALAN PINCHBECK 001 B#
AA 0 13 ALEX GOLDSCHNEIDER 001 A#
BB 3 16 ALEX MORGAN 002 B#
AA 0 17 ANDREW SMITH 001 A#
AA 1 18 ANDREW STALEY 001 A#
BB 6 20 ANDY FARRON 001 B#
AA 2 22 ANDY SMITH 001 A#
AA 0 26 ANTHONY HEYWOOD 001 A#
AA 1 29 BANWELL GARAGE 001 A#
AA 9 30 BARRY HAYES 001 A#
AA 0 36 C C HAMMERTON 001 A#
AA 8 38 CAROLINE BARNEY 001 A#
AA 1 40 CHRIS BARNES 001 A#
AA 6 43 CHRIS PAYZE 001 A#
BB 15 47 CHUCK 001 B#
BB 16 52 CHURCHILL CARS 005 B#
AA 12 54 CLAIRE TANNER 001 A#
AA 12 55 CLAIRE TANNER 002 A#
BB 19 59 COLYN HYNDMAN 001 B#
AA 8 65 DAVE MORTON 001 A#
AA 2 67 DAVE TRIGG 001 A#
AA 18 73 DAVID DEASY 001 A#
AA 13 76 DAVID WALDEN 001 A#
AA 19 80 DORA MACRIDES 001 A#
AA 18 81 DOUG WOOD 001 A#
AA 5 83 ED SWIFT 001 A#
AA 14 88 GEOFF NORTON 001 A#
AA 15 91 GERRY REID 001 A#
AA 28 92 GLYN CURTIS 001 A#
AA 28 93 GLYN CURTIS 002 A#
AA 14 96 GRAHAM HESTER 001 A#
AA 12 97 GRAHAM HUGHES 001 A#
AA 9 99 GREGOR WELLS 001 A#
AA 9 100 GREGOR WELLS 002 A#
AA 13 108 HURLEY BROTHERS 001 A#
AA 27 109 IAN HOLTEN 001 A#
AA 15 115 JAMES BARRITT 001 A#
AA 27 118 JAMES KENDALL MILNES 001 A#
AA 17 120 JAMIE ALLEN 001 A#
AA 34 130 JOHN BARRATT 001 A#
AA 27 135 JOHN HESKETH 001 A#
AA 27 136 JOHN HESKETH 002 A#
AA 2 141 JOHNATHON VARDY 001 A#
AA 10 144 JUAN FERNANDEZ 001 A#
AA 20 159 LISA COLLEY 001 A#
AA 20 160 LISA COLLEY 002 A#
AA 13 164 LUCA NOTO 001 A#
AA 30 170 MARCUS DERVILLE 001 A#
AA 41 173 MARK COULTON 001 A#
AA 42 176 MARK PETERSON 001 A#
AA 5 178 MARTIN COOKE 001 A#
AA 45 180 MARTIN PETERS 001 A#
AA 15 183 MATTHEW CONNOR 001 A#
AA 28 191 MUBARAK YUSUF 001 A#
AA 6 194 NEIL LETCHFORD 001 A#
AA 13 197 NICK HOLTOM 001 A#
AA 0 205 PADDOCK MOTORCYCLES 001 A#
AA 53 215 PAUL GILES 001 A#
AA 29 225 PETE COLEMAN 001 A#
AA 40 227 PETE HIGGS 001 A#
AA 17 228 PETER HILL 001 A#
AA 9 230 PHIL BAKER 001 A#
AA 23 234 PRISCILLAH KIHARA 001 A#
AA 11 236 R RANTZEN 001 A#
AA 42 240 RHYS DAVIES 001 A#
AA 42 241 RHYS DAVIES 002 A#
AA 11 243 RICHARD COOKE 001 A#
AA 47 253 ROB DALBY 002 A#
AA 37 254 ROB PHILLIPS 001 A#
BB 70 259 ROBERTS ENTERPRISES 001 B#
AA 48 260 ROD JONES 001 A#
AA 66 261 ROD VENNELL 001 A#
AA 57 264 ROY ASHMAN 001 A#
AA 34 266 RUSSELL LAMONT 001 A#
AA 12 276 SPENCER WONG 001 A#
AA 1 279 STEVE HESS 001 A#
AA 33 280 STEVE HUMBY 001 A#
AA 65 282 STEVE MCQUEEN 001 A#
AA 23 285 STEVE SHAW 001 A#
AA 47 295 TIM BREADIN 001 A#
AA 45 303 TONY JACOBS 001 A#
AA 19 305 URESEN NAIDOO 001 A#
AA 18 306 VERN 001 A#
AA 53 308 WENDY JESSOP 001 A#
AA 17 309 WILLIAM RAMSDEN 001 A#
AA 27 310 WINSTON YOUNG 001 A#
AA 27 311 WINSTON YOUNG 002 A#
AA 69 317 Z TEST IAN PARSONS 001 A#
AA 59 6 RICHARD WEDLOCK 001 A#
BB 0 7 A BAKALI 001 B#
BB 1 10 ALAN PINCHBECK 001 B#
AA 0 13 ALEX GOLDSCHNEIDER 001 A#
BB 3 16 ALEX MORGAN 002 B#
AA 0 17 ANDREW SMITH 001 A#
AA 1 18 ANDREW STALEY 001 A#
BB 6 20 ANDY FARRON 001 B#
AA 2 22 ANDY SMITH 001 A#
AA 0 26 ANTHONY HEYWOOD 001 A#
AA 1 29 BANWELL GARAGE 001 A#
AA 9 30 BARRY HAYES 001 A#
AA 0 36 C C HAMMERTON 001 A#
AA 8 38 CAROLINE BARNEY 001 A#
AA 1 40 CHRIS BARNES 001 A#
AA 6 43 CHRIS PAYZE 001 A#
BB 15 47 CHUCK 001 B#
BB 16 52 CHURCHILL CARS 005 B#
AA 12 54 CLAIRE TANNER 001 A#
AA 12 55 CLAIRE TANNER 002 A#
BB 19 59 COLYN HYNDMAN 001 B#
AA 8 65 DAVE MORTON 001 A#
AA 2 67 DAVE TRIGG 001 A#
AA 18 73 DAVID DEASY 001 A#
AA 13 76 DAVID WALDEN 001 A#
AA 19 80 DORA MACRIDES 001 A#
AA 18 81 DOUG WOOD 001 A#
AA 5 83 ED SWIFT 001 A#
AA 14 88 GEOFF NORTON 001 A#
AA 15 91 GERRY REID 001 A#
AA 28 92 GLYN CURTIS 001 A#
AA 28 93 GLYN CURTIS 002 A#
AA 14 96 GRAHAM HESTER 001 A#
AA 12 97 GRAHAM HUGHES 001 A#
AA 9 99 GREGOR WELLS 001 A#
AA 9 100 GREGOR WELLS 002 A#
AA 13 108 HURLEY BROTHERS 001 A#
AA 27 109 IAN HOLTEN 001 A#
AA 15 115 JAMES BARRITT 001 A#
AA 27 118 JAMES KENDALL MILNES 001 A#
AA 17 120 JAMIE ALLEN 001 A#
AA 34 130 JOHN BARRATT 001 A#
AA 27 135 JOHN HESKETH 001 A#
AA 27 136 JOHN HESKETH 002 A#
AA 2 141 JOHNATHON VARDY 001 A#
AA 10 144 JUAN FERNANDEZ 001 A#
AA 20 159 LISA COLLEY 001 A#
AA 20 160 LISA COLLEY 002 A#
AA 13 164 LUCA NOTO 001 A#
AA 30 170 MARCUS DERVILLE 001 A#
AA 41 173 MARK COULTON 001 A#
AA 42 176 MARK PETERSON 001 A#
AA 5 178 MARTIN COOKE 001 A#
AA 45 180 MARTIN PETERS 001 A#
AA 15 183 MATTHEW CONNOR 001 A#
AA 28 191 MUBARAK YUSUF 001 A#
AA 6 194 NEIL LETCHFORD 001 A#
AA 13 197 NICK HOLTOM 001 A#
AA 0 205 PADDOCK MOTORCYCLES 001 A#
AA 53 215 PAUL GILES 001 A#
AA 29 225 PETE COLEMAN 001 A#
AA 40 227 PETE HIGGS 001 A#
AA 17 228 PETER HILL 001 A#
AA 9 230 PHIL BAKER 001 A#
AA 23 234 PRISCILLAH KIHARA 001 A#
AA 11 236 R RANTZEN 001 A#
AA 42 240 RHYS DAVIES 001 A#
AA 42 241 RHYS DAVIES 002 A#
AA 11 243 RICHARD COOKE 001 A#
AA 47 253 ROB DALBY 002 A#
AA 37 254 ROB PHILLIPS 001 A#
BB 70 259 ROBERTS ENTERPRISES 001 B#
AA 48 260 ROD JONES 001 A#
AA 66 261 ROD VENNELL 001 A#
AA 57 264 ROY ASHMAN 001 A#
AA 34 266 RUSSELL LAMONT 001 A#
AA 12 276 SPENCER WONG 001 A#
AA 1 279 STEVE HESS 001 A#
AA 33 280 STEVE HUMBY 001 A#
AA 65 282 STEVE MCQUEEN 001 A#
AA 23 285 STEVE SHAW 001 A#
AA 47 295 TIM BREADIN 001 A#
AA 45 303 TONY JACOBS 001 A#
AA 19 305 URESEN NAIDOO 001 A#
AA 18 306 VERN 001 A#
AA 53 308 WENDY JESSOP 001 A#
AA 17 309 WILLIAM RAMSDEN 001 A#
AA 27 310 WINSTON YOUNG 001 A#
AA 27 311 WINSTON YOUNG 002 A#
AA 69 317 Z TEST IAN PARSONS 001 A#
AA 59 6 RICHARD WEDLOCK 001 A#
BB 0 54 CLAIRE TANNER 001 B#
AA 0 55 CLAIRE TANNER 002 A#
BB 2 215 PAUL GILES 001 B#
AA 2 227 PETE HIGGS 001 A#
AA 2 253 ROB DALBY 002 A#
AA 2 260 ROD JONES 001 A#
AA 0 6 RICHARD WEDLOCK 001 A#
BB 0 54 CLAIRE TANNER 001 B#
AA 0 55 CLAIRE TANNER 002 A#
BB 2 215 PAUL GILES 001 B#
AA 2 227 PETE HIGGS 001 A#
AA 2 253 ROB DALBY 002 A#
AA 2 260 ROD JONES 001 A#
AA 0 6 RICHARD WEDLOCK 001 A#
 

Attachments

  • 3418.jpg
    3418.jpg
    83.3 KB · Views: 3
Upvote 0
Was this long list created by a single test??
Code: 
BB             0             54           CLAIRE TANNER 001                         B#
Click to expand...
This should says that the last line with the missing column is populated by the "If added = False" condition, and f.Offset(, 13) is empty

So you should review the logic of your code

If you can share a sample workbook maybe our understanding of the situation would be better
 
Upvote 0
Yes to a single test.

But if you look here its also shows blanks.
Does that help.
 

Attachments

  • 3419.jpg
    3419.jpg
    221.6 KB · Views: 6
Upvote 0
That might correspond to this record
Code: 
BB             0             7            A BAKALI 001                B#
Click to expand...
Again f.Offset(, 13) seem to be empty
That is the only record with f.Row = 7
 
Upvote 0
HI

The answer is that 11 should be - 11 & now it works.

Many thanks
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Question
Userform does not show leading 0 for telephone number
Replies
1
Views
50
ipbr21054
I
I
  • Solved
Populate listbox but omit N/A value
Replies
2
Views
345
ipbr21054
I
I
  • Solved
Have Listbox show dd/mm/yyy
Replies
2
Views
276
ipbr21054
I
I
  • Question
Search for a value on worksheet even if there is a space involved ex HU83 or HU 83
Replies
1
Views
241
bobsan42
bobsan42
I
  • Solved
Listbox value not sorted correctly
Replies
3
Views
381
ipbr21054
I

Forum statistics

Threads
1,214,874
Messages
6,122,034
Members
449,061
Latest member
TheRealJoaquin

Share this page

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
Back
Top