Saltar al contenido
PROGRAMAR EN VBA MACROS DE EXCEL

Como Copiar y Eliminar Item al Pasar de Listbox a Otro Listbox con Doble Click y Vicerversa


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

En este post se mostrará Como Pasar Registros de Un Listbox a Otro Listbox Eliminar Item y Viceversa Haciendo Doble Click; es una variante de lo realizado al presionar Enter y se expuso en Como Copiar y Eliminar Item al Pasar de Listbox a Otro y Viceversa Listbox con Enter.

La macro permite al hacer doble click en el Listbox pasar los datos a otro listbox, eliminando los datos del primer listbox; en el segundo listbox se puede hacer lo mismo es decir presionando doble click se pasa al primer listbox eliminando los registros del del segundo listbox.

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.

  
Suscribe a nuestro canal para que YouTube te avise cuando se suba nuevo contenido al canal, en el vídeo encontrarás una explicación gráfica y detallada del ejemplo que se muestra en este post.

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);
}
}

 

Se sugiere descargar el ejemplo de esta forma será más fácil entender el ejemplo, al presionar el botón que se encuentra en la hoja de Excel del libro que contiene el ejemplo, se muestra un formulario con dos listbox, el Listbox1 con fondo color Rosa y el Listbox2 con fondo color Blanco.

Si se hace doble click sobre un ítem seleccionado en el primer listbox, se pasarán los datos al segundo Listbox eliminando los registros del Listbox con fondo Rosa, en el caso que no hayamos equivocado o simplemente, porque no se requiera que ese ítem este en el Listbox2, se puede hacer doble click en este Listbox2 pasar el ítem nuevamente el Listbox1 y eliminar el registro del Listbox con fondo Blanco.

⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛

Quizá sea de utilidad también

Como agregar un botón maximizar minimizar a un formulario de Excel

Como sumar celdas de un mismo color

Como enviar mail con outlook adjuntando archivo PDF

El código se debe escribir en el evento doble click del Listbox1 y Listbox2, obviamente haciendo referencia a cada control correspondiente, básicamente el código es el siguiente:

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
On Error Resume Next
Set a = UserForm1.ListBox1
Set b = UserForm1.ListBox2
fila = UserForm1.ListBox1.ListIndex
El códigos anteriores permiten crear un objeto con el Listbox1 y Listbox2 y determinar la fila del Listbox con el ítem seleccionado.
b.AddItem a.List(fila, 0)
b.List(b.ListCount – 1, 1) = a.List(fila, 1)
b.List(b.ListCount – 1, 2) = a.List(fila, 2)
b.List(b.ListCount – 1, 3) = a.List(fila, 3)
a.RemoveItem a.ListIndex
Los códigos anteriores, permiten pasar los datos de un Listbox a otro Listbox y con la última línea de código remover o borrar los registros del Listbox, lo anteriormente especificado se debe realizar con el Listbox2 en su evento doble click.

La descarga del ejemplo se realiza desde el final del post y seguidamente se muestra el código del ejemplo Como Copiar y Eliminar Registro al Pasar de un Listbox a Otro con Doble Click.

Código que se inserta en un Formulario de Excel

‘**************https://macrosenexcel.com  **** https://youtube.com/programarexcel*********

Private Sub CommandButton1_Click()
Unload Me
End Sub

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
On Error Resume Next
Set a = UserForm1.ListBox1
Set b = UserForm1.ListBox2
fila = UserForm1.ListBox1.ListIndex
b.AddItem a.List(fila, 0)
b.List(b.ListCount – 1, 1) = a.List(fila, 1)
b.List(b.ListCount – 1, 2) = a.List(fila, 2)
b.List(b.ListCount – 1, 3) = a.List(fila, 3)
a.RemoveItem a.ListIndex
End Sub
Private Sub ListBox2_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
On Error Resume Next
Set aa = UserForm1.ListBox1
Set bb = UserForm1.ListBox2
fila = bb.ListIndex
aa.AddItem bb.List(fila, 0)
aa.List(aa.ListCount – 1, 1) = bb.List(fila, 1)
aa.List(aa.ListCount – 1, 2) = bb.List(fila, 2)
aa.List(aa.ListCount – 1, 3) = bb.List(fila, 3)
bb.RemoveItem bb.ListIndex
End Sub
Private Sub TextBox1_Change()
On Error Resume Next
Set b = Sheets(«Hoja1»)
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 = «Hoja1!A2:D» & uf
   Exit Sub
End If
b.AutoFilterMode = False
Me.ListBox1.Clear
Me.ListBox1.RowSource = Clear
For i = 2 To uf
   strg = b.Cells(i, 1).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)
   End If
Next i
Me.ListBox1.ColumnWidths = «20 pt;90pt;80 pt;80 pt»
End Sub
Private Sub TextBox2_Change()
On Error Resume Next
Set b = Sheets(«Hoja1»)
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 = «Hoja1!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, 2).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;90pt;80 pt;80 pt»
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 = 4
    .ColumnWidths = «20 pt;90pt;80 pt;80 pt»
    ‘.RowSource = «Hoja2!A2:» & wc & uf
End With
With Me.ListBox2
    .ColumnCount = 4
    .ColumnWidths = «25 pt;90pt;60 pt;60 pt»
End With

uf = b.Range(«A» & Rows.Count).End(xlUp).Row
For i = 2 To uf
       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)

Next i

Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Código que se inserta en un módulo

‘**************https://macrosenexcel.com  **** https://youtube.com/programarexcel*********

Sub muestra1()
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 (24)

Hi there, after reading this remarkable post i am too happy
to share my experience here with mates.

Responder

If you would like to increase your experience just keep visiting this site and be updated with the
hottest news posted here.

Responder

I just like the valuable information you supply
to your articles. I’ll bookmark your blog and take a look
at once more right here regularly. I am quite certain I’ll be
told lots of new stuff right here! Best of luck for the following!

Responder

If some one wants expert view on the topic of blogging afterward i propose him/her to go to
see this blog, Keep up the nice work.

Responder

I loved as much as you will receive carried out right here.
The sketch is tasteful, your authored material stylish.
nonetheless, you command get got an edginess over that you
wish be delivering the following. unwell unquestionably
come further formerly again as exactly the same nearly very
often inside case you shield this increase.

Responder

Hello there! Do you know if they make any plugins to protect against hackers?

I’m kinda paranoid about losing everything I’ve worked hard on. Any recommendations?

Responder

Hi, i read your blog from time to time and i own a similar
one and i was just wondering if you get a lot of
spam remarks? If so how do you prevent it, any plugin or anything
you can suggest? I get so much lately it’s driving me mad so any help is
very much appreciated.

Responder

When someone writes an post he/she maintains the thought of a user in his/her mind that how a user can be aware of it.
Therefore that’s why this post is great. Thanks!

Responder

Wow, amazing blog layout! How long have you been blogging for?
you make blogging look easy. The overall look of your website is fantastic, as well as the content!

Responder

Everyone loves it when folks come together and share opinions.
Great website, keep it up!

Responder

Hi, I do believe this is an excellent web site. I stumbledupon it 😉
I’m going to revisit yet again since i have bookmarked
it. Money and freedom is the best way to change, may
you be rich and continue to guide others.

Responder

I am curious to find out what blog platform you are using?
I’m experiencing some small security issues with my latest site and I’d
like to find something more risk-free. Do you have any suggestions?

Responder

Good info. Lucky me I discovered your site by accident (stumbleupon).

I have saved it for later!

Responder

Wow, fantastic blog layout! How long have you been blogging for?
you make blogging look easy. The overall look of your site
is wonderful, let alone the content!

Responder

I go to see daily some blogs and information sites to read articles
or reviews, except this webpage gives feature based
articles.

Responder

Great post however , I was wanting to know if you could write a litte more
on this subject? I’d be very grateful if you could elaborate a little bit more.
Thanks!

Responder

Ridiculous quest there. What happened after? Thanks!

Responder

Great post. I was checking continuously this blog and I am
impressed! Extremely useful info specifically the ultimate phase 🙂 I take care of such info a lot.
I was looking for this certain information for a long time.
Thanks and good luck.

Responder

I have been exploring for a little bit for any high quality articles or
blog posts on this sort of space . Exploring in Yahoo I eventually stumbled upon this website.
Studying this information So i am happy to show that I have an incredibly excellent uncanny feeling I
discovered just what I needed. I such a lot surely will make certain to
don?t omit this web site and provides it a glance on a constant basis.

Responder

I blog often and I seriously appreciate your content.
This article has truly peaked my interest. I will take a note of your blog and keep checking for new details about once
a week. I subscribed to your Feed too.

Responder

What’s up Dear, are you in fact visiting this web
site daily, if so after that you will definitely take pleasant experience.

Responder

Hey there! Someone in my Facebook group shared this website with us so I came to give
it a look. I’m definitely enjoying the information. I’m bookmarking and will be tweeting this to my followers!
Superb blog and wonderful style and design.

Responder

Thanks for a marvelous posting! I quite enjoyed reading it, you can be
a great author. I will make sure to bookmark your blog
and may come back someday. I want to encourage you to
continue your great work, have a nice morning!

Responder

Thanks for some other informative website. Where else could
I get that type of information written in such a perfect approach?
I’ve a project that I’m simply now operating
on, and I’ve been at the glance out for such info.

Responder