I've checked all your code and tidied it up. The code will now copy the selected row to the selected sheet, remove the original data and re-populate the ListBox. All the TextBoxes are re-set as well.
I have removed all unnecessary code, change the use of RowSource to List, which is the best to use for this ListBox.
Code
Option Explicit
Dim rData As Range
Private Sub cmbNSheet_Change()
With Sheets(Me.cmbNSheet.Value)
Set rData = .Range("A1").CurrentRegion.Offset(1)
Set rData = rData.Offset(1).Resize(rData.Rows.Count - 1, rData.Columns.Count)
Me.TABELPENDUDUK.List = rData.Value
End With
End Sub
Private Sub CommandButton1_Click()
Dim oCtl As MSForms.Control
Dim lRw As Long ''/// the row to copy to
''/// check a sheet to copy to is selected
If Me.NSheetKe.ListIndex = -1 Then
MsgBox "Please select a sheet to transfer to"
Me.NSheetKe.SetFocus
Exit Sub
End If
''/// copy to selected sheet using TextBoxes
With Sheets(Me.NSheetKe.Value)
lRw = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
.Cells(lRw, 1).Resize(, 5).Value = Array(Me.txtNo, Me.txtID, Me.txtName, Me.txtAddress, Me.txtDOB)
End With
''/// clear the Listbox
Me.TABELPENDUDUK.Clear
''/// remove the the data from original sheet & re-populate the ListBox
With Sheets(Me.cmbNSheet.Value)
.Rows(CInt(Me.txtNo.Value) + 1).EntireRow.Delete
Set rData = .Range("A1").CurrentRegion.Offset(1)
Set rData = rData.Offset(1).Resize(rData.Rows.Count - 1, rData.Columns.Count)
''/// clear TextBoxes
For Each oCtl In Me.Controls
If TypeName(oCtl) = "TextBox" Then oCtl.Value = Empty
Next oCtl
''///re-load the data
Me.TABELPENDUDUK.List = rData.Value
End With
End Sub
Private Sub CommandButton2_Click()
Unload Me
End Sub
Private Sub TABELPENDUDUK_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
'Perintah memasukkan data dari ListBox ke TextBox
With Me
.cmbNSheet.Value = ActiveSheet.Name
txtNo.Value = TABELPENDUDUK.Column(0)
txtID.Value = TABELPENDUDUK.Column(1)
txtName.Value = TABELPENDUDUK.Column(2)
txtAddress.Value = TABELPENDUDUK.Column(3)
txtDOB.Value = Format(TABELPENDUDUK.Column(4), "dd-mm-yyyy")
End With
End Sub
Private Sub UserForm_Initialize()
Dim RIAN As Long
With Me
cmbNSheet.Clear
For RIAN = 1 To Sheets.Count
cmbNSheet.AddItem Sheets(RIAN).Name
Next
NSheetKe.List = cmbNSheet.List
cmbNSheet.Value = ActiveSheet.Name
End With
End Sub
Display More