How to fill multi-column listbox






With this VBA macro for Excel, VBA procedure or Excel VBA macro, as it is usually called, is shown fill a listbox that has several columns, with the complication that the data is not in a continuous range but rather arise from a Search data, which depends on the value that is entered in the combobox, ie a value is selected in the combo box, based on that data is obtained, once they were found they are loaded into the listbox, it has Several columns in the case of example 5; Click at the end of the post to download the example.

The example presented here has a close relationship with the macro VBA for Excel, published in my other post that deals with filling a listbox depending on another listbox and combobox.

Need to read an excellent book on Excel that will help operate the spreadsheet, click here, if you want to learn about Excel, in Spanish, then you must click here. If what you need is to learn or deepen about programming macros with VBA, this is one of the best courses on line I've seen on the internet.


  

The code below must be entered in a form that in the case of the example is named userform1, downloading the example you can see it working, analyze, modify and adapt to what you are doing the code is open without any restriction.

Subscribe to our You Tube channel to receive explanatory videos in your mail about interesting macros, such as a form that creates a list of all the sheets to be able to select themsearch in listbox while typing in textboxorder excel sheets by nameConnect Excel with Access and many more examples.






Code that is inserted into a module




Private Sub Combobox1_Change()
Application.ScreenUpdating = False
Dim fila, a As Integer
On Error Resume Next
'Borra datos del listbox
ListBox1.Clear
a = 0
fila = 2
While Sheets("hoja1").Cells(fila, 5) <> Empty
      dato = Combobox1
  Var = Sheets("hoja1").Cells(fila, 5)
   If Sheets("hoja1").Cells(fila, 5) = dato Then

         a = ListBox1.ListCount
        ListBox1.AddItem
        ListBox1.List(a, 0) = Sheets("hoja1").Cells(fila, 1)
        ListBox1.List(a, 1) = Sheets("hoja1").Cells(fila, 2)
        ListBox1.List(a, 2) = Sheets("hoja1").Cells(fila, 3)
        ListBox1.List(a, 3) = Sheets("hoja1").Cells(fila, 4)
        ListBox1.List(a, 4) = Sheets("hoja1").Cells(fila, 5)
   End If
  

fila = fila + 1
Wend

Application.ScreenUpdating = True
End Sub

Private Sub UserForm_Initialize()
Label2.Caption = Sheets("hoja1").Cells(1, 1)
Label3.Caption = Sheets("hoja1").Cells(1, 2)
Label4.Caption = Sheets("hoja1").Cells(1, 3)
Label5.Caption = Sheets("hoja1").Cells(1, 4)
Label6.Caption = Sheets("hoja1").Cells(1, 5)



Si te fue de utilidad puedes INVITARME UN CAFÉ y de esta manera ayudar a seguir manteniendo la página, CLICK para descargar en ejemplo en forma gratuita.


If this post was helpful INVITE ME A COFFEE and so help keep up the page, CLICK to download free example.


Si te gustó por favor compártelo con tus amigos
If you liked please share it with your friends