A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

How to know if a worksheet was unlocked



 
 
Thread Tools Display Modes
  #1  
Old March 22nd, 2010, 10:18 PM posted to microsoft.public.excel.worksheet.functions
Mathieu936
external usenet poster
 
Posts: 4
Default 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  
Old March 23rd, 2010, 07:27 AM posted to microsoft.public.excel.worksheet.functions
Héctor Miguel
external usenet poster
 
Posts: 298
Default 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  
Old March 23rd, 2010, 11:02 PM posted to microsoft.public.excel.worksheet.functions
Billns
external usenet poster
 
Posts: 88
Default 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  
Old May 7th, 2010, 04:53 PM posted to microsoft.public.excel.worksheet.functions
Mathieu936
external usenet poster
 
Posts: 4
Default 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  
Old May 7th, 2010, 10:14 PM posted to microsoft.public.excel.worksheet.functions
Gord Dibben
external usenet poster
 
Posts: 20,252
Default 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  
Old May 8th, 2010, 11:31 AM posted to microsoft.public.excel.worksheet.functions
Roger Govier[_8_]
external usenet poster
 
Posts: 338
Default 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  
Old May 14th, 2010, 07:49 PM posted to microsoft.public.excel.worksheet.functions
Mathieu936
external usenet poster
 
Posts: 4
Default How to know if a worksheet was unlocked

Wow, good idea! you guys are awesome! Thanks!
  #8  
Old June 1st, 2010, 06:00 PM posted to microsoft.public.excel.worksheet.functions
Mathieu936
external usenet poster
 
Posts: 4
Default 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  
Old June 1st, 2010, 07:30 PM posted to microsoft.public.excel.worksheet.functions
Dave Peterson
external usenet poster
 
Posts: 19,791
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:26 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.