Protect Sheet After Certain Time

[Jay Baker]

Hi all,
I run a bakery. We input our order forms into excel (XL03,WINXP) and generate a Total To Bake each day. I'm looking for a way to protect, lock, or save the original order sheets after the total is generated so that one of my "smart" secretaries, or someone else, doesn't go back and change (reduce) the numbers and "borrow" some extra cash for the weekend!
The problem is that the same workbook is used the next day (sometimes 2 days later for holidays) to enter returned bread and cost of labor data. Is there a way to expire, or time the protection of only those sheets (original orders) that shouldn't need to be touched again??

Thanks in advance for any help given...
Jay Baker

 

[Zoot]

Jay

Welcome to the board.

One way to do this would be to use the Aplication.ontime event. This in effect times a procedure to run. I'm not sure of your experience of macros/VBA etc but try this:

Right click on the excel icon (usually top left of the screen next to "File") and select View Code. The Visual Basic Editor screen will appear.

Copy and paste the following code into the window:

Private Sub Workbook_Open()
Application.OnTime Now + TimeValue("00:00:05"), "Protectsheets"
End Sub

This will run the procedure called "Protectsheets" 5 seconds after the workbook opens. You can amend this timing between brackets in the code.

Alternatively if you want the protection to run at a specified time then change the Application ontime line to:

Application.OnTime TimeValue("17:00:00"), "Protectsheets"

which will run the procedure at 5pm.

Then in the VB Editor click Insert and then Module. Copy and paste the following code into the empty screen:

Sub Protectsheets()
   Sheets("Sheet1").Protect
   Sheets("Sheet3").Protect
End Sub

This is the protectsheets event. You can edit this - put the actual names of the sheets you wish to protect in the quotation marks. You can add or remove other lines as necessary.

Not sure if that is what you are after. Post back if you need more help.

Regards

Zoot

 

[Jay Baker]

Hi Zoot,
Thank you for the help. I inputted the code in vba and it works well! Is there any way to add password protection for the sheets?

 

[Zoot]

Jay

Yep - Just use this:

Sub Protectsheets()
   Sheets("Sheet1").Protect Password = "Password"
   Sheets("Sheet3").Protect Password = "Password"
End Sub

Edit: Ooops - didn't test it! Got the Syntax wrong. Should be:

Sub Protectsheets()
   Sheets("Sheet1").Protect Password:="Password"
   Sheets("Sheet3").Protect Password:="Password"
End Sub

And then set the "Password" to whatever you want.

Regards

Zoot

 

[taregt]

Hi, I tried this, but it doesnt work. Where am i supposed to put in, under Sheet or under Module:


Private Sub Workbook_Open()Application.OnTime Now + TimeValue("00:00:05"), "Protectsheets"End Sub</PRE>