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!

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.

On 3/22/2010 2:18 PM, Mathieu936 wrote:
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

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!

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


On Fri, 7 May 2010 08:53:22 -0700 (PDT), Mathieu936
wrote:

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!

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.
--
Regards
Roger Govier

Mathieu936 wrote:
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!

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

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

UNDO does not work anymore with this Worksheet_Calculate sub :(

any idea?

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



Mathieu936 wrote:

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

UNDO does not work anymore with this Worksheet_Calculate sub :(

any idea?

--

Dave Peterson