Saltar al contenido
PROGRAMAR EN VBA MACROS DE EXCEL

Pasar datos de lisbox multiselect con doble click a Excel


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

Anteriormente se presentó una macro que permitía pasar datos de un listbox multiselect a una hoja de Excel a través de un botón, en esta oportunidad muestro una macro que permite pasar datos de listbox a hoja de excel a través de un doble click; existen otros ejemplos con listbox como

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 al hacer doble click en unos o varios items del listbox, pasar los datos seleccionados a la hoja de Excel. El código que se encuentra debajo de estas líneas es el mismo que encontrarás en el ejemplo, sin embargo al descargar el ejemplo podrás ver su funcionamiento sin tener que crear el formulario, pudiéndolo adaptar a tu proyecto.  
Desde el link del final se puede descargar cientos de macros que puedes adaptar a tu proyecto, su uso es libre, si te fueron de utilidad considera la posibilidad de invitarme una tasa de café para seguir subiendo cosas útiles.

Suscribe 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 seleccionarlas, buscar en listbox mientras escribes en textbox, ordenar hojas libro excel por su nombre, conectar 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 un formulario

Private Sub CommandButton1_Click()
Unload Me
End Sub

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Dim conta As Integer
conta = 0
Set a = Sheets(«Hoja1»)
filaedit = a.Range(«A» & Rows.Count).End(xlUp).Row + 1
For x = 0 To Me.ListBox1.ListCount – 1
    If Me.ListBox1.Selected(x) = True Then
    conta = conta + 1
    End If
Next x
If conta <> 0 Then
For x = 0 To Me.ListBox1.ListCount – 1
          If Me.ListBox1.Selected(x) = True Then
            ‘fila = Me.ListBox1.ListIndex.SelectedItem
            a.Cells(filaedit, «A») = ListBox1.List(x, 0)
            a.Cells(filaedit, «B») = ListBox1.List(x, 1)
            a.Cells(filaedit, «C») = ListBox1.List(x, 2)
            a.Cells(filaedit, «D») = ListBox1.List(x, 3)
            a.Cells(filaedit, «E») = ListBox1.List(x, 4)
            a.Cells(filaedit, «F») = ListBox1.List(x, 5)
            a.Cells(filaedit, «G») = ListBox1.List(x, 6)
            a.Cells(filaedit, «H») = ListBox1.List(x, 7)
            Me.ListBox1.Selected(x) = False
            filaedit = filaedit + 1
          End If
Next x
End If
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 (80)

I’m the manager of JustCBD Store company (justcbdstore.com) and am planning to broaden my wholesale side of company. It would be great if someone at targetdomain give me some advice . I thought that the most effective way to do this would be to talk to vape companies and cbd stores. I was really hoping if anybody at all could suggest a reputable web-site where I can purchase CBD Shops B2B Mailing List I am currently taking a look at creativebeartech.com, theeliquidboutique.co.uk and wowitloveithaveit.com. Not sure which one would be the best solution and would appreciate any assistance on this. Or would it be easier for me to scrape my own leads? Suggestions?

Responder

Быстрый результат. комментарии адвокатов банкротство юридических лиц а так же закрытие ооо с долгами

Responder

I have to thank you for the efforts you’ve put in writing this site. I really hope to see the same high-grade blog posts by you in the future as well. In truth, your creative writing abilities has inspired me to get my very own blog now 😉

Responder

Can I simply just say what a comfort to uncover somebody that really knows what they’re discussing on the web. You actually understand how to bring a problem to light and make it important. More people need to read this and understand this side of the story. I was surprised you’re not more popular because you definitely have the gift.

Responder

Good info. Lucky me I found your blog by chance (stumbleupon). I have book marked it for later!

Responder

Hello there! This article couldn’t be written much better! Looking through this post reminds me of my previous roommate! He continually kept talking about this. I will forward this information to him. Pretty sure he’ll have a great read. Thanks for sharing!

Responder

Greetings, I think your site could possibly be having internet browser compatibility problems. Whenever I look at your website in Safari, it looks fine however, when opening in Internet Explorer, it’s got some overlapping issues. I merely wanted to give you a quick heads up! Other than that, wonderful blog!

Responder

Hi there! This article couldn’t be written much better! Reading through this article reminds me of my previous roommate! He constantly kept preaching about this. I will forward this information to him. Pretty sure he will have a good read. Many thanks for sharing!

Responder

Spot on with this write-up, I honestly believe that this amazing site needs much more attention. I’ll probably be back again to read more, thanks for the info!

Responder

You’ve made some good points there. I checked on the internet for more information about the issue and found most people will go along with your views on this web site.

Responder

Having read this I thought it was extremely informative. I appreciate you spending some time and energy to put this information together. I once again find myself personally spending way too much time both reading and commenting. But so what, it was still worth it!

Responder

Very good post. I will be experiencing many of these issues as well..

Responder

May I simply say what a relief to discover a person that really understands what they’re discussing on the internet. You actually know how to bring a problem to light and make it important. More and more people ought to check this out and understand this side of your story. I was surprised you aren’t more popular because you most certainly possess the gift.

Responder

Hi there! I could have sworn I’ve visited this website before but after browsing through some of the articles I realized it’s new to me. Nonetheless, I’m certainly happy I discovered it and I’ll be book-marking it and checking back frequently!

Responder

Right here is the perfect website for anybody who hopes to find out about this topic. You know a whole lot its almost tough to argue with you (not that I really would want to…HaHa). You definitely put a brand new spin on a subject which has been discussed for many years. Great stuff, just great!

Responder

Spot on with this write-up, I absolutely think this web site needs a great deal more attention. I’ll probably be back again to read through more, thanks for the information!

Responder

Greetings! Very useful advice within this post! It is the little changes that will make the most important changes. Thanks a lot for sharing!

Responder

Howdy! This blog post could not be written any better! Looking through this post reminds me of my previous roommate! He continually kept preaching about this. I am going to send this article to him. Pretty sure he will have a very good read. I appreciate you for sharing!

Responder

This site was… how do you say it? Relevant!! Finally I’ve found something that helped me. Kudos!

Responder

Hello there! This article couldn’t be written any better! Looking at this post reminds me of my previous roommate! He always kept talking about this. I will send this post to him. Fairly certain he’s going to have a great read. Thanks for sharing!

Responder

I would like to thank you for the efforts you have put in penning this blog. I really hope to see the same high-grade blog posts by you later on as well. In truth, your creative writing abilities has inspired me to get my own, personal blog now 😉

Responder

Good post. I am going through many of these issues as well..

Responder

Great post. I will be experiencing some of these issues as well..

Responder

Good post. I learn something new and challenging on websites I stumbleupon every day. It’s always useful to read through content from other writers and use a little something from their sites.

Responder

You have made some really good points there. I checked on the internet to find out more about the issue and found most individuals will go along with your views on this site.

Responder

I blog often and I seriously appreciate your content. This article has really peaked my interest. I’m going to book mark your blog and keep checking for new details about once per week. I subscribed to your Feed as well.

Responder

I seriously love your site.. Very nice colors & theme. Did you develop this amazing site yourself? Please reply back as I’m looking to create my own website and want to learn where you got this from or exactly what the theme is named. Kudos!

Responder

This is a topic that is near to my heart… Many thanks! Where are your contact details though?

Responder

Way cool! Some extremely valid points! I appreciate you penning this article and the rest of the website is very good.

Responder

Can I just say what a relief to uncover a person that truly knows what they are discussing on the internet. You certainly realize how to bring a problem to light and make it important. A lot more people ought to look at this and understand this side of the story. I can’t believe you aren’t more popular since you certainly possess the gift.

Responder

Greetings, I think your blog may be having browser compatibility issues. When I take a look at your web site in Safari, it looks fine however, when opening in Internet Explorer, it’s got some overlapping issues. I merely wanted to provide you with a quick heads up! Besides that, excellent site!

Responder

Oh my goodness! Incredible article dude! Thanks, However I am encountering troubles with your RSS. I don’t understand why I am unable to subscribe to it. Is there anybody getting similar RSS problems? Anyone that knows the solution can you kindly respond? Thanks!!

Responder

Great post! We will be linking to this particularly great content on our website. Keep up the good writing.

Responder

I’m amazed, I have to admit. Rarely do I come across a blog that’s equally educative and entertaining, and without a doubt, you have hit the nail on the head. The problem is an issue that not enough people are speaking intelligently about. I am very happy that I found this in my search for something regarding this.

Responder

Oh my goodness! Impressive article dude! Many thanks, However I am having troubles with your RSS. I don’t understand why I can’t join it. Is there anyone else getting identical RSS issues? Anyone who knows the answer can you kindly respond? Thanks!!

Responder

I would like to thank you for the efforts you’ve put in writing this site. I’m hoping to see the same high-grade content by you later on as well. In truth, your creative writing abilities has motivated me to get my own site now 😉

Responder

This is the perfect website for anybody who wishes to understand this topic. You know a whole lot its almost hard to argue with you (not that I personally will need to…HaHa). You certainly put a fresh spin on a subject that’s been discussed for decades. Great stuff, just excellent!

Responder

Way cool! Some very valid points! I appreciate you penning this article and the rest of the site is extremely good.

Responder

You made some decent points there. I looked on the web for more information about the issue and found most individuals will go along with your views on this site.

Responder

I’m amazed, I must say. Seldom do I encounter a blog that’s equally educative and entertaining, and without a doubt, you’ve hit the nail on the head. The problem is something not enough people are speaking intelligently about. I am very happy I stumbled across this during my hunt for something concerning this.

Responder

Pretty! This was an extremely wonderful post. Thank you for providing this info.

Responder

I have to thank you for the efforts you’ve put in penning this site. I’m hoping to view the same high-grade blog posts by you later on as well. In fact, your creative writing abilities has inspired me to get my very own site now 😉

Responder

I blog frequently and I genuinely appreciate your content. Your article has really peaked my interest. I am going to take a note of your blog and keep checking for new details about once a week. I subscribed to your Feed too.

Responder

I couldn’t resist commenting. Well written!

Responder

When I initially commented I appear to have clicked on the -Notify me when new comments are added- checkbox and now every time a comment is added I get four emails with the exact same comment. Perhaps there is a means you can remove me from that service? Thank you!

Responder

Having read this I believed it was very enlightening. I appreciate you taking the time and effort to put this content together. I once again find myself spending a lot of time both reading and leaving comments. But so what, it was still worth it!

Responder

You made some decent points there. I checked on the net for more info about the issue and found most individuals will go along with your views on this site.

Responder

Thank you for the good writeup. It in fact was a amusement account it. Look advanced to more added agreeable from you! By the way, how could we communicate?|

Responder

The next time I read a blog, Hopefully it does not disappoint me as much as this particular one. After all, I know it was my choice to read through, but I truly thought you would probably have something interesting to say. All I hear is a bunch of complaining about something you could fix if you were not too busy seeking attention.

Responder

I seriously love your blog.. Very nice colors & theme. Did you develop this site yourself? Please reply back as I’m looking to create my own website and would love to know where you got this from or exactly what the theme is called. Thank you!

Responder

Good strategy for telling, and fastidious piece of writing to get facts on the topic of my presentation subject
material, which i am going to convey in academy.

my page DorthyHMae

Responder

I really like it whenever people get together and share ideas. Great website, keep it up!|

Responder

I’m extremely impressed with your writing skills and in addition with the layout on the
blog. Is it a paid theme or did you customize it yourself?
In any event keep up to date the nice quality writing, it can be
rare to find out a nice blog this way one today.

Here is my website; EugeneYTeele

Responder

Hey there would you mind sharing which blog platform you’re working with? I’m looking to start my own blog in the near future but I’m having a hard time making a decision between BlogEngine/Wordpress/B2evolution and Drupal. The reason I ask is because your layout seems different then most blogs and I’m looking for something completely unique. P.S Apologies for getting off-topic but I had to ask!|

Responder

hi!,I love your writing very much! share we be in contact more approximately your article on AOL? I require a specialist on this house to resolve my problem. May be that’s you! Looking ahead to look you. |

Responder

Quality articles, I am waiting for your next article. keep working

Responder

I agree with your opinion. From now on I will always support you.

Responder

Hi my friend! I wish to say that this article is awesome, nice written and come with almost all vital infos. I’d like to peer extra posts like this .|

Responder

Everyone loves it when individuals get together and share opinions. Great site, stick with it!|

Responder

It’s hard to come by experienced people about this topic, but you sound like you know what you’re talking about! Thanks

Responder

I agree with your opinion. From now on I will always support you.

Responder

Pretty! This has been a really wonderful post. Many thanks for supplying this information.

Responder

quite interesting article. however, in some cases it still needs improvement.

Responder

May I simply say what a relief to uncover somebody that actually knows what they’re discussing on the net. You certainly know how to bring a problem to light and make it important. More and more people need to check this out and understand this side of your story. I was surprised that you’re not more popular because you surely have the gift.

Responder

Everything is very open with a really clear description of the issues. It was definitely informative. Your website is useful. Thank you for sharing!

Responder

Quality articles, I am waiting for your next article. keep working

Responder

I really like it when people get together and share ideas. Great website, stick with it!

Responder

This is a great tip particularly to those new to the blogosphere. Simple but very accurate info… Thank you for sharing this one. A must read article!

Responder

Undeniably believe that which you said. Your favorite justification appeared to be on the web the easiest thing to take into accout of. I say to you, I certainly get irked at the same time as other folks consider concerns that they just don’t realize about. You controlled to hit the nail upon the highest and also defined out the whole thing without having side effect , other folks could take a signal. Will probably be back to get more. Thank you|

Responder

Your article makes perfect sense. Writing that is worth reading. oh yeah btw also visit my website. Thanks

Responder

I was able to find good advice from your blog articles.

Responder

I was able to find good info from your blog posts.

Responder

This can be an inspiration to many people. Very good job

Responder

I blog quite often and I seriously thank you for your information. Your article has truly peaked my interest. I’m going to book mark your website and keep checking for new information about once per week. I opted in for your Feed too.

Responder

znwgxqudvetlthsdidonyjwfsewgqt

Responder

You are so interesting! I do not suppose I’ve truly read something like that before. So nice to find somebody with some original thoughts on this subject. Seriously.. thanks for starting this up. This site is one thing that is required on the internet, someone with some originality!

Responder

Everyone loves it whenever people get together and share views. Great website, continue the good work!

Responder

Hi, I do believe this is an excellent website. I stumbledupon it 😉 I will return yet again since I book marked it. Money and freedom is the best way to change, may you be rich and continue to help other people.

Responder

I used to be able to find good information from your content.

Responder

Having read this I believed it was really enlightening. I appreciate you spending some time and effort to put this short article together. I once again find myself spending a lot of time both reading and commenting. But so what, it was still worthwhile!

Responder