If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How to know if a worksheet was unlocked
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! |
#2
|
|||
|
|||
How to know if a worksheet was unlocked
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. |
#3
|
|||
|
|||
How to know if a worksheet was unlocked
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 |
#4
|
|||
|
|||
How to know if a worksheet was unlocked
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! |
#5
|
|||
|
|||
How to know if a worksheet was unlocked
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! |
#6
|
|||
|
|||
How to know if a worksheet was unlocked
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! |
#7
|
|||
|
|||
How to know if a worksheet was unlocked
Wow, good idea! you guys are awesome! Thanks!
|
#8
|
|||
|
|||
How to know if a worksheet was unlocked
Guys, I have a concern with this function...
UNDO does not work anymore with this Worksheet_Calculate sub any idea? |
#9
|
|||
|
|||
How to know if a worksheet was unlocked
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 |
Thread Tools | |
Display Modes | |
|
|