How to know if a worksheet was unlocked
in [Functions]
|
From: Mathieu936 on 7 May 2010 11:53 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!
From: Gord Dibben on 7 May 2010 17:14 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 <mathieu936(a)gmail.com> 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!
From: Roger Govier on 8 May 2010 06:31 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!
From: Mathieu936 on 14 May 2010 14:49 Wow, good idea! you guys are awesome! Thanks!
From: Mathieu936 on 1 Jun 2010 13:00
Guys, I have a concern with this function... UNDO does not work anymore with this Worksheet_Calculate sub :( any idea? |