protecting my hard earned work


Board Regular
May 30, 2002
So .... you folks have been a great help to me and I have put together a wkbk that contains the DB's which will produce all the data I need to run my business more efficiently.


Is there anything I need to know regarding 'protecting' this effort so nothing changes except the DB info? Just have some fear of a formula getting whacked or a directory path changing etc etc.



Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Firstly - ALWAYS save a back up - Excel files can corrupt themseleves at any time!

If you want to protect the worksheets so that no one can override formulas etc you need to protect the worksheet.

If you're sheet is protected and you then run macros that will enter data into this sheets then you MUST UNPROTECT the sheet in the Macro before putting data into it e.g.

Sheets("Sheet1").Unprotect Password:="Password"

Once you have run the macro that enters the data you can protect it again in code:

Sheets("Sheet1").Protect Password:="Password"

Any help or are you after something more specific?
Upvote 0
Yes this is helpful. My wkbk has three DB's, a handful of PT's and other sheets running formulas from/to the DB's. The wkbk is 'the hub' of my billing, payroll, and job costing. Customer invoices pull data with =GETPIVOTDATA .... , payroll is pulled with =SUMPRODUCT .... and then theres a VLOOKUP thrown in for good measure.

Once a week or once every other week I'll do an hour or so of data entry and the rest will take care of itself, for the most part.

I just want to make sure my 'hub' isn't wrecked some how. Having a regular back up is a good idea, although the =GETPIVOTDATA formulas would have to change each time I saved the latest update of the wkbk.

Is there any way to 'freeze' the formulas?

Could be much ado about nothing.


Upvote 0
There are many ways to Automagically save your workbook. And, the advice you recieved about Excel self corupting cannot be overstated. Make a copy now. Put it in a safe place.

Now on to Passwords. EXCEL Password protection is a Joke!.. It is easily bypassed. I use it here just to remind the users that they are in an area that they should not be typing in. I do not expect it to protect anything.

I also use the PW OFF PW On routine at the beginning and end of some routines that change data in protected cells.

Lastly, you may want to create an on close routine that checks today's date compares it to a string you saved with the date of the last back up you made and if X days have passed create a Copy in a folder of your choosing. That way if something terrible happens you can delete your corrupted file and use the latest back up. You can also set this up to save two, three or a zillion files and over write them when certain conditions are met.

Lets say you want a copy each week. But, you want to protect yourself if the file corruption occurs during your last SaveCopyAs routine. You could have a file saved as Copy1 And another saved as Copy2. your routine would check, before it saves, to see if copy one or two was the last saved and save the file as the other one. This way you would increase your chances of having a file that would work should the worst happen and you end up saving a corrupted file. And if all else failed, remember that backup you made, now would be a good time to get it out.

Yours in EXCELent Frustration


While this on close event is a bit complex you can do it by placing your changing values on a hidden sheet and referenceing them with your code. If or Select Case statement s would be used to evaluate the conditions and tell the routine to procedd to branch A, B C, etc.
Upvote 0

You have good sound advice there, i would like to add BACK UP as stated but i say back up in different locations if on network and also more than one copy OK boring but one day you thank us all.

Maybe thats why i and a great pal are developing a cool back up utility - im beta testing it, its awesome and does the trick soon to be given away FREE of cause.

BACK UP more the better.

For ideas and scripts check / search the site theres loads it well covered,


PS even my code to back up thats started all this yo might find!
Upvote 0
I know well the value of back ups and saving frequently. Your advice is not falling on deaf ears - my back up copy will be safely stowed tonight. Maybe on one of my nice little zip disks.

The ON CLOSE routine mentioned, with copy 1 and copy 2 sound mighty interesting, but as with the rest of my wkbk - assistance is required. I'm just too much a novice to know these things. I suppose I could figure out a manual regimin that would insulate me from having saved a corrupted file.

Other than data being 'wrong' (things not working as expected) - is there any other way to know if a file is corrupt?

Upvote 0
The number one indicator of a corrupt workbook is it WILL NOT OPEN.

The AutoSaveOnClose Routine follows.

Look Through the code to see what is happening. There are more efficient ways to do this but here you can see the various procedures and what is happening and why.

This goes in the WorkBook Module for the Workbook you want it to function from

On a WorkSheet somewhere in your WorkBook You will need to Name 2 Ranges.
One will be called "TheDate".
The other will be called "TheCopy"

You MUST also create a SubFolder in the same Folder that your workbook resides in, called Back-Ups for this to work. This is because the routine will look for the Back-Ups folder to save your files in. It will overwrite the existing saved file by the same name WITHOUT asking permission. In addition it will save the working file automatically. Ths is because the last thing the Routine does is update the Saved Date and Copy Numbers so that the next time it runs it can determine the LAST time you saved.


Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim SaveDate, SaveInterv, SaveCopy, FileSave, CopyCount
'On a WorkSheet somewhere in your WorkBook _
You will need to name 2 Ranges _
One will be called "TheDate" _
The other will be called "TheCopy"
SaveDate = Range("TheDate").Value ' When did we save it last
SaveCopy = Range("TheCopy").Value ' What copy was it
SaveInterv = Date - SaveDate 'How many days since last save
CopyCount = SaveCopy 'Set a counter to name the file _
without changing anything, YET
If SaveInterv >= 7 Then 'If more than 7 days get permission to save the copy

Select Case CopyCount ' Which copy was last saved _
This is te reverse of the Copy counter
Case 1
CopyCount = 2
Case 2
CopyCount = 1
End Select
'The following asks if you want to do the back up. _
You will need a SubFolder in the same Folder _
that the program resides called Back-Ups _
For this to work.
If MsgBox("Do you want to make a Back-Up" & Chr(13) & _
"It has been " & SaveInterv & " days since your last save" _
& Chr(13) & "Your Last save was on " & SaveDate _
& Chr(13) & "Your last save was Copy " & CopyCount, vbYesNo) = vbYes Then
'If you answer YES then set the Copy # to the oppisite of the current copy.
Select Case Range("TheCopy").Value
Case 1
Range("TheCopy").Value = 2
Case 2
Range("TheCopy").Value = 1
End Select

'Here we set the varible FileSave to the path _
we want our back up to goto
FileSave = (ActiveWorkbook.Path & "Back-UpsCopy" & SaveCopy & ".XLS")
'Then we save it
ActiveWorkbook.SaveCopyAs FileName:=FileSave
'Then we update the Saved Date to Today so the counter _
will count 7 days from the last save
Range("TheDate").Value = Date
'Here we save the actual Workbook
End If

End If
'Then the OnClose will quit.
End Sub


Yours in EXCELent Frustration


Teach a man to fish and he can feed himself.
Give a man a grenade and he can feed the whole village.
This message was edited by KniteMare on 2002-09-12 10:48
Upvote 0

Forum statistics

Latest member

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
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 "".
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