Saltar al contenido
PROGRAMAR EN VBA MACROS DE EXCEL

Pasar datos de listbox con doble click a hoja de excel


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

En esta oportunidad muestro como pasar datos de un listbox a una hoja de Excel con doble click, anteriormente se han publicado otros post relacionados buscar en listbox mientras se escribe en textbox y pasar datos de listbox a hoja de Excel, existe una variante que es pasar datos de un listbox a una hoja de Excel presionando enter.

Antes de seguir 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.

  
El ejemplo permite pasar datos del listbox a la hoja de Excel, descargando el ejemplo desde el link del final observarás un botón que permite ejecutar una macro la cual muestra un listbox, haciendo doble click en un item del listbox se copia el dato en la hoja de Excel.
El vídeo que sigue muestra una explicación más detallada y gráfica de la macro presentada, 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 formulario que crea un listado de todas las hojas para poder luego seleccionarlasbuscar en listbox mientras escribes en textboxordenar hojas libro excel por su nombreconectar Excel con Access y muchos ejemplos más.

function onYtEvent(payload) {
if (payload.eventType == ‘subscribe’) {
// Add code to handle subscribe event.
} else if (payload.eventType == ‘unsubscribe’) {
// Add code to handle unsubscribe event.
}
if (window.console) { // for debugging only
window.console.log(‘YT event: ‘, payload);
}
}

Código que se inserta en un módulo

Sub muestra1()
UserForm1.Show
End Sub

Código que se inserta en userform

Private Sub CommandButton1_Click()
Unload Me
End Sub

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Set a = Sheets(«Hoja1»)
filaedit = a.Range(«A» & Rows.Count).End(xlUp).Row + 1
fila = Me.ListBox1.ListIndex
a.Cells(filaedit, «A») = ListBox1.List(fila, 0)
a.Cells(filaedit, «B») = ListBox1.List(fila, 1)
a.Cells(filaedit, «C») = ListBox1.List(fila, 2)
a.Cells(filaedit, «D») = ListBox1.List(fila, 3)
a.Cells(filaedit, «E») = ListBox1.List(fila, 4)
a.Cells(filaedit, «F») = ListBox1.List(fila, 5)
a.Cells(filaedit, «G») = ListBox1.List(fila, 6)
a.Cells(filaedit, «H») = ListBox1.List(fila, 7)

End Sub

Private Sub TextBox1_Change()
On Error Resume Next
Set b = Sheets(«Hoja2»)
uf = b.Range(«A» & Rows.Count).End(xlUp).Row
If Trim(TextBox1.Value) = «» Then
     ‘Me.ListBox1.List() = b.Range(«A2:H» & uf).Value
     Me.ListBox1.RowSource = «Hoja2!A2:H» & uf
   Exit Sub
End If
b.AutoFilterMode = False
Me.ListBox1 = Clear
Me.ListBox1.RowSource = Clear
For i = 2 To uf
   strg = b.Cells(i, 3).Value
   If UCase(strg) Like UCase(TextBox1.Value) & «*» Then
       Me.ListBox1.AddItem b.Cells(i, 1)
       Me.ListBox1.List(Me.ListBox1.ListCount – 1, 1) = b.Cells(i, 2)
       Me.ListBox1.List(Me.ListBox1.ListCount – 1, 2) = b.Cells(i, 3)
       Me.ListBox1.List(Me.ListBox1.ListCount – 1, 3) = b.Cells(i, 4)
       Me.ListBox1.List(Me.ListBox1.ListCount – 1, 4) = b.Cells(i, 5)
       Me.ListBox1.List(Me.ListBox1.ListCount – 1, 5) = b.Cells(i, 6)
       Me.ListBox1.List(Me.ListBox1.ListCount – 1, 6) = b.Cells(i, 7)
       Me.ListBox1.List(Me.ListBox1.ListCount – 1, 7) = b.Cells(i, 8)
   End If
Next i
Me.ListBox1.ColumnWidths = «20 pt;70 pt;180 pt;80 pt;60 pt;60 pt;60 pt;60pt»
End Sub
Private Sub TextBox2_Change()
On Error Resume Next
Set b = Sheets(«Hoja2»)
uf = b.Range(«A» & Rows.Count).End(xlUp).Row
If Trim(TextBox2.Value) = «» Then
     ‘Me.ListBox1.List() = b.Range(«A2:H» & uf).Value
     Me.ListBox1.RowSource = «Hoja2!A2:H» & uf
   Exit Sub
End If
b.AutoFilterMode = False
Me.ListBox1 = Clear
Me.ListBox1.RowSource = Clear
For i = 2 To uf
   strg = b.Cells(i, 4).Value
   If UCase(strg) Like UCase(TextBox2.Value) & «*» Then
       Me.ListBox1.AddItem b.Cells(i, 1)
       Me.ListBox1.List(Me.ListBox1.ListCount – 1, 1) = b.Cells(i, 2)
       Me.ListBox1.List(Me.ListBox1.ListCount – 1, 2) = b.Cells(i, 3)
       Me.ListBox1.List(Me.ListBox1.ListCount – 1, 3) = b.Cells(i, 4)
       Me.ListBox1.List(Me.ListBox1.ListCount – 1, 4) = b.Cells(i, 5)
       Me.ListBox1.List(Me.ListBox1.ListCount – 1, 5) = b.Cells(i, 6)
       Me.ListBox1.List(Me.ListBox1.ListCount – 1, 6) = b.Cells(i, 7)
       Me.ListBox1.List(Me.ListBox1.ListCount – 1, 7) = b.Cells(i, 8)
   End If
Next i
Me.ListBox1.ColumnWidths = «20 pt;70 pt;180 pt;80 pt;60 pt;60 pt;60 pt;60pt»
End Sub

Private Sub UserForm_Initialize()
Dim fila As Long
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Set b = Sheets(«Hoja2»)
uf = b.Range(«A» & Rows.Count).End(xlUp).Row
uc = b.Cells(1, Columns.Count).End(xlToLeft).Address
wc = Mid(uc, InStr(uc, «$») + 1, InStr(2, uc, «$») – 2)
With Me.ListBox1
    .ColumnCount = 8
    .ColumnWidths = «20 pt;70 pt;180 pt;80 pt;60 pt;60 pt;60 pt;60pt»
    .RowSource = «Hoja2!A2:» & wc & uf
End With
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
On Error GoTo Fin
If CloseMode <> 1 Then Cancel = True
Fin:
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 un comentario

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

Comentarios (32)

I’m the proprietor of JustCBD Store company (justcbdstore.com) and am planning to grow my wholesale side of company. I am hoping someone at targetdomain is able to provide some guidance 🙂 I considered that the very best way to accomplish this would be to connect to vape companies and cbd stores. I was hoping if anybody could suggest a qualified site where I can get CBD Shops B2B Data I am presently checking out creativebeartech.com, theeliquidboutique.co.uk and wowitloveithaveit.com. Not sure which one would be the very best selection and would appreciate any assistance on this. Or would it be simpler for me to scrape my own leads? Ideas?

Responder

Проведем официальную процедуру и это процедура ликвидация ООО вопрос интереснен каждому директору компании

Responder

Very good post. I absolutely appreciate this site. Stick with it!

Responder

Nice post. I learn something totally new and challenging on sites I stumbleupon every day. It’s always useful to read through content from other authors and practice a little something from their sites.

Responder

I’m impressed, I have to admit. Seldom do I come across a blog that’s equally educative and amusing, and without a doubt, you’ve hit the nail on the head. The issue is an issue that not enough people are speaking intelligently about. Now i’m very happy I stumbled across this during my hunt for something concerning this.

Responder

This is a topic which is near to my heart… Best wishes! Exactly where are your contact details though?

Responder

I want to to thank you for this excellent read!! I absolutely loved every bit of it. I’ve got you book marked to check out new things you post…

Responder

Oh my goodness! Impressive article dude! Many thanks, However I am encountering troubles with your RSS. I don’t understand the reason why I am unable to join it. Is there anybody having the same RSS problems? Anyone who knows the solution will you kindly respond? Thanks!!

Responder

I needed to thank you for this wonderful read!! I certainly loved every little bit of it. I have got you book marked to look at new things you post…

Responder

After going over a number of the articles on your website, I seriously appreciate your technique of writing a blog. I saved as a favorite it to my bookmark webpage list and will be checking back in the near future. Take a look at my web site as well and let me know how you feel.

Responder

Nice post. I learn something totally new and challenging on websites I stumbleupon every day. It’s always helpful to read through content from other writers and practice something from their websites.

Responder

I couldn’t resist commenting. Exceptionally well written!

Responder

Pretty! This has been a really wonderful article. Many thanks for providing this info.

Responder

Hi there! I could have sworn I’ve been to this website before but after going through a few of the articles I realized it’s new to me. Regardless, I’m certainly happy I found it and I’ll be bookmarking it and checking back frequently!

Responder

Howdy! I could have sworn I’ve visited this website before but after looking at some of the articles I realized it’s new to me. Nonetheless, I’m certainly delighted I stumbled upon it and I’ll be book-marking it and checking back regularly!

Responder

Your style is unique compared to other folks I’ve read stuff from. Thanks for posting when you have the opportunity, Guess I’ll just book mark this site.

Responder

When I originally left a comment I seem to have clicked the -Notify me when new comments are added- checkbox and from now on every time a comment is added I recieve four emails with the same comment. There has to be a means you can remove me from that service? Thanks a lot!

Responder

I could not refrain from commenting. Exceptionally well written!

Responder

You ought to take part in a contest for one of the best sites on the internet. I most certainly will recommend this website!

Responder

Good info. Lucky me I came across your website by accident (stumbleupon). I’ve book-marked it for later!

Responder

I’m extremely pleased to discover this web site. I wanted to thank you for your time for this fantastic read!! I definitely liked every part of it and i also have you book-marked to look at new things on your web site.

Responder

Your style is very unique compared to other people I’ve read stuff from. I appreciate you for posting when you’ve got the opportunity, Guess I will just bookmark this blog.

Responder

Hello, I do believe your blog may be having internet browser compatibility issues. When I look at your web site in Safari, it looks fine but when opening in I.E., it’s got some overlapping issues. I simply wanted to give you a quick heads up! Apart from that, great blog!

Responder

When I initially left a comment I appear to have clicked the -Notify me when new comments are added- checkbox and from now on each time a comment is added I receive four emails with the exact same comment. Is there a means you can remove me from that service? Appreciate it!

Responder

I just want to say I’m new to blogging and site-building and seriously enjoyed you’re blog. Very likely I’m planning to bookmark your blog post . You certainly come with exceptional posts. Regards for sharing with us your blog.

Responder

Good post. I absolutely love this site. Keep it up!

Responder

Your style is unique compared to other people I’ve read stuff from. Many thanks for posting when you have the opportunity, Guess I will just bookmark this web site.

Responder

Hi, I do think this is a great blog. I stumbledupon it 😉 I may revisit yet again since i have saved as a favorite it. Money and freedom is the best way to change, may you be rich and continue to help other people.

Responder

This blog was… how do you say it? Relevant!! Finally I’ve found something that helped me. Thanks a lot!

Responder

Hey there! Do you use Twitter? I’d like to follow you if that would be ok.

I’m absolutely enjoying your blog and look forward to new posts.

Responder

wonderful issues altogether, you just received a brand new reader.
What could you suggest in regards to your submit that you
simply made some days in the past? Any positive? adreamoftrains web hosting

Responder

Hi, I do believe this is an excellent blog. I stumbledupon it 😉 I’m going to come back yet again since I bookmarked it. Money and freedom is the greatest way to change, may you be rich and continue to guide others.

Responder