How can I make a UserForm ComboBox that will populate named columns into a ListBox or TextBox as a list?

Question

So I am currently trying to make an interactive inventory userform for our ambulance to streamline things. What I am trying to do is, is allow users to select an item from a ComboBox that references a location on our ambulance that would then populate a list of items into a ListBox that shows them 2 columns of info. The Quantity of the item and the Item name. Everything I am finding has to do with just filling a single text box with a single line of info. If you look at the picture above I would like to also have the data with the headers also showing the column for the qty and item description. I need help with the coding on to do this cause I cant find any VBA code on something like this. Screenshot added above to ind of get an idea of what I am trying to do.

The current code I have for this is:

Private Sub ListBox1_Change()
  If ComboBox1.Value = "Cabinet 1" Then
     ListBox1.AddItem Sheet5.Range("Cabinet_1")
  End If
End Sub

Okay so that worked but how would I get the code to not include the blank cells in the columns additionally how do i get it to snake the columns — Kenneth Brannon, Jul 15 '19 at 22:23
@Kenneth to give you an idea for your follow up question of not include blanks - you could add the specified range to an `Array` and then loop through tue array to remove blanks and finally write the array to the sheet in the desired location. Sorry i cant give you specific code at the moment but with a bit of google you should find an abundance of solutions for doing this. — Samuel Everson, Jul 16 '19 at 09:54
@SamuelEverson I figured it out. I had my named range selected for the entire row so I went back an changed it to only the cells that had the data in it. No I just need to figure out how to snake the columns in the list so that there is no scrolling. — Kenneth Brannon, Jul 16 '19 at 15:15
@kenneth ah even easier! With the 'snaking' i think a loop with a counter will be the best bet? Probably with an if statement checking if the counter value is > the max row before it starts from the top again? — Samuel Everson, Jul 18 '19 at 11:31
@kenneth or this might help? https://stackoverflow.com/a/11222439/9663006 — Samuel Everson, Jul 18 '19 at 11:32

How can I make a UserForm ComboBox that will populate named columns into a ListBox or TextBox as a list?

0 Answers0