Re-Transfer Row from Listbox to another sheet VBA

R.Soeta · Mar 21st 2020

Hello Sir, I have read and tried to apply a thread here according to the subject I wrote again above but I get a problem that appears 'could not find the specific object' and debug it in Me ("TextBox" & iX - 1). Value = .List (.ListIndex, iX - 1).

beg for your help, every help is very valuable to me

Justin Doward · Mar 21st 2020

this looks like it is part of a "with" instruction, really need to see the rest of the code to fix it.

it should be something like:

Code

With Me.ListBox1

Me.Controls("TextBox" & iX - 1).value = .List(.ListIndex, iX - 1)

End With

where listbox1 is your object of interest.

You will get a moderator upset if you do not wrap your code in code tags, don't forget to read the rules.

Cheers

Justin

R.Soeta · Mar 21st 2020

apologize profusely sir, I'm still new in Excel VBA issues.

I will present the rest of the code with your suggestion:

Code

Private Sub TABELPENDUDUK_Click ()
Dim iX As Integer


With Me.TABEL POPULATION
For iX = 1 to 39
If iX = 1 Then
txtRowNo.Value = .List (.ListIndex, iX - 1)
Else
Me.Controls ("TextBox" & iX - 1) .Value = .List (.ListIndex, iX - 1)
End If
Next iX
End with
End Sub

Display More

Justin Doward · Mar 21st 2020

TABEL POPULATION is not a legal name for a form object, you need to have a look on the form and change the name to the correct name. It should be referring to a listbox judging by the .list(.listindex - reference.

**royUK** · Mar 21st 2020

Please use Code Tags when posting your code to the Forum.

That looks like my coding, what exactly are you trying to do?

An example workbook would help

R.Soeta · Mar 21st 2020

sorry, basically my goal is to want to move some data that is in the list sourced from a sheet on another sheet with a combobox option that is in userform

**royUK** · Mar 21st 2020

You need to specify the sheet to write to.

As I said an example workbook would help

R.Soeta · Mar 21st 2020

I will attach the file

this file is the result of my modification for needs only without reducing my respect for the first file maker

**royUK** · Mar 21st 2020

There's no code in the UserForm

R.Soeta · Mar 21st 2020

I'm sorry sir, I sent the wrong file,

this is the attachment I was supposed to send.

the question is how to transfer data from cmbNSheet to NSheetKe

apologize and thank you in advance

Justin Doward · Mar 21st 2020

If you want to copy the data at the same time you fill the textboxes you could try:

Code

Private Sub TABELPENDUDUK_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

If Me.NSheetKe.Value = vbNullString Then
MsgBox "Select a copy to sheet first"
Exit Sub
End If

Dim EmptyRW, i As Long
Dim ws As Worksheet: Set ws = Sheets(Me.NSheetKe.Value)

'Perintah memasukkan data dari ListBox ke TextBox
Me.cmbNSheet.Value = ActiveSheet.Name
Me.txtNo.Value = Me.TABELPENDUDUK.Column(0)
Me.txtID.Value = Me.TABELPENDUDUK.Column(1)
Me.txtName.Value = Me.TABELPENDUDUK.Column(2)
Me.txtAddress.Value = Me.TABELPENDUDUK.Column(3)
Me.txtDOB.Value = Format(Me.TABELPENDUDUK.Column(4), "dd-mm-yyyy")

EmptyRW = Sheets(Me.NSheetKe.Value).Cells(1, 1).CurrentRegion.Rows.Count + 1

For i = 0 To 4

ws.Cells(EmptyRW, i + 1) = Me.TABELPENDUDUK.Column(i)

Next i

Exit Sub
End Sub

Display More

R.Soeta · Mar 21st 2020

thank you, justin, what if you want to delete the data from the previous sheet as well?

Justin Doward · Mar 22nd 2020

Try this:

Code

Private Sub TABELPENDUDUK_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Application.ScreenUpdating = False
If Me.NSheetKe.Value = vbNullString Then
MsgBox "Select a copy to sheet first"
Exit Sub
End If
Dim EmptyTRW, EmptyFRW, i As Long
Dim MyOldRNGE As Range

Dim ws As Worksheet: Set ws = Sheets(Me.NSheetKe.Value)
Dim ws1 As Worksheet: Set ws1 = Sheets(Me.cmbNSheet.Value)
'Perintah memasukkan data dari ListBox ke TextBox
Me.cmbNSheet.Value = ActiveSheet.Name
Me.txtNo.Value = Me.TABELPENDUDUK.Column(0)
Me.txtID.Value = Me.TABELPENDUDUK.Column(1)
Me.txtName.Value = Me.TABELPENDUDUK.Column(2)
Me.txtAddress.Value = Me.TABELPENDUDUK.Column(3)
Me.txtDOB.Value = Format(Me.TABELPENDUDUK.Column(4), "dd-mm-yyyy")
EmptyTRW = ws.Cells(1, 1).CurrentRegion.Rows.Count + 1 ' identify blank row to insert cells on the "copy to" sheet
EmptyFRW = ws1.Cells(1, 1).CurrentRegion.Rows.Count     'identify final row in "copy from" sheet
Set MyOldRNGE = ws1.Range(ws1.Cells(2, 2), ws1.Cells(EmptyFRW, 2))
mydmtch = Application.Match(Me.TABELPENDUDUK.Column(1), MyOldRNGE)

For i = 0 To 4
ws.Cells(EmptyTRW, i + 1) = Me.TABELPENDUDUK.Column(i)
Next i

ws1.Rows(mydmtch + 1).EntireRow.Delete

Me.TABELPENDUDUK.RowSource = "A2:E" & Range("E" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = True

End Sub

Display More

R.Soeta · Mar 22nd 2020

Thank you very much, Mr. Justin

This code works fine it's just that the ID has changed, how to handle it?

and for sequence numbers to adjust according to list order?

sorry for too many requests sir

Justin Doward · Mar 22nd 2020

Hi RI,

I made a couple of changes, there were a couple of errors popping up as I had not set the application.match function only return exact matches, I have added a line to format the column with the ID and the first column now copies a formula to track the row rather than copying the value.

Code

Private Sub TABELPENDUDUK_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

Application.ScreenUpdating = False
If Me.NSheetKe.Value = vbNullString Then
MsgBox "Select a copy-to sheet first"
Exit Sub
End If
Dim EmptyTRW, EmptyFRW, i As Long
Dim MyOldRNGE As Range
Dim ws As Worksheet: Set ws = Sheets(Me.NSheetKe.Value)
Dim ws1 As Worksheet: Set ws1 = Sheets(Me.cmbNSheet.Value)

'Perintah memasukkan data dari ListBox ke TextBox

Me.cmbNSheet.Value = ActiveSheet.Name
Me.txtNo.Value = Me.TABELPENDUDUK.Column(0)
Me.txtID.Value = Me.TABELPENDUDUK.Column(1)
Me.txtName.Value = Me.TABELPENDUDUK.Column(2)
Me.txtAddress.Value = Me.TABELPENDUDUK.Column(3)
Me.txtDOB.Value = Format(Me.TABELPENDUDUK.Column(4), "dd-mm-yyyy")
EmptyTRW = ws.Cells(1, 1).CurrentRegion.Rows.Count + 1 ' identify blank row to insert cells on the "copy to" sheet
EmptyFRW = ws1.Cells(1, 1).CurrentRegion.Rows.Count 'identify final row in "copy from" sheet
Set MyOldRNGE = ws1.Range(ws1.Cells(2, 2), ws1.Cells(EmptyFRW, 2))
mydmtch = Application.Match(Me.TABELPENDUDUK.Column(1), MyOldRNGE, 0)
For i = 0 To 4
    If i = 0 Then
    ws.Cells(EmptyTRW, i + 1) = "=row() - 1"
    Else
    ws.Cells(EmptyTRW, i + 1) = Me.TABELPENDUDUK.Column(i)
    End If
Next i
ws1.Rows(mydmtch + 1).EntireRow.Delete
Me.TABELPENDUDUK.RowSource = "A2:E" & Range("E" & Rows.Count).End(xlUp).Row
ws.Columns(2).NumberFormat = "0"
Application.ScreenUpdating = True

End Sub

Display More

R.Soeta · Mar 22nd 2020

Thank you, Mr. Justin, for your help so far. if you don't mind, please also make coding for multi-criteria (by ID or by Name) data search, sir

Justin Doward · Mar 23rd 2020

Transfer Data from Form final.xlsm

Try this one, search in textbox6.

**royUK** · Mar 23rd 2020

My question would be - why do you need to move data? Data should be stored on one sheet

R.Soeta · Mar 23rd 2020

Cool, Mr. Justin, this is perfect.

honestly I am very proud to be joining this group and very grateful to get an answer to my problem.

Once again, thank you very much, Mr. Justin.

Mr. RO, I apologize in advance, I work at the Village Government agency. The data I sent was a sample from the Population Master Book, sir.

R.Soeta · Mar 23rd 2020

Mr. Justin may I ask again?

for setting the data transfer between sheets can I move the macro script to CommandButton1 with the format Private Sub CommandButton1_Click ()?

Re-Transfer Row from Listbox to another sheet VBA

Participate now!

Share