Saltar al contenido
PROGRAMAR EN VBA MACROS DE EXCEL

Llenar listbox dependiendo de otro listbox y combobox con vba para excel


(adsbygoogle = window.adsbygoogle || []).push({});

En un post anterior se expuso un ejemplo de marcro sobre como llenar un listbox dependiendo del dato seleccionado en  un combobox, aquí presento esta macro de Excel o procedimiento de VBA para Excel desarrollado íntegramente Visual Basic para Aplicaciones, también en este ejemplo se hace depender los datos encontrados del valor que es previamente ingresado en combobox, la variante es que no sólo depende de un combobox, sino que depende de otro listbox, el ejemplo muestra como llenar un listbox dependiendo de otro listbox y combobox; resumiendo los datos que muestra  el listbox1, van a depender del valor seleccionado en el combobox y luego de los datos seleccionados en el listbox2.

Si te estás iniciando en la operación de Excel o requieres afirmar conocimientos, recomiendo leer un excelente libro sobre Excel que te ayudará operar las planillas u hojas de cálculo, haz click acá, si quieres aprender sobre Excel, en inglés, entonces debes hacer click here. Si lo que necesitas es aprender o profundizar sobre la programación de macros con VBA este es unos de los mejores cursos on line que he visto en internet.

  

La macro VBA excel corre  al apretar el  botón que llama al formulario, en el formulario existen, un  combobox, dos listbox y botón de opciones, que nos permiten definir el orden  que le quisiéramos dar a los datos encontrados, es decir ascendente o descendente.
Al elegir un dato en el combobox, que se dota de datos al cargarse el formulario, se realiza una búsqueda de datos que luego son cargados en el listbox correspondiente; si se hace doble click en algún dato de los desplegados en el listbox, se procede  a realizar otra búsqueda de datos que son cargados en el listbox destinado a ello.
Los botones de opciones se ponen para que si se desea se pueda ordenar en forma  ascendente o descendente, según como se prefiera, dichas opciones se han puesto en cada uno de los listbox que se presenta en el ejemplo.
 

El vídeo verás la macro en acción con una explicación más detallada de su codificación y funcionamiento, recomiendo observar para una más fácil comprensión de la macro; suscribe a nuestra web desde la parte superior derecha de la página ingresando tu mail y a nuestro canal de You Tube para recibir en tu correo vídeos explicativos sobre macros interesantes, como  por ejemplo Recorre fila buscando y comparando datos de dos columnas en hojas distintasbuscar en listbox mientras escribes en textboxcomo crear una factura o sale invoice y grabar guardar PDF XLS y enviar por mailtrabajando con filas, celdas, columnas, rangos y muchos ejemplos más.

  

El código que se encuentra a continuación se debe ingresar en un  userform, descargando el ejemplo lo podrás ver en funcionamiento, analizar, modificar y adaptar a lo que tú estés realizando el código está abierto sin ningún tipo de restricción.

Podrás ver como funciona al descargar, desde el link del final, el ejemplo de la macro, lo cual facilita mucho su comprensión, si bien se trata de hacer los ejemplos lo más fácil posible, algún conocimiento en VBA se debería tener para poder modificar las macros y adaptarla para de este modo automatizar tu libro excel.

Private Sub ListBox2_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
‘Evito movimientos de la pantalla
Application.ScreenUpdating = False
Dim fila, a, colum1 As Integer
On Error Resume Next
‘Borra datos del listbox
ListBox1.Clear
a = 0
fila = 2
‘Selecciono columna donde buscar
d = Combobox1
Select Case d
Case «Departamento»
colum1 = 5
Case «Nacionalidad»
colum1 = 2
Case «Estado»
colum1 = 3
End Select
‘Bucle mientras la fila no esté vacia
While Sheets(«hoja1»).Cells(fila, colum1) <> Empty
      d = ListBox2.Value
‘Si el dato de la fila coincide con textbox carga los datos al listbox
 
   If Sheets(«hoja1»).Cells(fila, colum1) = d Then

        ‘Copia los datos de la celda list box
        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
  
‘Aumento la fila para que pase a la siguiente
fila = fila + 1
Wend
‘Mostrar el número de entradas en el ListBox1.
UserForm1.Label8.Caption = «Total Registros: » & a + 1

‘Devuelvo movimientos de la pantalla
Application.ScreenUpdating = True
End Sub

Private Sub Combobox1_Change()
‘Evito movimientos de la pantalla
Application.ScreenUpdating = False
Dim fila2, colum2, b, uf As Integer
Dim col As String
‘Dim ran As Range
Dim DSR As New Collection
On Error Resume Next
‘Borra datos del listbox
ListBox2.Clear
b = 0
fila2 = 2
‘Selecciono columna donde buscar
dato = Combobox1
Select Case dato
Case «Departamento»
colum2 = 5
Case «Nacionalidad»
colum2 = 2
Case «Estado»
colum2 = 3
End Select
Select Case colum2
Case 5
col = «E»
Case 2
col = «B»
Case 3
col = «C»
End Select
‘Determina rango de datos a cargar al listbox
uf = Sheets(«hoja1»).Range(«A» & Rows.Count).End(xlUp).Row
ran = col & fila2 & «:» & col & uf
For Each Celda In Range(ran)
DSR.Add Celda.Value, CStr(Celda.Value)
Next Celda
‘Agregar elementos a la lista
For Each Item In DSR
UserForm1.ListBox2.AddItem Item
Next Item
‘Mostrar el número de entradas en el ListBox1.
UserForm1.Label7.Caption = «Total Registros: » & DSR.Count

‘Devuelvo movimientos de la pantalla
Application.ScreenUpdating = True
End Sub
Private Sub OptionButton1_Click()
‘Evito movimientos de la pantalla
Application.ScreenUpdating = False
Dim r1, r2, r3 As String
‘determines last row with data
uf = Sheets(«hoja1»).Range(«A» & Rows.Count).End(xlUp).Row
‘will help determine ranges to sort data
r1 = «b2» & «:b» & uf
r3 = «A1» & «:E» & uf
‘sorts the data
 ActiveWorkbook.Worksheets(«hoja1»).Sort.SortFields.Clear
 ActiveWorkbook.Worksheets(«hoja1»).Sort.SortFields.Add Key:=Range(r1) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
 With ActiveWorkbook.Worksheets(«hoja1»).Sort
        .SetRange Range(r3)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
 End With

‘Luego de ordenar corre macro para llenar listbox
Combobox1_Change
‘Devuelvo movimientos de la pantalla
Application.ScreenUpdating = True
End Sub
Private Sub OptionButton2_Click()
‘Evito movimientos de la pantalla
Application.ScreenUpdating = False
Dim r1, r2, r3 As String
‘determines last row with data
uf = Sheets(«hoja1»).Range(«A» & Rows.Count).End(xlUp).Row
‘will help determine ranges to sort data
r1 = «b2» & «:b» & uf
‘Se usa para filtrar por otro criterio
‘r2 = «B2» & «:B» & uf
r3 = «A1» & «:E» & uf
‘sorts the data
 ActiveWorkbook.Worksheets(«hoja1»).Sort.SortFields.Clear
 ActiveWorkbook.Worksheets(«hoja1»).Sort.SortFields.Add Key:=Range(r1) _
        , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal

 ‘Segundo criterio de filtrado
 ‘ActiveWorkbook.Worksheets(«hoja1»).Sort.SortFields.Add Key:=Range(r2) _
       ‘ , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal

 With ActiveWorkbook.Worksheets(«hoja1»).Sort
        .SetRange Range(r3)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
 End With

‘Luego de ordenar corre macro para llenar listbox
Combobox1_Change
‘Devuelvo movimientos de la pantalla
Application.ScreenUpdating = True
End Sub
Private Sub OptionButton3_Click()
‘Evito movimientos de la pantalla
Application.ScreenUpdating = False
Dim r1, r2, r3 As String
‘determines last row with data
uf = Sheets(«hoja1»).Range(«A» & Rows.Count).End(xlUp).Row
‘will help determine ranges to sort data
r1 = «A2» & «:A» & uf
r2 = «E2» & «:E» & uf
r3 = «A1» & «:E» & uf
‘sorts the data
 ActiveWorkbook.Worksheets(«hoja1»).Sort.SortFields.Clear
 ActiveWorkbook.Worksheets(«hoja1»).Sort.SortFields.Add Key:=Range(r1) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

 ‘Segundo criterio de filtrado
 ActiveWorkbook.Worksheets(«hoja1»).Sort.SortFields.Add Key:=Range(r2) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

 With ActiveWorkbook.Worksheets(«hoja1»).Sort
        .SetRange Range(r3)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
 End With

‘Carga datos ordenados al listbox
Dim fila, a, colum1 As Integer
On Error Resume Next
‘Borra datos del l istbox
ListBox1.Clear
a = 0
fila = 2
‘Selecciono columna donde buscar
d = Combobox1
Select Case d
Case «Departamento»
colum1 = 5
Case «Nacionalidad»
colum1 = 2
Case «Estado»
colum1 = 3
End Select
‘Bucle mientras la fila no esté vacia
While Sheets(«hoja1»).Cells(fila, colum1) <> Empty
      d = ListBox2.Value
‘Si el dato de la fila coincide con textbox carga los datos al listbox
   If Sheets(«hoja1»).Cells(fila, colum1) = d Then

        ‘Copia los datos de la celda list box
        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
  
‘Aumento la fila para que pase a la siguiente
fila = fila + 1
Wend
‘Devuelvo movimientos de la pantalla
Application.ScreenUpdating = True
End Sub
Private Sub OptionButton4_Click()
‘Evito movimientos de la pantalla
Application.ScreenUpdating = False
Dim r1, r2, r3 As String
‘determines last row with data
uf = Sheets(«hoja1»).Range(«A» & Rows.Count).End(xlUp).Row
‘will help determine ranges to sort data
r1 = «A2» & «:A» & uf
r2 = «E2» & «:E» & uf
r3 = «A1» & «:E» & uf
‘sorts the data
 ActiveWorkbook.Worksheets(«hoja1»).Sort.SortFields.Clear
 ActiveWorkbook.Worksheets(«hoja1»).Sort.SortFields.Add Key:=Range(r1) _
        , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal

 ‘Segundo criterio de filtrado
 ActiveWorkbook.Worksheets(«hoja1»).Sort.SortFields.Add Key:=Range(r2) _
        , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal

 With ActiveWorkbook.Worksheets(«hoja1»).Sort
        .SetRange Range(r3)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
 End With

‘Carga datos ordenados al listbox
Dim fila, a, colum1 As Integer
On Error Resume Next
‘Borra datos del l istbox
ListBox1.Clear
a = 0
fila = 2
‘Selecciono columna donde buscar
d = Combobox1
Select Case d
Case «Departamento»
colum1 = 5
Case «Nacionalidad»
colum1 = 2
Case «Estado»
colum1 = 3
End Select

‘Bucle mientras la fila no esté vacia
While Sheets(«hoja1»).Cells(fila, colum1) <> Empty
      d = ListBox2.Value
‘Si el dato de la fila coincide con textbox carga los datos al listbox
   If Sheets(«hoja1»).Cells(fila, colum1) = d Then

        ‘Copia los datos de la celda list box
        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
  
‘Aumento la fila para que pase a la siguiente
fila = fila + 1
Wend
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)
Label7.Caption = Clear
Label8.Caption = Clear
Combobox1.AddItem («Departamento»)
Combobox1.AddItem («Nacionalidad»)
Combobox1.AddItem («Estado»)

End Sub
     

Código que  se debe ingresar en un módulo

Sub form()
UserForm1.Show
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.


(adsbygoogle = window.adsbygoogle || []).push({});

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      

Entradas relacionadas

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

Comentarios (34)

Why users still use to read news papers when in this technological world all is available on web?

Responder

Wonderful, what a weblog it is! This webpage provides helpful facts to us, keep it up.

Responder

Someone essentially lend a hand to make critically articles I would state.
That is the first time I frequented your website page and
to this point? I surprised with the research you made to create this actual put up incredible.
Fantastic job!

Responder

If some one desires expert view on the topic of running a blog afterward i propose him/her to pay a quick visit this web
site, Keep up the good job.

Responder

A motivating discussion is definitely worth comment.
I think that you should write more about this topic, it
may not be a taboo subject but generally people don’t discuss such topics.
To the next! Kind regards!!

Responder

Very quickly this site will be famous amid all
blogging users, due to it’s good articles

Responder

Generally I do not learn post on blogs, however I wish to say
that this write-up very pressured me to try and do it!
Your writing taste has been surprised me. Thank you, quite nice article.

Responder

Hey there! Do you know if they make any plugins to safeguard against hackers?
I’m kinda paranoid about losing everything I’ve
worked hard on. Any recommendations?

Responder

We still cannot quite feel like I made it worse come to be the staring at the important points located on your webblog. My in laws so i are sincerely thankful for use in your generosity and then for giving me possibility pursue our chosen profession path. Document important info Managed to get with the web-site.

Responder

some stores have really bad customer service while others have topnotch custmer service’

Responder

hey there and thank you for your info – I’ve definitely picked up
something new from right here. I did however expertise some technical points using this
website, as I experienced to reload the site a lot of times previous to I could get it to load correctly.
I had been wondering if your web hosting is OK? Not that I am complaining, but sluggish loading instances
times will very frequently affect your placement in google and can damage your high quality
score if advertising and marketing with Adwords. Well I’m adding
this RSS to my email and could look out for a lot
more of your respective fascinating content. Ensure that you update this again soon.

Responder

If some one desires expert view regarding blogging and site-building after that i recommend him/her
to go to see this website, Keep up the nice work.

Responder

I really like your blog.. very nice colors & theme.
Did you make this website yourself or did you hire someone to do it for
you? Plz respond as I’m looking to construct my own blog and would like to know
where u got this from. kudos

Responder

Greetings! This is my first visit to your blog!
We are a group of volunteers and starting a
new project in a community in the same niche. Your blog
provided us useful information to work on. You have done
a wonderful job!

Responder

I just could not depart your site prior to suggesting that I actually enjoyed
the standard info a person provide on your guests?

Is gonna be again often in order to investigate cross-check
new posts

Responder

I’ve been browsing online more than three hours today, yet I never found any interesting article
like yours. It is pretty worth enough for me.
In my opinion, if all website owners and bloggers made good content as you did,
the internet will be a lot more useful than ever before.

Responder

Hey! This is my first visit to your blog!
We are a group of volunteers and starting a new initiative in a community in the same niche.

Your blog provided us beneficial information to work on. You have done a outstanding job!

Responder

Hey there, You’ve done a great job. I will certainly digg it and personally suggest to my
friends. I am sure they’ll be benefited from this
site.

Responder

I don’t even know the way I finished up here, however I believed this put up was once great.
I do not know who you’re however certainly you are
going to a famous blogger should you are not already. Cheers!

Responder

Hey! This is kind of off topic but I need some guidance from an established blog.

Is it very hard to set up your own blog? I’m not very techincal but
I can figure things out pretty fast. I’m thinking about creating my
own but I’m not sure where to begin. Do you have any ideas or suggestions?
Thank you

Responder

Hi there! This post could not be written any better! Reading this post reminds me of my good old room mate!
He always kept chatting about this. I will forward this write-up to him.
Pretty sure he will have a good read. Thanks for sharing!

Responder

Having read this I thought it was very enlightening. I appreciate
you finding the time and effort to put this short article together.
I once again find myself spending way too much time both reading
and commenting. But so what, it was still worth it!

Responder

Does your website have a contact page? I’m having trouble locating it but, I’d like to shoot you an email.
I’ve got some ideas for your blog you might be interested in hearing.
Either way, great site and I look forward to seeing
it grow over time.

Responder

I’m excited to uncover this page. I need to to thank you for
ones time for this fantastic read!! I definitely liked every little bit of it and I have you bookmarked to see new information on your site.

Responder

What a stuff of un-ambiguity and preserveness of precious knowledge concerning unexpected
feelings.

Responder

Article writing is also a fun, if you know after that you can write
if not it is complex to write.

Responder

The other day, while I was at work, my cousin stole my iPad and tested to see if it can survive
a forty foot drop, just so she can be a youtube sensation. My apple ipad is now destroyed and she has 83 views.

I know this is totally off topic but I had to share it with someone!

Responder

Definitely imagine that which you stated. Your favorite justification appeared to be at the web the simplest thing
to have in mind of. I say to you, I definitely get annoyed whilst people
think about worries that they plainly don’t understand about.
You managed to hit the nail upon the top as well as outlined out the whole thing with no need side-effects , other people could take a signal.
Will probably be again to get more. Thanks

Responder

I have been surfing online more than 2 hours today, yet I never found any interesting article like
yours. It’s pretty worth enough for me. Personally,
if all site owners and bloggers made good content
as you did, the internet will be a lot more useful
than ever before.

Responder

I read this piece of writing fully on the topic of the comparison of most
up-to-date and earlier technologies, it’s amazing article.

Responder

Hi there it’s me, I am also visiting this web site daily,
this web site is really good and the visitors are in fact sharing nice thoughts.

Responder

I’m excited to uncover this web site. I want to to thank you for
ones time for this particularly fantastic read!! I definitely
loved every little bit of it and i also have you book-marked to check out new things on your web site.

Responder

Hi, I check your new stuff like every week. Your story-telling style is witty, keep doing what you’re doing!

Responder

I’ve been browsing online more than 3 hours today, yet I never
found any interesting article like yours.

It’s pretty worth enough for me. In my view, if all
site owners and bloggers made good content as you did,
the web will be a lot more useful than ever before.

Responder