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
 
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

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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

C
  • Question Question
Searching using TextBox and ListBox
Replies
1
Views
231
campbell1093
C
I
  • Question Question
Select Listbox value to be then taken to customer on worksheet
Replies
1
Views
104
ipbr21054
I
GomaPile
  • Question Question
Listbox1 Error
Replies
4
Views
257
GomaPile
GomaPile
K
  • Question Question
I was try to load data To List Box And Transfer data to Sheet2.... (Not work this userform)
Replies
4
Views
192
krunal123
K
D
  • Question Question
why show the frame of userform as the photo1
Replies
0
Views
202
david2005wang
D

Forum statistics

Threads
1,223,903
Messages
6,175,286
Members
452,631
Latest member
a_potato

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