Deleting cell data automatically at midnight

[djdale808]

Aloha all!! Thank you for reading.

I have a form on our company server. It's for people to select their break and lunch hours. Everything on the form is protected except for the areas they can fill in.
At the end of the day, say midnight, I'd like the form to clear all contents inputted throughout the day. I am super new to Excel and I haven't learned what VBA and Macros are.
Any help would be truly appreciated!!

Mahalo!!

 

[SQUIDD]

Re: Pls HELP deleting cell data automatically at midnight. Thank you in advance!

hi

welcome
put this into you workbook module

Private Sub Workbook_Activate()
Application.OnTime TimeValue("23:59:59"), "remove_data"
End Sub

and this in a standard module and change to suit the data you want to clear.

Sub remove_data()
Range("a1:b100").ClearContents
End Sub

let me know if you need any more help doing this, or help in what you want to clear.
the code has not specified what sheet it is to clear for example, so it will cleare the active sheet a1:b100

when you open your workbook up, the 1st code starts and waits until midnight at whick point the second code will run and clear your data.

Dave

 

[djdale808]

Re: Pls HELP deleting cell data automatically at midnight. Thank you in advance!

Hey Dave!

Thank you so much for your reply. Sorry I took so long to respond. I gave it a shot but was not able to get it to clear at midnight. Did the form have to be opened for it to run, or would it have worked even with the form closed? I tried playing the macro and it did exactly what it was supposed to. I probably did something wrong lol. I'll try again and report back to see what I was doing wrong. Your help on this is really appreciated!

 

[SQUIDD]

Re: Pls HELP deleting cell data automatically at midnight. Thank you in advance!

Hi

yes, for it to run the workbook has to be open.

The 1st code sets in motion the clock essentially and waits for midnight.

Try testing it again with the workbook open.
Change the time so you can see it run for a couple of mins in the future.


1 think to note though. When you add the code you need to 1st save the workbook with the code in. Then reopen it.

As as the code starts when the workbook activates.

Make sense?

dave