Moving row from Listbox1 to Listbox2

P

Patriot2879

Well-known Member
Joined
Feb 1, 2018
Messages
1,227
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
 
Sort by date Sort by votes
I don't understand the below:
i had then an error come up on UserForm1 where the 2 listboxes are located
Click to expand...

What line do you get an error on?
 
Upvote 0

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Patriot2879 said:
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
Click to expand...
Test sheet Hi I have attached a link to dropbox where i have put a sample file if that helps.
 
Last edited:
Upvote 0
Make a copy of your file and try replacing all (clear out all of the current code in there) of the code in Userform1 with the below:
VBA Code: 
Option Explicit

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.ListBox2.ListCount - 1
        If Me.ListBox2.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()
    Dim rng1 As Range
    
    lrA = sh1.Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
    If lrA = 1 Then lrA = 2
    Set rng1 = sh1.Range("A2:J" & lrA)
    
    With ListBox2
      .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 ListBox1
      .ColumnCount = 10 'Set the column Amount
      .ColumnHeads = True
      .RowSource = rng2.Address(, , , 1) 'Fill the Listbox
    End With
End Sub

Your listbox's were the other way around to what I had them.
 
Upvote 0
I have run it a few times - on about the 7th time i did get an error - Unable to set RowSource, would be interested to see if you get the same?

I added about 20 rows of data to youre 'Outages data' tab to test.
 
Upvote 0
Georgiboy said:
Make a copy of your file and try replacing all (clear out all of the current code in there) of the code in Userform1 with the below:
VBA Code: 
Option Explicit

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.ListBox2.ListCount - 1
        If Me.ListBox2.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()
    Dim rng1 As Range
   
    lrA = sh1.Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
    If lrA = 1 Then lrA = 2
    Set rng1 = sh1.Range("A2:J" & lrA)
   
    With ListBox2
      .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 ListBox1
      .ColumnCount = 10 'Set the column Amount
      .ColumnHeads = True
      .RowSource = rng2.Address(, , , 1) 'Fill the Listbox
    End With
End Sub

Your listbox's were the other way around to what I had them.
Click to expand...
Tester Hi thank you for the update, but i got an error on the userform_utilize. i have attached the file with the updated code hope you can advise please and thank you for your support.
 
Upvote 0
You had more than one 'UserForm_Initialize' in there, remove ALL code from the UserForm1 and try the below:
VBA Code: 
Option Explicit

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

Private Sub CommandButton2_Click()
    Unload Me
End Sub

Private Sub CommandButton3_Click()
    UserForm2.Show
End Sub

Private Sub CommandButton4_Click()
    UserForm5.Show
End Sub

Private Sub CommandButton5_Click()
    UserForm4.Show
End Sub

Private Sub CommandButton6_Click()
    UserForm3.Show
End Sub

Private Sub CommandButton7_Click()
    UserForm6.Show
End Sub

Private Sub CommandButton9_Click()
    UserForm7.Show
End Sub

Private Sub CommandButton8_Click()
    Dim i As Long, itm As Long
 
    For i = 0 To Me.ListBox2.ListCount - 1
        If Me.ListBox2.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()
    Dim rng1 As Range
 
    lrA = sh1.Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
    If lrA = 1 Then lrA = 2
    Set rng1 = sh1.Range("A2:J" & lrA)
 
    With ListBox2
      .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 ListBox1
      .ColumnCount = 10 'Set the column Amount
      .ColumnHeads = True
      .RowSource = rng2.Address(, , , 1) 'Fill the Listbox
    End With
End Sub
 
Upvote 0
Georgiboy said:
You had more than one 'UserForm_Initialize' in there, remove ALL code from the UserForm1 and try the below:
VBA Code: 
Option Explicit

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

Private Sub CommandButton2_Click()
    Unload Me
End Sub

Private Sub CommandButton3_Click()
    UserForm2.Show
End Sub

Private Sub CommandButton4_Click()
    UserForm5.Show
End Sub

Private Sub CommandButton5_Click()
    UserForm4.Show
End Sub

Private Sub CommandButton6_Click()
    UserForm3.Show
End Sub

Private Sub CommandButton7_Click()
    UserForm6.Show
End Sub

Private Sub CommandButton9_Click()
    UserForm7.Show
End Sub

Private Sub CommandButton8_Click()
    Dim i As Long, itm As Long
 
    For i = 0 To Me.ListBox2.ListCount - 1
        If Me.ListBox2.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()
    Dim rng1 As Range
 
    lrA = sh1.Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
    If lrA = 1 Then lrA = 2
    Set rng1 = sh1.Range("A2:J" & lrA)
 
    With ListBox2
      .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 ListBox1
      .ColumnCount = 10 'Set the column Amount
      .ColumnHeads = True
      .RowSource = rng2.Address(, , , 1) 'Fill the Listbox
    End With
End Sub
Click to expand...
That works thank hyou so much but how can i add the below code into my Userform_Initialize please?
VBA Code: 
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
 
Upvote 0
Try replacing the current 'UserForm_Initialize' sub with the below:
VBA Code: 
Private Sub UserForm_Initialize()
    Set sh1 = Sheets("Outages Data")
    Set sh2 = Sheets("Additional Job")
 
    ws1Rng
    ws2Rng
    
    UserForm1.TextBox2.Value = Application.WorksheetFunction.CountIf(ThisWorkbook.Sheets("In Day VL").Range("A:A"), "*CAG*")
    UserForm1.TextBox3.Value = Application.WorksheetFunction.CountIf(ThisWorkbook.Sheets("In Day VL").Range("A:A"), "*CC*")
    UserForm1.TextBox4.Value = Application.WorksheetFunction.CountIf(ThisWorkbook.Sheets("In Day VL").Range("A:A"), "*Additional Job*")
    UserForm1.TextBox5.Value = Application.WorksheetFunction.CountIf(ThisWorkbook.Sheets("In Day VL").Range("A:A"), "*Sickness*")
    UserForm1.TextBox6.Value = Application.WorksheetFunction.CountIf(ThisWorkbook.Sheets("In Day VL").Range("A:A"), "*Stores*")
End Sub
 
Upvote 0
Georgiboy said:
Try replacing the current 'UserForm_Initialize' sub with the below:
VBA Code: 
Private Sub UserForm_Initialize()
    Set sh1 = Sheets("Outages Data")
    Set sh2 = Sheets("Additional Job")
 
    ws1Rng
    ws2Rng
   
    UserForm1.TextBox2.Value = Application.WorksheetFunction.CountIf(ThisWorkbook.Sheets("In Day VL").Range("A:A"), "*CAG*")
    UserForm1.TextBox3.Value = Application.WorksheetFunction.CountIf(ThisWorkbook.Sheets("In Day VL").Range("A:A"), "*CC*")
    UserForm1.TextBox4.Value = Application.WorksheetFunction.CountIf(ThisWorkbook.Sheets("In Day VL").Range("A:A"), "*Additional Job*")
    UserForm1.TextBox5.Value = Application.WorksheetFunction.CountIf(ThisWorkbook.Sheets("In Day VL").Range("A:A"), "*Sickness*")
    UserForm1.TextBox6.Value = Application.WorksheetFunction.CountIf(ThisWorkbook.Sheets("In Day VL").Range("A:A"), "*Stores*")
End Sub
Click to expand...

HIya when i click on a row 2nd or 2rd down in the listbox for example i get an error - Run-time error '-2147024882' could not set the rowsource property. Hope you can advise please?
 
Upvote 0
I mentioned in post 14 that I also get that error but I am looking into a fix, I dont understand currently what is causing it. It seems to be something to do with the log files. I will revert when i find a fix, or maybe someone else here will understand why the error is there?
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question
Get data which is present in the column range of sheet4 but not present in listbox (listbox1) in listbox2
Replies
1
Views
472
DanteAmor
DanteAmor
P
  • Question
Data not copying fully into ListBox2
Replies
0
Views
116
Patriot2879
P
P
  • Solved
Listbox2 value send email
Replies
9
Views
260
Domenic
D
J
  • Question
How to delete row on two worksheets
Replies
2
Views
316
jdcar
J
N
  • Solved
Userform populated w/ word information
Replies
1
Views
350
NdNoviceHlp
NdNoviceHlp

Forum statistics

Threads
1,215,083
Messages
6,123,020
Members
449,092
Latest member
ikke

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