I have created a form that will take several fields of data and send it to another sheet to store. I created a Submit button that is working perfectly transferring the data from the form to the next line in my Data sheet, but once the data is transferred, the form does not clear out and its very easy to hit the Submit button a second time thinking it did not go through (sending the data to the other sheet twice).
I'm trying to build in a "Clear Form" function into the Submit button so once the data gets transferred, the form clears out automatically so 1) you know it submitted properly and 2) gets the form ready for the next submission. So far, here is what my VBA looks like:
Sub data_input()
ws_output = "Data"
next_row = Sheets(ws_output).Range("A" & Rows.Count).End(xlUp).Offset(1).Row
Sheets(ws_output).Cells(next_row, 1).Value = Range("Unit").Value
Sheets(ws_output).Cells(next_row, 2).Value = Range("DateSched").Value
Sheets(ws_output).Cells(next_row, 3).Value = Range("DateRes").Value
Sheets(ws_output).Cells(next_row, 4).Value = Range("Vendor").Value
Sheets(ws_output).Cells(next_row, 5).Value = Range("Category").Value
Sheets(ws_output).Cells(next_row, 6).Value = Range("VendorAff").Value
Sheets(ws_output).Cells(next_row, 7).Value = Range("Status").Value
Sheets(ws_output).Cells(next_row, 8).Value = Range("Notes").Value
Sheets(ws_output).Cells(next_row, 9).Value = Range("SubmittedBy").Value
End Sub
Does anyone know how I can add in this "Clear Form" function once data has been transferred? I'm stumped.
I'm trying to build in a "Clear Form" function into the Submit button so once the data gets transferred, the form clears out automatically so 1) you know it submitted properly and 2) gets the form ready for the next submission. So far, here is what my VBA looks like:
Sub data_input()
ws_output = "Data"
next_row = Sheets(ws_output).Range("A" & Rows.Count).End(xlUp).Offset(1).Row
Sheets(ws_output).Cells(next_row, 1).Value = Range("Unit").Value
Sheets(ws_output).Cells(next_row, 2).Value = Range("DateSched").Value
Sheets(ws_output).Cells(next_row, 3).Value = Range("DateRes").Value
Sheets(ws_output).Cells(next_row, 4).Value = Range("Vendor").Value
Sheets(ws_output).Cells(next_row, 5).Value = Range("Category").Value
Sheets(ws_output).Cells(next_row, 6).Value = Range("VendorAff").Value
Sheets(ws_output).Cells(next_row, 7).Value = Range("Status").Value
Sheets(ws_output).Cells(next_row, 8).Value = Range("Notes").Value
Sheets(ws_output).Cells(next_row, 9).Value = Range("SubmittedBy").Value
End Sub
Does anyone know how I can add in this "Clear Form" function once data has been transferred? I'm stumped.