How to know if a worksheet was unlocked

[Mathieu936]

Hi!

I have a worksheet containing many formulas. This worksheet is locked
without any password.

Is there a way to see if somebody unlocked the worksheet to change
formulas?

I want the user can unlock and lock back the worksheet, but I need a
way to know it.

Thanks!

 

[Héctor Miguel]

hi, Mathieu !

I have a worksheet containing many formulas. This worksheet is locked without any password.
Is there a way to see if somebody unlocked the worksheet to change formulas?
I want the user can unlock and lock back the worksheet, but I need a way to know it.

ActiveSheet.ProtectContents returns true/false according actual protection of active sheet (i.e.)

MsgBox "Active sheet is " & IIf(ActiveSheet.ProtectContents, "", "UN-") & "Protected"

question is: how/when/where/... do you plan to lock-back when the case is ?

hth,
hector.

 

[Billns]

Hi!

I have a worksheet containing many formulas. This worksheet is locked
without any password.

Is there a way to see if somebody unlocked the worksheet to change
formulas?

I want the user can unlock and lock back the worksheet, but I need a
way to know it.

Thanks!

I suppose you could ask the user...

You must trust him or her if you allow unlocking.

Bill

 

[Mathieu936]

Yes I could ask the users, but I doubt I'll have always the truth!

I know how to automatically lock and unlock the sheet via coding, but
I want to trace if the sheet was unlocked via the menus.

I did an excel tool with formulas that I know, but these formulas
could be changed if needed by the users, but I need to know if the
formulas were changed.

Thanks guys!

 

[Gord Dibben]

As far as I know there is no Lock or UnLock event.

You could use event code to track the address of any formula cell that was
changed.

Create a worksheet named "Logsheet"(no quotes) and leave it hidden or
xlveryhidden

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRng As Range
Dim wksht As Worksheet
Set wksht = Sheets("Logsheet")
Set myRng = wksht.Cells(Rows.Count, "A").End(xlUp) _
.Offset(1, 0)
If Target.HasFormula Then
With myRng
.Value = Target.Address & " Changed"
.Offset(0, 1).Value = Format(Now, "mm-dd-yy hh:mm:ss")
End With
End If
End Sub


Gord Dibben MS Excel MVP

 

[Roger Govier]

Hi Mathieu

You could use the following pieces of event code

Private Sub Worksheet_Activate()
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=TrueProt = True
End Sub

Private Sub Worksheet_Calculate()
If ActiveSheet.EnableSelection = 1 Then
MsgBox "Sheet Unprotected"
End If
End Sub

The protection is set so the user cannot select a locked cell.
Then on calculate, the code checks whether this is still the protection
status of the sheet.

You could incorporate Gordon's log idea to insert in place of Msgbox, or
trigger anything else that you want to happen if you find that
protection has been removed.

 

[Mathieu936]

Wow, good idea! you guys are awesome! Thanks!

 

[Mathieu936]

Guys, I have a concern with this function...

UNDO does not work anymore with this Worksheet_Calculate sub

any idea?

 

[Dave Peterson]

Most macros that do anything will destroy the Redo/Undo stack.