Private Sub CommandButton11_Click()
Load UserForm5
If TextBox1.Value = "" Or ComboBox1.Value = "" Then
MsgBox "Incomplete Data", vbCritical, ""
Exit Sub
End If
UserForm5.TextBox1 = UserForm4.TextBox1.Value
UserForm5.TextBox2 = UserForm4.ComboBox1.Value
UserForm5.Show
End Sub
Note: When CommandButton11 is clicked (above - userform4), it opens below userform5. Userform5 textbox1 and textbox2 then retrieves data from sheet1 as shown in userform4 above.
Private Sub CommandButton3_Click()
Dim emptyRow As Long
'Make Sheet 2 active
Sheets("Sheet2").Activate
'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("A:G")) + 1
'Transfer information from userform fields to Sheet 2
Cells(emptyRow, 2).Value = TextBox1.Value
Cells(emptyRow, 3).Value = TextBox2.Value
Cells(emptyRow, 5).Value = TextBox3.Value
Cells(emptyRow, 7).Value = TextBox4.Value
Cells(emptyRow, 8).Value = TextBox5.Value
End Sub
When Userform5 Commandbutton3 is clicked, I want the rows selected in sheet1 (i.e. based on the value from textbox1 which is pulled from sheet1) to be cut and pasted into sheet2
Hello All,
Hoping someone could help me out on this one. As the code shows above, I can get the values in the userform textbox to be added to sheet 2. The userform retrieves some of the records from sheet 1. What I am trying to achieve is when that value from sheet 1 is retrieved, I want to cut it from sheet 1 into sheet 2. What is the best way to do this?
Thank you
Regards, Kevin
It isn't the only way, but try an array. For more info see VBA Arrays And Worksheet Ranges.
Dim rngOldRow, rngNewRow, arrValuesInRow
Set rngOldRow = Worksheets(1).Range("A10:N10") 'Defines the old row.
Set rngNewRow = Worksheets(2).Range("A5:N5") 'Defines the new row.
arrValuesInRow = rngOldRow 'Copies values to an array variable.
arrValuesInRow(1, 2) = TextBox1.Value 'Changes values in the array.
arrValuesInRow(1, 3) = TextBox2.Value
arrValuesInRow(1, 5) = TextBox3.Value
arrValuesInRow(1, 7) = TextBox4.Value
arrValuesInRow(1, 8) = TextBox5.Value
rngNewRow.Value = arrValuesInRow 'Copies from the array to Sheet 2.
rngOldRow.Delete Shift:=xlUp 'Deletes the row on Sheet 1.