Input Form to Get Data and Store it in Another Tab in Excel


Hello everyone!

So I am using an excel file where with a macro assigned to a certain "Submit" button. By pressing that button, different data from sheet 1 is sent to sheet 2.

My question is, how or what can i change to the code (or anywhere else ) , in order that after every hit of the "Submit" button, the informations are sent to sheet 2 but i also want that the informations from sheet 1 to get deleted automatically.

Otherwise I see the risk that people will over-use the "Submit" button when it's not the case :).

thank you in advance



Does the user first input the data into sheet 1 then click Submit (so their input is sent to sheet 2)?

It sounds like a simple matter of using the Range.Clear method (or similar) for the data range you transfer to sheet 2.

To be more certain, please edit your Question to clarify and if possible attach a sample spreadsheet for us to try. 
John_Ru (rep: 1557) Dec 1, '20 at 4:57 pm
question #1 ) Yes you are right. data is sent from sheet1 to sheet2 automatically, after submit button is hit. 

I have added the spreadsheet on my initial post.
razvansd (rep: 2) Dec 2, '20 at 11:29 am
Thanks. I see Don and Variatus have done some work on this already so will leave them to respond 
John_Ru (rep: 1557) Dec 2, '20 at 11:51 am
Add to Discussion


Selected Answer

In the macro, after the lines that transfer the values to the other sheet, put something like this for each field that you want to clear:


Change Input to the name of the sheet with the input form and F3 to the range address of the field to clear. Just put this line as many times as you need to clear all of the fields.



This worked ! Thank you very much ! :)
razvansd (rep: 2) Dec 2, '20 at 12:08 pm
Add to Discussion

It's not likely to be a good idea to delete anything. Instead, ceate a way by which you can tell if an entry has already been made. In the case of a salary that would be the month, or perhaps a date. Then, when the user presses the Submit button the code looks for that entry and creates a new one only if it isn't found. If the user presses Submit repeatedly you get the choice of either over-writing the existing or refusing to make any entry or perhaps giving a message and ask the user for a decision.

This choice is a matter of system design. If you do allow over-write the user could make changes to the original data and thereby change the previous entry. Either way, both records would stay synched.



Same entries for sure will be created multiple times per month ( ok, the date will differ).
At this moment the over-writting is not happening. After the data are introduced in sheet1 -> the submit button is hit -> data transffered to sheet2. But if i hit the submit button 4,5 times => the same data will be transffered 4,5 times to sheet2. And this is what i don't want.
I want the data from sheet1 to get automatically deleted after the submit button is hit.
razvansd (rep: 2) Dec 2, '20 at 11:25 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login