MS Excel macro: Check file validity

MS Excel macro: Check file validity

Didn't find your answer?

Is it possible to write a Macro that can tell me if an MS Excel workbook or worksheet that I have protected has been unprotected before been sent back to me.

Alan Ryder

Replies (11)

Please login or register to join the discussion.

avatar
By edhy
13th Aug 2008 14:55

Use IF in Worksheet Change event
Though not foolproof, In a worksheet change event macro use a conditional IF to chek the staus of protection against your stored value. If the staus changes, execute your code (say get the user info) and also toggle stored value of protection status.

Protection status variable should be global and for first time setting it use sheet Worksheet_Activate event.

Hope this will give some assurance.

Regards

Zubair Edhy

Thanks (0)
avatar
By Chris Martin
17th Jun 2008 17:20

Create a log file
You could use the work_sheetchange event to trigger writing to a separate log file.
The following creates a unique log file based on the user name and records the time, workbook name and path, whether or not the sheet is protected (True / False) at the time of the change, the name of the sheet, the address of the changed cell and contents after the change. If the path is omitted it'll save to the default Excel file location (I think). This is works in the background so is 'invisible' to the user unless they get to the VBA . Each change is appended to the existing file.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Open "filepath" & Application.UserName & ".log" For Append As #1
Write #1, Now, _
ActiveWorkbook.Name, ActiveWorkbook.Path, _
Sh.ProtectContents, Sh.Name, _
Target.Address, Target.Formula
Close #1
End Sub

Thanks (0)
avatar
By User deleted
17th Jun 2008 09:20

Aside
Yes!

Thank you Alastair.

Thanks (0)
avatar
By listerramjet
17th Jun 2008 09:08

events
Spiney, the event tracking is active when the worksheet is active - whether changes persist beyond a file save and reopen would depend on what actions the code takes when an event is trigerred. I have used a scheme where if a cell changes I put it back to what it was (original contents hard coded in the vba). The trick to beware is the recursive nature of this - but you can temporarily suspend event tracking during run time.

does this answer your aside?

Thanks (0)
avatar
By User deleted
16th Jun 2008 15:55

A slight aside
Alastair, I like your change event solution but will this survive a file save and reopen?

I was under the impression that an object (re)activation cancelled change events. Just asking because I've never seen it used like this. Thanks.

Thanks (0)
avatar
By listerramjet
16th Jun 2008 14:25

vba I'm afraid
if you are into VBA then a solution to this problem is to use the worksheet change event.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("a4") Then
MsgBox "tut tut"
End If
End Sub

this code will display the message tut tut if cell a4 is changed. You would have to extend the checking to all cells you want to monitor, and you have then 2 choices. One is to capture a log of the fact that a monitored cell has been changed. Two is to change the cell back, so that any change is ineffective.

As an alternative, you could put all of your critical cells into a worksheet that has as its property sheet very hidden. This means that the user cannot see the sheet, and the worksheet menu format sheet unhide is ineffective.

However, beware that Excel is relatively unsecure, and so I would agree with Richard that the best approach is to use the worksheet to capture data, and process it in your own workbook, which users cannot see.

Thanks (0)
avatar
By User deleted
16th Jun 2008 09:37

Check file validity
Although it is difficult to check this using the protection mechanism, you can quite simply check to see if the content has been changed:

If 'Book1' was your original workbook
'Book2' is the version returned to you
You could set up a Book3 containing a formula such as

=IF([Book1]Sheet1!A1=[Book2]Sheet1!A1,"","ERROR")

in cell A1

Which compares cell A1 in each workbook and reports ERROR if this cell has been changed.

Copy this to cover all cells that need checking (Remember to remove any '$' signs Excel may introduce or the cell references will not change when copying).

You can easily convert this to a macro if you wish but a global find/replace is probably easier for changing the workbook and sheet references.

Thanks (0)
avatar
By User deleted
31st May 2008 11:04

Event log ...
You might want to have a look at this - I haven't checked it out but it may give something of a partial solution

http://www.tanguay.info/web/codeExample.php?id=875

Thanks (0)
avatar
By AnonymousUser
30th May 2008 17:11

Many thanks!
Richard,

Many thanks for your help. It’s a pity that there is no hierarchically log of protected/unprotected events in MS Excel.

Your suggestion on data input only worksheets is welcomed. A little more work, but at least the processed data could be relied upon.

I’m not sure if this is really useful, but for anyone interested, you can get VBA code to check if a workbook has any protected worksheets at the following website:

http://www.vbaexpress.com/kb/getarticle.php?kb_id=551

All the best,

Alan

Thanks (0)
avatar
By RichardSchollar
30th May 2008 10:44

Yes
Hi Alan

It's certainly possible, but let me ask why you would want to do this - is it so that you can then make amendments to the file, or is it to validate that the file has actually been changed by whoever you sent the wb to?

Richard

Thanks (0)
avatar
By AnonymousUser
30th May 2008 12:58

Why I want to do this
Hi Richard,

I have a worksheet with that asks for input into yellow data entry cells. I have formula and formats that are in white cells. I have asked that white cells should never be deleted or changed and I password protected the worksheet so that no entry could be make to white cells.

Recently, one colleague broke the password protection and amended the worksheet. I want to be able to track if anyone in the future breaks a password protected worksheet, and either re-protects the workbook or leaves it unprotected before submitting the workbook back to me. I receive back a number of these workbooks and have been linkup to an overall consolidation model.

The fact that a password protect worksheet was amended is worrying from a data validity point-of-view. I have a macro that checks if a worksheet is unprotected but this does not really solve my problem, as I would like a list of the people who protect / unprotect this worksheet.

Thanks (0)