Hello, I am still very new to VBA and am working on a project where I have created a user form to send data input by user to a “Sheet3”. Now I want the same user form to send the data input to BOTH “Sheet3” and “Sheet7”. Currently, my user form can send data to “Sheet3” just fine.
In “Sheet7”, I have several columns: “Date”, “Code”, “Serial No.”, “Repair Date”, and “Status”
I want the user form’s Date, Code, Serial No. textboxes and OptionButton8 to also send the data to Sheet7 based on the columns respective name.
Here is my code(from userform):
Private Sub CommandButton1_Click()
'code to send data from service records userform to service record excel sheet
Dim newRow As Long
'activate service records excel sheet
Sheet3.Activate
'determine newRow
newRow = WorksheetFunction.CountA(Range("A:A")) + 1
'code to transfer data from userform listboxes, textboxes etc to excel sheet
'code to transfer data from textbox1 to "DATE" column
Cells(newRow, 1).Value = TextBox1.Value
'code to transfer data from textbox2 to "CODE" column
Cells(newRow, 3).Value = TextBox2.Value
'code to transfer data from textbox3 to "SERIAL NO" column
Cells(newRow, 4).Value = TextBox3.Value
If OptionButton8.Value = True Then
Cells(newRow, 10).Value = "Pending"
Else
End If
End sub
Then, I would like to program using VBA, the “Repair Date” column in such that when user keys in the date, the “Status” column will change from “Pending” to “Repaired” (assuming the “Status” column is already in “Pending”)
Any help would be greatly appreciated. Thank you.
In “Sheet7”, I have several columns: “Date”, “Code”, “Serial No.”, “Repair Date”, and “Status”
I want the user form’s Date, Code, Serial No. textboxes and OptionButton8 to also send the data to Sheet7 based on the columns respective name.
Here is my code(from userform):
Private Sub CommandButton1_Click()
'code to send data from service records userform to service record excel sheet
Dim newRow As Long
'activate service records excel sheet
Sheet3.Activate
'determine newRow
newRow = WorksheetFunction.CountA(Range("A:A")) + 1
'code to transfer data from userform listboxes, textboxes etc to excel sheet
'code to transfer data from textbox1 to "DATE" column
Cells(newRow, 1).Value = TextBox1.Value
'code to transfer data from textbox2 to "CODE" column
Cells(newRow, 3).Value = TextBox2.Value
'code to transfer data from textbox3 to "SERIAL NO" column
Cells(newRow, 4).Value = TextBox3.Value
If OptionButton8.Value = True Then
Cells(newRow, 10).Value = "Pending"
Else
End If
End sub
Then, I would like to program using VBA, the “Repair Date” column in such that when user keys in the date, the “Status” column will change from “Pending” to “Repaired” (assuming the “Status” column is already in “Pending”)
Any help would be greatly appreciated. Thank you.