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 them, search in listbox while typing in textbox, order excel sheets by name, Connect 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
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.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)
fila = fila + 1
Application.ScreenUpdating = True
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)
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