Moving row from Listbox1 to Listbox2

P

Patriot2879

Well-known Member
Joined
Feb 1, 2018
Messages
1,259
Office Version
  1. 2010
Platform
  1. Windows
Hi good morning, hope you can help me please, i have a Userform1 which has a listbox1 and listbox2 in it, i have a command button for listbox1, for when this is clicked i want it to remove the row selected and move it into listbox2 i have the code below but it is not working, i have a background of yellow on a line of code 'Me.ListBox2.AddItem Me.ListBox1.List(iCnt)'. hope you can help me please.
VBA Code: 
Private Sub CommandButton8_Click()
    
    'Variable Declaration
    Dim iCnt As Integer
    
    'Move Items from ListBox1 to ListBox2
    For iCnt = 0 To Me.ListBox1.ListCount - 1
        Me.ListBox2.AddItem Me.ListBox1.List(iCnt)
    Next iCnt
    
    'Clear ListBox1 After moving Items from ListBox1 to ListBox2
    Me.ListBox1.Clear
    
End Sub
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Sort by date Sort by votes
Patriot2879 said:
Hi good morning, hope you can help me please, i have a Userform1 which has a listbox1 and listbox2 in it, i have a command button for listbox1, for when this is clicked i want it to remove the row selected and move it into listbox2 i have the code below but it is not working, i have a background of yellow on a line of code 'Me.ListBox2.AddItem Me.ListBox1.List(iCnt)'. hope you can help me please.
VBA Code: 
Private Sub CommandButton8_Click()
   
    'Variable Declaration
    Dim iCnt As Integer
   
    'Move Items from ListBox1 to ListBox2
    For iCnt = 0 To Me.ListBox1.ListCount - 1
        Me.ListBox2.AddItem Me.ListBox1.List(iCnt)
    Next iCnt
   
    'Clear ListBox1 After moving Items from ListBox1 to ListBox2
    Me.ListBox1.Clear
   
End Sub
Click to expand...
I have also tried this code below but this does not work when commandbutton is pressed.
VBA Code: 
Private Sub CommandButton8_Click()
    
   With Me.ListBox1
      For i = .ListCount - 1 To 0 Step -1
         If .Selected(i) Then
            Me.ListBox2.AddItem .List(i)
            For j = 1 To 2
               Me.ListBox2.List(Me.ListBox2.ListCount - 1, j) = .List(i, j)
            Next j
            .RemoveItem (i)
         End If
      Next i
   End With
End Sub
 
Upvote 0
Out of interest, how do you populate the listbox's in the first instance?
 
Upvote 0
Georgiboy said:
Out of interest, how do you populate the listbox's in the first instance?
Click to expand...
Hi for the listboxes i use the code below to populate.
VBA Code: 
Private Sub UserForm_Initialize()
  Dim lr As Long
  Dim sh As Worksheet
 
  'set the sheet with data
  Set sh = Sheets("Additional Job")
 
  'get the last row in sheet
  lr = sh.Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
 
  'work with listbox1
  With ListBox1
    .ColumnCount = 10                                                   'Set the column Amount
    .ColumnHeads = True
    .RowSource = "'" & sh.Name & "'!" & sh.Range("A2:J" & lr).Address   'Fill the Listbox
  End With
 
 
  'set the sheet with data
  Set sh = Sheets("Outages data")
 
  'get the last row in sheet
  lr = sh.Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
 
  'work with listbox1
  With ListBox2
    .ColumnCount = 10                                                   'Set the column Amount
    .ColumnHeads = True
    .RowSource = "'" & sh.Name & "'!" & sh.Range("A2:J" & lr).Address   'Fill the Listbox
  End With
 

j = Application.WorksheetFunction.CountIf(ThisWorkbook.Sheets("In Day VL").Range("A:A"), "*CAG*")
UserForm1.TextBox2.Value = j
 j = Application.WorksheetFunction.CountIf(ThisWorkbook.Sheets("In Day VL").Range("A:A"), "*CC*")
 UserForm1.TextBox3.Value = j
 j = Application.WorksheetFunction.CountIf(ThisWorkbook.Sheets("In Day VL").Range("A:A"), "*Additional Job*")
 UserForm1.TextBox4.Value = j
 j = Application.WorksheetFunction.CountIf(ThisWorkbook.Sheets("In Day VL").Range("A:A"), "*Sickness*")
 UserForm1.TextBox5.Value = j
 j = Application.WorksheetFunction.CountIf(ThisWorkbook.Sheets("In Day VL").Range("A:A"), "*Stores*")
 UserForm1.TextBox6.Value = j
 
End Sub
 
Upvote 0
I think this is part of the issue, are you looking to get the change made in the listbox's to reflect in the range on the worksheet?
 
Upvote 0
Patriot2879 said:
I am yes is this possible hope you can help please?
Click to expand...
I have tried amended the code to the below, where it needs to look in Outages Data sheet and find what is selected in listbox1, then remove from Outages Data and paste into Additional Job sheet, then refresh listbox1 and listbox2, but it doesnt work.
VBA Code: 
Private Sub CommandButton8_Click()
    
Dim sh, sh2 As Worksheet
Dim lRw As Long
Dim Rng As Range

Set sh = ThisWorkbook.Worksheets("Outages data")
Set sh2 = ThisWorkbook.Worksheets("Additional Job")
    

lRw = lstEile.ListIndex + 1

Product = sh.Range("A" & lRw).Value

  With sh
 
    .Cells(lRw, 3).Value = ListBox1.Value
      
  End With
  ListBox1.Value = ""
 
 
  Set Rng = Sheet2.Columns("A:A").Find(What:=Product, _
    LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)

If Rng Is Nothing Then Exit Sub  'exit if no data found
    
    With sh2
    
        .Cells(Rng.Row, 2).Value = ListBox1.Value
 
    End With
 
End Sub
 
Upvote 0
I have put the below together, see how you get on with it?
VBA Code: 
Option Explicit

Dim sh1 As Worksheet, lrA As Long
Dim sh2 As Worksheet, lrB As Long

Private Sub CommandButton1_Click()
    Dim i As Long, itm As Long
       
    For i = 0 To Me.ListBox1.ListCount - 1
        If Me.ListBox1.Selected(i) Then
            itm = i + 2
            Exit For
        End If
    Next i
   
    sh1.Rows(itm).Cut sh2.Range("A" & lrB + 1)
    sh1.Rows(itm).Delete
    Application.CutCopyMode = False
   
    ws1Rng
    ws2Rng
End Sub

Private Sub UserForm_Initialize()
    Set sh1 = Sheets("Sheet1")
    Set sh2 = Sheets("Sheet2")
   
    ws1Rng
    ws2Rng
End Sub

Sub ws1Rng()
    Dim rng1 As Range
    lrA = sh1.Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
    Set rng1 = sh1.Range("A2:J" & lrA)
   
    With ListBox1
      .ColumnCount = 10 'Set the column Amount
      .ColumnHeads = True
      .RowSource = rng1.Address(, , , 1) 'Fill the Listbox
    End With
End Sub

Sub ws2Rng()
    Dim rng2 As Range
    lrB = sh2.Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
    Set rng2 = sh2.Range("A2:J" & lrB)
   
    With ListBox2
      .ColumnCount = 10 'Set the column Amount
      .ColumnHeads = True
      .RowSource = rng2.Address(, , , 1) 'Fill the Listbox
    End With
End Sub
 
Last edited:
Upvote 0
EDIT: I have amended the code above a couple of times, so it might be worth taking another look depending on when you first viewed it.
 
Upvote 0
Georgiboy said:
I have put the below together, see how you get on with it?
VBA Code: 
Option Explicit

Dim sh1 As Worksheet, lrA As Long
Dim sh2 As Worksheet, lrB As Long

Private Sub CommandButton1_Click()
    Dim i As Long, itm As Long
      
    For i = 0 To Me.ListBox1.ListCount - 1
        If Me.ListBox1.Selected(i) Then
            itm = i + 2
            Exit For
        End If
    Next i
  
    sh1.Rows(itm).Cut sh2.Range("A" & lrB + 1)
    sh1.Rows(itm).Delete
    Application.CutCopyMode = False
  
    ws1Rng
    ws2Rng
End Sub

Private Sub UserForm_Initialize()
    Set sh1 = Sheets("Sheet1")
    Set sh2 = Sheets("Sheet2")
  
    ws1Rng
    ws2Rng
End Sub

Sub ws1Rng()
    Dim rng1 As Range
    lrA = sh1.Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
    Set rng1 = sh1.Range("A2:J" & lrA)
  
    With ListBox1
      .ColumnCount = 10 'Set the column Amount
      .ColumnHeads = True
      .RowSource = rng1.Address(, , , 1) 'Fill the Listbox
    End With
End Sub

Sub ws2Rng()
    Dim rng2 As Range
    lrB = sh2.Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
    Set rng2 = sh2.Range("A2:J" & lrB)
  
    With ListBox2
      .ColumnCount = 10 'Set the column Amount
      .ColumnHeads = True
      .RowSource = rng2.Address(, , , 1) 'Fill the Listbox
    End With
End Sub
Click to expand...
HI I have changed the commandbutton name and sheet names accordingly, but in my coding i have another Userform_initialize so i kept getting an error the code i changed it too is the below, but this didnt work, i had then an error come up on UserForm1 where the 2 listboxes are located. -
VBA Code: 
Option Explicit

Dim rng1 As Range, rng2 As Range
Dim sh1 As Worksheet, lrA As Long
Dim sh2 As Worksheet, lrB As Long

Private Sub CommandButton8_Click()
    Dim i As Long, itm As Long
    
    For i = 0 To Me.ListBox1.ListCount - 1
        If Me.ListBox1.Selected(i) Then
            itm = i + 2
            Exit For '?
        End If
    Next i
 
    sh1.Rows(itm).Cut sh2.Range("A" & lrB + 1)
    sh1.Rows(itm).Delete
    Application.CutCopyMode = False
 
    ws1Rng
    ws2Rng
End Sub

Private Sub UserForm_Initialize()
    Set sh1 = Sheets("Outages Data")
    Set sh2 = Sheets("Additional Job")
 
    ws1Rng
    ws2Rng
End Sub

Sub ws1Rng()
  lrA = sh1.Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
  Set rng1 = sh1.Range("A2:J" & lrA)

  With ListBox1
    .ColumnCount = 10 'Set the column Amount
    .ColumnHeads = True
    .RowSource = rng1.Address(, , , 1) 'Fill the Listbox
  End With
End Sub

Sub ws2Rng()
  lrB = sh2.Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
  Set rng2 = sh2.Range("A2:J" & lrB)
 
  With ListBox2
    .ColumnCount = 10 'Set the column Amount
    .ColumnHeads = True
    .RowSource = rng2.Address(, , , 1) 'Fill the Listbox
  End With
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Searching using TextBox and ListBox
Replies
1
Views
279
campbell1093
C
G
  • Question Question
Move the selected item up one position in the list Userform ListBox
Replies
4
Views
93
giri u
G
I
  • Question Question
Select Listbox value to be then taken to customer on worksheet
Replies
1
Views
138
ipbr21054
I
GomaPile
  • Question Question
Listbox1 Error
Replies
4
Views
270
GomaPile
GomaPile
I
  • Solved
Listbox to show dd/mm/yyyy & not mm/dd/yyyy
2
Replies
13
Views
154
Alex Blakenburg
A

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top