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.
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,957
Range("Unit").Value = ""
Range("DateSched").Value = ""
etc
etc
 

frubbit

New Member
Joined
Dec 23, 2020
Messages
3
Office Version
  1. 2019
Platform
  1. Windows
Holy moly that worked!! So simple and yet I could not seem to get there on my own. Thank you!!
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,858
Office Version
  1. 2010
Platform
  1. Windows
@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:

frubbit

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

Watch MrExcel Video

Forum statistics

Threads
1,127,347
Messages
5,624,136
Members
416,012
Latest member
rockermom59

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
Top