PROGRAMAR EN VBA MACROS PARA EXCEL: How to fill combobox which depends on another combobox and fill listbox

How to fill combobox which depends on another combobox and fill listbox






In filling listbox depending on another listbox and combobox, fill combobox and search data are among other post are related to the present example called Combobox depends on another combobox and fill listbox, there are also others where they are exposed macros on how to walk the rows with macro and are: rows searching and comparing data, scroll rows and count cells with data, scroll rows and copy from column A to B, there are several examples you will find here.

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 example that can be downloaded FREE from the end of the page is to fill a combobox depending on another combobox and this combobox depends on a listbox. When you load the form, a list of products is loaded in the first combobox, which filters them to produce only single products and avoid duplicates; Depending on the data selected in this combobox is filled the second combobox turn the data selected in the second combobox is filled a listbox.

The macro example that fills in combobox depending on another combobox and in turn controls the data of a listbox, can be downloaded from the link at the end of this post, once downloaded the example is pressed the button that says "Run Macro" Executes the macro doing the filling of the first combobox, after the second combobox based on those selected in the first combobox; Finally the listbox depends on the selected in the previous combobox.

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

Sub Botón1_Haga_clic_en()
UserForm2.Show
End Sub



Code that is inserted into a userform

Public dir
Private Sub ComboBox1_Change()
Dim fila As Integer
Dim uf As Integer
Dim d1, d2 As String
fila = 2
uf = Sheets("hoja2").Range("A" & Rows.Count).End(xlUp).Row
ComboBox2.Clear
While Sheets("hoja2").Cells(fila, 1) <> Empty
d1 = ComboBox1
d2 = Sheets("hoja2").Cells(fila, 1)
If d1 = d2 Then
ComboBox2.AddItem Sheets("hoja2").Cells(fila, 4)
End If
fila = fila + 1
Wend

End Sub
Private Sub ComboBox2_Change()
Application.ScreenUpdating = False
Dim fila, a As Integer
Dim dato, var As String
'On Error Resume Next
'Borra datos del listbox
ListBox1.Clear
ListBox1.ColumnCount = 6

a = 0
fila = 2
While Sheets("hoja2").Cells(fila, 4) <> Empty
      dato = ComboBox2
'Si el dato de la fila coincide con textbox carga los datos al listbox
  var = Sheets("hoja2").Cells(fila, 4)
   If var = dato Then
        dir = Sheets("hoja2").Cells(fila, 4).Address(False, False)
        
        a = ListBox1.ListCount
        ListBox1.AddItem
        ListBox1.List(a, 0) = Sheets("hoja2").Cells(fila, 1)
        ListBox1.List(a, 1) = Sheets("hoja2").Cells(fila, 2)
        ListBox1.List(a, 2) = Sheets("hoja2").Cells(fila, 3)
        ListBox1.List(a, 3) = Sheets("hoja2").Cells(fila, 4)
        ListBox1.List(a, 4) = Sheets("hoja2").Cells(fila, 5)
        ListBox1.List(a, 5) = Sheets("hoja2").Cells(fila, 6)
   End If


fila = fila + 1
Wend


Application.ScreenUpdating = True
End Sub

Private Sub CommandButton2_Click()
If ComboBox3 = Empty Then
MsgBox ("Debe cargar fecha de salida"), vbCritical, "AVISO"
ComboBox3.SetFocus
Exit Sub
End If
Sheets("hoja2").Range(dir).Offset(0, 2) = ComboBox3
ComboBox1.Clear
ComboBox2.Clear
ListBox1.Clear
ComboBox1.SetFocus
MsgBox ("El registro se guardó con éxito"), vbInformation, "AVISO"
ComboBox3.Clear
End Sub

Private Sub CommandButton3_Click()
Unload Me
End Sub
Private Sub UserForm_Initialize()
Dim sd As New Collection
Dim celda As Range
Dim dato
Dim r As String
Dim uf As Integer
Application.ScreenUpdating = False
On Error Resume Next
ComboBox1.Clear
Sheets("hoja2").Select
Range("A2").Select
uf = Range("A" & Rows.Count).End(xlUp).Row
r = "A2:A" & uf
For Each celda In Range(r)
sd.Add celda.Value, CStr(celda.Value)
Next celda
For Each dato In sd
ComboBox1.AddItem dato
Next dato
Application.ScreenUpdating = True
End Sub



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