Transfer selection data from a listbox1 in userform, to another listbox2.

jdcar

Board Regular
Joined
Apr 27, 2012
Messages
156
Hi everyone
I would like to display in listbox(listbox 2) a row selection from another listbox(Listbox 1) with all its columns in userform. be will possible?
As one as the other, the codes below in listbox2 , gives me, one above the other, when I wish all the selection.
Which of these codes will be the most correct to be alterate?
Thank you
VBA Code:
Private Sub ListBox1_Click()

     Me.ListBox2.AddItem ListBox1.Column(0, ListBox1.ListIndex)
     Me.ListBox2.AddItem ListBox1.Column(1, ListBox1.ListIndex)
    Me.ListBox2.AddItem ListBox1.Column(2, ListBox1.ListIndex)
    Me.ListBox2.AddItem ListBox1.Column(3, ListBox1.ListIndex)
     Me.ListBox2.AddItem ListBox1.Column(4, ListBox1.ListIndex)
    Me.ListBox2.AddItem ListBox1.Column(5, ListBox1.ListIndex)
     Me.ListBox2.AddItem ListBox1.Column(6, ListBox1.ListIndex)
     
              OR

''other code that I take from net

Dim lastrow, curVal, x As Long
    'find last row
    lastrow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
    ' clear listbox2
    Me.ListBox2.Clear
    
 curVal = Me.ListBox1.Value
 
 For x = 2 To lastrow
    If Sheet1.Cells(x, "a") = curVal Then
       'found a match; populate listbox2
      Me.ListBox2.AddItem Sheet1.Cells(x, "a")
      Me.ListBox2.AddItem Sheet1.Cells(x, "b")
      Me.ListBox2.AddItem Sheet1.Cells(x, "c") '..........util all columns
    End If
  Next x

End Sub
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
Try this:

VBA Code:
Private Sub ListBox1_Click()
  Dim i As Long
  With ListBox2
    .AddItem
    For i = 0 To 6
      .List(.ListCount - 1, i) = ListBox1.List(ListBox1.ListIndex, i)
    Next
  End With
End Sub
 

jdcar

Board Regular
Joined
Apr 27, 2012
Messages
156
Hi
Thanks for your answer.
The code works partially. In list box 2 appears what is selected, that is what I want, BUT, when I change for other selection in list box 1, list box 2 does not change. Why?
I also ask you for a big favor when I select, the backcolor of listbox2 could be able to change the color for exemple to blue.
I thank you in advance for the help you can give me.
jdcar
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
when I change for other selection in list box 1, list box 2 does not change. Why?
Did you modify something in the code?
Do you have any other code?
What is the height of Listbox2, that is, is it high enough for more than one record to be seen?
 

jdcar

Board Regular
Joined
Apr 27, 2012
Messages
156

ADVERTISEMENT

Hi DanteAmor
Did you modify something in the code?
No
Do you have any other code?
In same listbox2, No
What is the height of Listbox 2, that is, is it high enough for more than one record to be seen?
yes, the height is only for one selection.
==XX=====
The objective of my domestic project is to present in the listbox1, through the cmd1 command, all the movements made in the all months. The cmd2 command is just in case I need to see just one specific month.
The cmd3 is used to make corrections to the selected record after selection. As this procedure after rectification, the list box returns to its beginning, when the intention is to analyze the rectification that I was made.
There are, for example, 500 movements and it is intended to rectify the registration number 375. The code rectifies but listbox1 goes back to the beginning, that is, from 1st to 20th, not being able to check immediately if the amendment was made.
Here I thought of using another listbox (listbox2), just for the movement selected in listbox1 and checking the especific rectification that was done.
For better understanding, follow the codes used below of Userform1, anduserform2 this used to rectification and save.
code:
VBA Code:
Option Explicit

Private Sub CheckBox1_Click() 'open Textbox
With CheckBox1

   If CheckBox1.Value = True Then
     ComboBox1.Value = ""
     ListBox1.Clear
     cboTxtBackColorTrue  'modulo to change backcolor True
   Else
     cboTxtBackColorFalse 'modulo to change backcolor False
   End If
End With
TextBox1.Value = ""
End Sub

Private Sub CommandButton1_Click() 'show all data of all month

ComboBox1.Value = ""
ListBox1.Clear
TextBox1.Value = ""

Application.ScreenUpdating = False
With Sheet2
     ListBox1.ColumnCount = 7
     ListBox1.List = Sheets("sheet1").Range("a1:g10000").Value
End With
 
Application.ScreenUpdating = True

End Sub

Private Sub ComboBox1_Change() 'show data of especific month choose

ListBox1.Clear
TextBox1.Value = ""

On Error Resume Next
Application.ScreenUpdating = False

With Sheet2
Sheets("sheet2").Range("k2").Value = ComboBox1.Value

ComboChooseMonths   'modulo Advance Data    ' [ Sheets("sheet1").Range("A1:G10000").AdvancedFilter Action:=xlFilterCopy, _
                                            ' CriteriaRange:=Sheets("sheet2").Range("k1:k2"), CopyToRange:=Sheets("sheet2").Range( _
                                            ' "A1:G1"), Unique:=False ]

End With

ListBox1.ColumnCount = 7
ListBox1.List = Sheets("Sheet2").Range("A1:G" & Sheets("sheet2").Range("A" & Rows.Count).End(xlUp).Row).Value

Application.ScreenUpdating = True
End Sub

Private Sub CommandButton4_Click()   'Clear boxes
      ComboBox1.Value = ""
      TextBox1.Value = ""
      CheckBox1.Value = False
      ListBox1.Clear
      ListBox2.Clear
End Sub

Private Sub CommandButton5_Click()   'Exit from userform

    Unload UserForm1
    CommandButton5.Value = Sheets("sheet2").Range("a2:g10000").ClearContents
    CommandButton5.Value = Sheets("sheet2").Range("k2").ClearContents
    CommandButton5.Value = Sheets("sheet2").Range("l2").ClearContents
    
End Sub

Private Sub ListBox1_Click()  'your code= to show in listbox2 the selection in listbox1

  Dim i As Long
  With ListBox2
    .AddItem
    For i = 0 To 6
      .List(.ListCount - 1, i) = ListBox1.List(ListBox1.ListIndex, i)
    Next i
    
  End With
  
End Sub

Private Sub TextBox1_Change()   'To choose value to be corrected

Application.ScreenUpdating = False

ListBox1.Value = ""
ComboBox1.Value = ""

On Error Resume Next

With Sheets("Sheet2")
   Sheets("Sheet2").Range("L2").Value = TextBox1.Value
   ComboChooseValor  ' the same modulo above
   ListBox1.List = Sheets("Sheet2").Range("A1:G" & .Range("A" & Rows.Count).End(xlUp).Row).Value
End With

Application.ScreenUpdating = True

End Sub
Private Sub CommandButton2_Click() 'Re_Edit

With UserForm2

UserForm2.lblIDorder.Caption = ListBox1.List(ListBox1.ListIndex)
UserForm2.TextBox4.Text = ListBox1.Column(3, ListBox1.ListIndex)
UserForm2.TextBox1.Text = ListBox1.Column(4, ListBox1.ListIndex)
UserForm2.TextBox2.Value = ListBox1.Column(5, ListBox1.ListIndex)
UserForm2.TextBox3.Text = ListBox1.Column(6, ListBox1.ListIndex)
End With

UserForm2.Show

ListBox1.List = Sheets("sheet1").Range("a1:g10000").Value

End Sub
Private Sub CommandButton3_Click() 'Delete

Dim i As Integer

If MsgBox("Are you sure you want to delete this row?", vbYesNo + vbQuestion, "Delete row") = vbYes Then

      For i = 2 To Range("g10000").End(xlUp).Row
        If Cells(i, 1) = ListBox1.List(ListBox1.ListIndex) Then

            Rows(i).Select
            Selection.Delete
           ListBox1.List = Sheets("sheet1").Range("a1:g10000").Value
        End If
      Next i

End If

End Sub

Private Sub UserForm_Initialize()
Me.ComboBox1.List = Array("JANEIRO", "FEVEREIRO", "MARÇO", "ABRIL", "MAIO", "JUNHO", "JULHO", "AGOSTO", "SETEMBRO", "OUTUBRO", "NOVEMBRO", "DEZEMBRO")

End Sub
Userform2
VBA Code:
Option Explicit

Private Sub CommandButton1_Click() 'SAVE the Re_Edit

Dim i As Integer

With UserForm1.ListBox1
  For i = 2 To Range("g10000").End(xlUp).Row
     If Cells(i, 1) = lblIDorder.Caption Then
        Cells(i, 4) = TextBox4.Text 'tipo Pagtº
        Cells(i, 5) = TextBox1.Text  'tipo Despesa
        Cells(i, 6) = TextBox2.Value  'valores
        Cells(i, 7) = TextBox3.Text   'Obs
     End If
  Next i
End With

MsgBox "saved!", vbInformation

Unload Me

With UserForm1
 UserForm1.CheckBox1.Value = False
 UserForm1.TextBox1.Value = ""
 UserForm1.ComboBox1.Value = ""
End With
End Sub
I appeal to your patience and goodwill to help me in the resolution of this my objective, with my reinforcement of thanks.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
yes, the height is only for one selection.

You just have to clean listbox2 to show the new record:

Rich (BB code):
Private Sub ListBox1_Click()
  Dim i As Long
  With ListBox2
    .Clear
    .AddItem
    For i = 0 To 6
      .List(.ListCount - 1, i) = ListBox1.List(ListBox1.ListIndex, i)
    Next
  End With
End Sub
 

jdcar

Board Regular
Joined
Apr 27, 2012
Messages
156

ADVERTISEMENT

Hi
For the initial question, everything is ok, it's solved
Without wanting to abuse the presentation of the questions, what will be the possibility for the listbox2, to be able to present the change that is made through commadbutton2.
Thank you
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
what will be the possibility for the listbox2, to be able to present the change that is made through commadbutton2.
I think it is a new topic, you could create a new thread and there you explain with examples what you want.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
I'm glad to help you. Thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,800
Messages
5,574,402
Members
412,590
Latest member
Velly
Top