Bizarre Listbox Population Issue

[afrazier1221]

Okay, this is a weird glitch, and I'm not sure what to do about it. For lack of better options available to me, I'm using Excel and vba to create a database/POS type of system for my mobile auto repair business. It will ultimately allow me to have a list of customers, and individual lists of vehicles, estimates, and invoices keyed to their respective customers. Up to this point, with a little cussing and careful work, I've managed to get it working one step at a time. BUT . . . I've run into a bizarre issue. The primary listbox for my customer list sometimes lists only one name, sometimes one name and a blank spot above the one name, and sometimes all the names. At one point it became apparent that the sheet names were contributing to the glitch. When I used default names in the coding; e.g. Sheet1.Cells(1, 1), the glitch would suddenly occur if I used a non-default name, like Customers.Cells(1, 1). So I switched all the coding to the old fashioned way of designating a Sheet; viz. Sheets("Sheet1") or Sheets("Customers"), etc. This corrected the problem at that time. Then the glitch started to occur when I created a sheet that had a two-word name and a space. The listbox didn't like that. So I reformatted the names to include an underscore.

To clarify, when I say the listbox didn't like it, I mean the listbox didn't like it. All the coding in general works. Even the listbox itself works, except for populating correctly. Things that should have absolutely no bearing on the listbox whatsoever are making the listbox glitch out. Nothing else. None of the other listboxes. Just that one.

The most recent head-scratcher . . . When I Run the user form from visual basic to test as I go, I have discovered that whether the listbox populates correctly or not is now directly affected by which workbook sheet I happen to have active, or in focus. If I'm on the "Customers" sheet, the listbox populates correctly. If I'm on the "Vehicles" sheet, I'll get a blank space and one name.

This is all truly just bizarre. I pride myself on being able to find solutions, but this one really has me in a state of wtf. Any feedback anyone has concerning their experience with this sort of glitch and how they overcame it would be greatly appreciated.

 

[Akuini]

@afrazier1221, wecome to the Forum.

Then the glitch started to occur when I created a sheet that had a two-word name and a space. The listbox didn't like that. So I reformatted the names to include an underscore.

If the sheet name has a space, you need to wrap it with apostrophes.

ListBox1.RowSource = "'my customer'!A1:A5"

I have discovered that whether the listbox populates correctly or not is now directly affected by which workbook sheet I happen to have active, or in focus. If I'm on the "Customers" sheet, the listbox populates correctly. If I'm on the "Vehicles" sheet, I'll get a blank space and one name.

How did you populate the listbox?

 

[afrazier1221]

@afrazier1221, wecome to the Forum.


If the sheet name has a space, you need to wrap it with apostrophes.

ListBox1.RowSource = "'my customer'!A1:A5"

How did you populate the listbox?

Last_Row = Cells(Rows.Count, "A").End(xlUp).Row
With CDLB
.ColumnCount = 2
.ColumnHeads = False
.ColumnWidths = "0,165"
.RowSource = "Customers!AF2:AG" & Last_Row
End With

That's the code for populating it. BUT, I think I see the issue. I neglected to identify the sheet. If the sheet isn't identified for the row count, then it's defaulting to the active sheet. And if the active sheet only has one row worth of entries, or none at all, then although it takes the data from the right sheet because the row source is identified, the row count is relative to the active sheet.

You didn't actually help, but you helped. lol. Sometimes I have to say it out loud to work through a problem. And making me post the code caused me to scrutinize it and catch the error. I just made the correction, gave it a try, and problem solved.

Last_Row = Sheets("Customers").Cells(Rows.Count, "A").End(xlUp).Row
With CDLB
.ColumnCount = 2
.ColumnHeads = False
.ColumnWidths = "0,165"
.RowSource = "Customers!AF2:AG" & Last_Row
End With

 

[Akuini]

Ok, great.
But what about sheet names with a space? Is it resolved by adding apostrophes?

 

[afrazier1221]

Ok, great.
But what about sheet names with a space? Is it resolved by adding apostrophes?

Honestly, I think the error I mentioned was the problem all along. I may have assumed that the other issues were the problem when in actual fact I was probably on the wrong page causing the unspecified default to count the wrong sheet. I could go back and change everything to find out if the apostrophes were the issue, but at this point it works. I prefer to move forward. Problem solved for now.

 

[Akuini]

Ok, glad you figured it out.