Is there a way to use VBA to automatically lock (protect) my sheet every 2
hours? Only certain employees are allowed to modify it, and they are
forgetting to lock it at the end of their shift. So far I have come up with
this:
Sub ProtectSheet()
ActiveSheet.Protect (password)
End Sub
Can anyone point me towards a source of info on how to do this at 2 hour
intervals?
Thanks in advance...
Josh,
Use the OnTime method of the Application object. See
www.cpearson.com/excel/ontime.htm for more details.
--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
"Josh" <no spam> wrote in message
news:[email protected]...
> Is there a way to use VBA to automatically lock (protect) my
> sheet every 2
> hours? Only certain employees are allowed to modify it, and
> they are
> forgetting to lock it at the end of their shift. So far I have
> come up with
> this:
>
> Sub ProtectSheet()
>
> ActiveSheet.Protect (password)
>
> End Sub
>
> Can anyone point me towards a source of info on how to do this
> at 2 hour
> intervals?
>
> Thanks in advance...
>
>
take a look at
http://cpearson.com/excel/ontime.html
Note that protection alone is fairly worthless if you're trying to keep
unauthorized people out (at least if they want to get in, and have the
gumption to find these groups):
http://www.mcgimpsey.com/excel/removepwords.html
In article <[email protected]>, "Josh" <no spam>
wrote:
> Is there a way to use VBA to automatically lock (protect) my sheet every 2
> hours? Only certain employees are allowed to modify it, and they are
> forgetting to lock it at the end of their shift. So far I have come up with
> this:
>
> Sub ProtectSheet()
>
> ActiveSheet.Protect (password)
>
> End Sub
>
> Can anyone point me towards a source of info on how to do this at 2 hour
> intervals?
Thanks
"JE McGimpsey" <[email protected]> wrote in message
news:[email protected]...
> take a look at
>
> http://cpearson.com/excel/ontime.html
>
>
> Note that protection alone is fairly worthless if you're trying to keep
> unauthorized people out (at least if they want to get in, and have the
> gumption to find these groups):
>
> http://www.mcgimpsey.com/excel/removepwords.html
There are no malicious attempts to get into the spreadsheet -- just people
messing with it that shouldn't be changing anything. They think they are
"helping"...
That's the level that ws protection is good for...
In article <[email protected]>, "Josh" <no spam>
wrote:
> There are no malicious attempts to get into the spreadsheet -- just people
> messing with it that shouldn't be changing anything. They think they are
> "helping"...
I will never argue with Chip Pearson or John McGimpsey. Good to see Chip
back again.
Is Frank Kabel on holidays?
Anyway Gosh in stead of "on time" you may want to consider to protect the
sheets before closing, before saving and "before opening"
Sub Seal_File()
For Each sheet In Sheets
On Error Resume Next
sheet.Protect ("spw")
Next
Application.StatusBar = ""
End Sub
Sub UNSEAL()
'you could give this macro a key combination ..............
ActiveWorkbook.Unprotect ("spw")
For Each sheet In Sheets
On Error Resume Next
sheet.Unprotect ("spw")
Next
Application.StatusBar = "NOT sealed"
End Sub
Note the application status bar commands
that way you will see at the bottom of the sheet whether the sheet is in the
unprotected mode.
Regards
Bill K
"JE McGimpsey" <[email protected]> wrote in message
news:[email protected]...
> That's the level that ws protection is good for...
>
>
> In article <[email protected]>, "Josh" <no spam>
> wrote:
>
>> There are no malicious attempts to get into the spreadsheet -- just
>> people
>> messing with it that shouldn't be changing anything. They think they are
>> "helping"...
"Bill Kuunders" <[email protected]> wrote in message
> Is Frank Kabel on holidays?
Tragically, Frank passed away a few weeks ago. He will be sorely
missed.
--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
"Bill Kuunders" <[email protected]> wrote in message
news:evvZ%[email protected]...
>I will never argue with Chip Pearson or John McGimpsey. Good to
>see Chip back again.
> Is Frank Kabel on holidays?
> Anyway Gosh in stead of "on time" you may want to consider to
> protect the sheets before closing, before saving and "before
> opening"
>
> Sub Seal_File()
>
> For Each sheet In Sheets
> On Error Resume Next
> sheet.Protect ("spw")
> Next
> Application.StatusBar = ""
> End Sub
>
> Sub UNSEAL()
> 'you could give this macro a key combination ..............
> ActiveWorkbook.Unprotect ("spw")
> For Each sheet In Sheets
> On Error Resume Next
> sheet.Unprotect ("spw")
> Next
> Application.StatusBar = "NOT sealed"
> End Sub
>
> Note the application status bar commands
> that way you will see at the bottom of the sheet whether the
> sheet is in the unprotected mode.
>
> Regards
> Bill K
>
> "JE McGimpsey" <[email protected]> wrote in message
> news:[email protected]...
>> That's the level that ws protection is good for...
>>
>>
>> In article <[email protected]>, "Josh" <no
>> spam>
>> wrote:
>>
>>> There are no malicious attempts to get into the
>>> spreadsheet -- just people
>>> messing with it that shouldn't be changing anything. They
>>> think they are
>>> "helping"...
>
>
Oops Josh
I forgot to delete the line
ActiveWorkbook.Unprotect ("spw")
in the unseal macro.
It probably will give you an error........... if the workbook is not
protected in the first place.
Bill K
"Bill Kuunders" <[email protected]> wrote in message
news:evvZ%[email protected]...
>I will never argue with Chip Pearson or John McGimpsey. Good to see Chip
>back again.
> Is Frank Kabel on holidays?
> Anyway Gosh in stead of "on time" you may want to consider to protect the
> sheets before closing, before saving and "before opening"
>
> Sub Seal_File()
>
> For Each sheet In Sheets
> On Error Resume Next
> sheet.Protect ("spw")
> Next
> Application.StatusBar = ""
> End Sub
>
> Sub UNSEAL()
> 'you could give this macro a key combination ..............
> ActiveWorkbook.Unprotect ("spw")
> For Each sheet In Sheets
> On Error Resume Next
> sheet.Unprotect ("spw")
> Next
> Application.StatusBar = "NOT sealed"
> End Sub
>
> Note the application status bar commands
> that way you will see at the bottom of the sheet whether the sheet is in
> the unprotected mode.
>
> Regards
> Bill K
>
> "JE McGimpsey" <[email protected]> wrote in message
> news:[email protected]...
>> That's the level that ws protection is good for...
>>
>>
>> In article <[email protected]>, "Josh" <no spam>
>> wrote:
>>
>>> There are no malicious attempts to get into the spreadsheet -- just
>>> people
>>> messing with it that shouldn't be changing anything. They think they
>>> are
>>> "helping"...
>
>
Yes I already did!
Had the greatest respect for him and always wondered how he could answer so
many queries in all newsgroups.
Bill K
"Chip Pearson" <[email protected]> wrote in message
news:[email protected]...
> "Bill Kuunders" <[email protected]> wrote in message
>
>> Is Frank Kabel on holidays?
>
> Tragically, Frank passed away a few weeks ago. He will be sorely missed.
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
>
>
> "Bill Kuunders" <[email protected]> wrote in message
> news:evvZ%[email protected]...
>>I will never argue with Chip Pearson or John McGimpsey. Good to see Chip
>>back again.
>> Is Frank Kabel on holidays?
>> Anyway Gosh in stead of "on time" you may want to consider to protect the
>> sheets before closing, before saving and "before opening"
>>
>> Sub Seal_File()
>>
>> For Each sheet In Sheets
>> On Error Resume Next
>> sheet.Protect ("spw")
>> Next
>> Application.StatusBar = ""
>> End Sub
>>
>> Sub UNSEAL()
>> 'you could give this macro a key combination ..............
>> ActiveWorkbook.Unprotect ("spw")
>> For Each sheet In Sheets
>> On Error Resume Next
>> sheet.Unprotect ("spw")
>> Next
>> Application.StatusBar = "NOT sealed"
>> End Sub
>>
>> Note the application status bar commands
>> that way you will see at the bottom of the sheet whether the sheet is in
>> the unprotected mode.
>>
>> Regards
>> Bill K
>>
>> "JE McGimpsey" <[email protected]> wrote in message
>> news:[email protected]...
>>> That's the level that ws protection is good for...
>>>
>>>
>>> In article <[email protected]>, "Josh" <no spam>
>>> wrote:
>>>
>>>> There are no malicious attempts to get into the spreadsheet -- just
>>>> people
>>>> messing with it that shouldn't be changing anything. They think they
>>>> are
>>>> "helping"...
>>
>>
>
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks