Add a 'clear' function to my Submit button

frubbit

New Member
Joined
Dec 23, 2020
Messages
3
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Holy moly that worked!! So simple and yet I could not seem to get there on my own. Thank you!!
 
Upvote 0
@frubbit,

In case you are interested, because you used Defined Names, your code can be compacted somewhat. The following macro will make the same assignments that your code does and it will clear the values in the source cells...
VBA Code:
Sub data_input()
  Dim next_row As Long, ws_output As String
  ws_output = "Data"
  next_row = Sheets(ws_output).Range("A" & Rows.Count).End(xlUp).Offset(1).Row
  Intersect(Sheets(ws_output).Columns("A:I"), Sheets(ws_output).Rows(next_row)) = Array([Unit], [DateSched], [DateRes], [Vendor], [Category], [VendorAff], [Status], [Notes], [SubmittedBy])
  [Unit,DateSched,DateRes,Vendor,Category,VendorAff,Status,Notes,SubmittedBy] = ""
End Sub
Note: I always declare all of my variables, hence the Dim statement that I added at the beginning of the code.
 
Last edited:
Upvote 0
@frubbit,

In case you are interested, because you used Defined Names, your code can be compacted somewhat. The following macro will make the same assignments that your code does and it will clear the values in the source cells...
VBA Code:
Sub data_input()
  Dim next_row As Long, ws_output As String
  ws_output = "Data"
  next_row = Sheets(ws_output).Range("A" & Rows.Count).End(xlUp).Offset(1).Row
  Intersect(Sheets(ws_output).Columns("A:I"), Sheets(ws_output).Rows(next_row)) = Array([Unit], [DateSched], [DateRes], [Vendor], [Category], [VendorAff], [Status], [Notes], [SubmittedBy])
  [Unit,DateSched,DateRes,Vendor,Category,VendorAff,Status,Notes,SubmittedBy] = ""
End Sub
Note: I always declare all of my variables, hence the Dim statement that I added at the beginning of the code.
Wow. I think that may actually be a lot more efficient. Thank you so much for your help!!
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top