VBA -- protecting my sheet

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...


0
Josh
1/27/2005 4:20:04 PM
excel 39879 articles. 2 followers. Follow

9 Replies
466 Views

Similar Articles

[PageSpeed] 56

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:10vi53lno21bea2@corp.supernews.com...
> 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...
>
> 


0
chip1 (1821)
1/27/2005 4:22:00 PM
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 <10vi53lno21bea2@corp.supernews.com>, "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?
0
jemcgimpsey (6723)
1/27/2005 4:24:01 PM
Thanks


0
Josh
1/27/2005 5:24:50 PM
"JE McGimpsey" <jemcgimpsey@mvps.org> wrote in message
news:jemcgimpsey-D20022.09240127012005@msnews.microsoft.com...
> 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"...


0
Josh
1/27/2005 5:25:45 PM
That's the level that ws protection is good for...


In article <10vi8uome6d59ee@corp.supernews.com>, "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"...
0
jemcgimpsey (6723)
1/27/2005 5:56:00 PM
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" <jemcgimpsey@mvps.org> wrote in message 
news:jemcgimpsey-C02F55.10560027012005@msnews.microsoft.com...
> That's the level that ws protection is good for...
>
>
> In article <10vi8uome6d59ee@corp.supernews.com>, "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"... 


0
1/27/2005 9:16:21 PM
"Bill Kuunders" <bill.kuunders@xtra.co.nz> 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" <bill.kuunders@xtra.co.nz> wrote in message 
news:evvZ%23VLBFHA.2196@TK2MSFTNGP14.phx.gbl...
>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" <jemcgimpsey@mvps.org> wrote in message 
> news:jemcgimpsey-C02F55.10560027012005@msnews.microsoft.com...
>> That's the level that ws protection is good for...
>>
>>
>> In article <10vi8uome6d59ee@corp.supernews.com>, "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"...
>
> 


0
chip1 (1821)
1/27/2005 9:20:46 PM
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" <bill.kuunders@xtra.co.nz> wrote in message 
news:evvZ%23VLBFHA.2196@TK2MSFTNGP14.phx.gbl...
>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" <jemcgimpsey@mvps.org> wrote in message 
> news:jemcgimpsey-C02F55.10560027012005@msnews.microsoft.com...
>> That's the level that ws protection is good for...
>>
>>
>> In article <10vi8uome6d59ee@corp.supernews.com>, "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"...
>
> 


0
1/27/2005 9:22:54 PM
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" <chip@cpearson.com> wrote in message 
news:Ox4KTYLBFHA.1632@tk2msftngp13.phx.gbl...
> "Bill Kuunders" <bill.kuunders@xtra.co.nz> 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" <bill.kuunders@xtra.co.nz> wrote in message 
> news:evvZ%23VLBFHA.2196@TK2MSFTNGP14.phx.gbl...
>>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" <jemcgimpsey@mvps.org> wrote in message 
>> news:jemcgimpsey-C02F55.10560027012005@msnews.microsoft.com...
>>> That's the level that ws protection is good for...
>>>
>>>
>>> In article <10vi8uome6d59ee@corp.supernews.com>, "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"...
>>
>>
>
> 


0
1/27/2005 9:30:08 PM
Reply:

Similar Artilces:

Opening Excel Opens Too Many Sheets
Don't know what I did recently but when I open Excel, FINANCIAL MANAGER is added to the main menu at the top and several sheets/files are opened that I do not want to open. FINANCIAL MANAGER wasn't there before and I can find no way to take it off the menu. I find no "startup" folder that lists these files that I could delete the shortcuts to - I've looked in the various folders relating to Excel but not found anything that might stop this activity whenever I open Excel. Opening the shortcut I'd been using to get to Excel, it's like a macro starts off and I fin...

in VBA
Using Office 2003 I would like to reset any custom filter settings from all columns. The following resets Column 1 only: Selection.AutoFilter Field:=1 What is the VBA code to reset Columns 1-17? TIA Dennis Dennis, Selection.AutoFilter You could have found that out by yourself if you had just used Macro Record (as I did). -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Dennis" <dmburgess@removespamameritech.net> wrote in message news:961i10teu4q5r2t6rh38bmen6lski9r6f6@...

Using A Macro/VBA code to re-set formulas
Hi can any one help me with a Macro/VBA code to re-set formulas in pre-defined set of cells i.e. in my case C14:C40 At present I have a copy of the Formulas in another part of th worksheet and when I want to reset the worksheet (as the user can ove right the formulas as the formula is based on either the sum of anothe two cells or the users input) I use a Macro to copy and paste (special the formulas. What I require is a Macro/VBA Code which already has the formula store in the actual Macro/VBA code i.e. so they are not stored as copy in m worksheet Ay help would be greatly appreciated T...

sheet protection #7
why can't i protect some of my excel sheets? Is the workbook shared? Have you grouped multiple worksheets? jaci wrote: > > why can't i protect some of my excel sheets? -- Dave Peterson ...

I can't select cells on Excel using the touch pad, and neither the sheet not the workbook is protected
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) SOS folks, <br><br>I am in a pickle! So, as I was working on Excel, something happened because now it doesn't let me select cells with the touchpad, but only using the arrow keys. It's very strange. I went to tools to make sure that neither the sheet nor the book is protected...What can it possibly be? <br> I would be fantastic if somebody could cast some light on this technical glitch. <br><br>All the best, <br><br>Joseph Hail mates, I solved the problem by updating the software...

Time sheet issues
There are three questions I am preparing a time shhet. To strart with I have to prepare a blank sheet and give one sheet for each emlpoyee Here are the blank walls I am hitting 1. I want to format a cell (E2 in my time sheet) in such a way that if enter "1" it should display "January" and so on. How to do it? 2. The First cell of last row (A 34 in my of the time sheet) should have 28, 29, 30 or 31 depending the conditions of E2 and H2 which has month and year respectively. 3. The cells B4 to B34 should have days cooresponding to dates (number) in A4 to B34 They sho...

Unable to password-protect "VeryHidden" worksheet
Hello I'm using Excel 2007. In VBE I tried to password-protect a "VeryHidden" worksheet by going to VBAProject Properties, Protection, check the box on "Lock project for viewing" and set the password in the boxes as provided. However, each time I re-open the file (after saving and closing the file), the worksheet was not protected. When I check the VBAProject Properties, the check box of "Lock project for viewing" and the "Password to view project properties" are all back to its original state of unchecked/empty. Please advise what steps I...

How do I update links from .xls sheets to .xlms sheets
I have thousands of links to many consolidated spreadsheets created in Excel 2003. Now that I use Excel 2007, how do I convert the links without breaking them. On Apr 21, 2:06=A0pm, June <J...@discussions.microsoft.com> wrote: > I have thousands of links to many consolidated spreadsheets created in Ex= cel > 2003. =A0Now that I use Excel 2007, how do I convert the links without br= eaking > them. After making a copy of your workbook, try to search and replace ".xls" with ".xlms" Hope this helps, Chris M. It's not clear what you'v...

Sheets not getting displayed
When an excel file is opened the sheets are not getting displayed. Using windows XP. Pls help how to overcome this situation Are the sheets hidden? - - try Format, Sheets, Unhide, and unhide any required sheets listed there. Saj Francis Wrote: > When an excel file is opened the sheets are not getting displayed. > Using > windows XP. Pls help how to overcome this situation -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=21059 View this thread:...

I need a four sided program to print on one sheet
I need to make a four sided program, front & back to print on 8.5 x 11 sheet Okay - so what's your question? -- JoAnn Paules MVP Microsoft [Publisher] "debbie hughes" <debbie hughes@discussions.microsoft.com> wrote in message news:B9FA1ECD-6405-4690-BCA3-5AD2FE716683@microsoft.com... >I need to make a four sided program, front & back to print on 8.5 x 11 >sheet debbie hughes wrote: > I need to make a four sided program, > front & back to print on 8.5 x 11 sheet ============================== Choose one of the pre-formatted greeting ca...

last sheet or go back
I have been trying to figure out how to go back to the last sheet that I was working on. I often navigate my spreadsheets with hyperlinks, but I haven't figured out how to go back to the sheet I was last on. Any help would be great. By the way, what is up with all of these weird posts that we have been inundated with lately? Regards, Shane > By the way, what is up with all of these weird posts that we have been > inundated with lately? Some nutter sends them out now and then - probably trying to get into the top-10 posters lists !! Pete How about a navigation bar from Debr...

Excel VBA
I have a workbook that has multiple worksheets and 2 of them are letters. I would like to autofill data into the letters by using the userform. I have been able to code it using 1 worksheet. Is it possible to use same userform for the different worksheets? Thank you! Very simple, but you need to be more specific if you want some aid. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "djshides" <djshides@discussions.microsoft.com> wrote in message news:51B36917-FC0E-4567-B7D7-4B37E8A365B1@microsoft.com... > I have a workbook t...

VBA moving and adding data
I have this appliaction that spits out a format that is not usable for me, but with some minor changes it would be good to go. I just don't know how the code would work. here is what part of my file looks like: ColumnA Coulmn B Column C Column D FAULT F02 8 PILLAR 3018169.273 492458.0997 -9771.365343 3017502.798 493106.6833 -8696.365343 3016836.323 493755.2668 -7621.365343 3016169.848 494403.8503 -6546.365343 3015503.373 495052.4339 -5471.365343 Here is what I would like it to look like: ColumnA ...

VBA calling sp with ANSI_NULLS set ON
Hi, On Query Analyzer, I created a sp as follow: > > if exists (select * from dbo.sysobjects where id = object_id(N'dbo.PCS_SEG1') and OBJECTPROPERTY(id, N'IsProcedure')= 1) DROP PROCEDURE dbo.PCS_SEG1 GO CREATE PROCEDURE dbo.PCS_SEG1 as set ANSI_NULLS on set ANSI_WARNINGS on ** sql statement *** > > I need the to set ANSI_NULLS and ANSI_WARNINGS on as it involves another server using linked server. When executing the sp from query analyzer, it works fine too: > exec PCS_SEG1 > But, when I called from VBA, I received error: Heterogeneous queries require t...

Error occurred while loading sheet 3011111111111111111111111111111
I get this message on one of my XL workbooks - fairly sure it's converted from XL 97. I've read all the related posts which all say that the answer is to open the file in XL 97 and do the fix. All well and good but we no longer have XL 97 - is there any other way to fix the issue and open the file? Thanks. Hi FRM If you can't open the file in 2000-2003 then send me the file and i will change the codename for you. Send the file to me private -- Regards Ron de Bruin http://www.rondebruin.nl "FMR" <fionaross99@hotmail.com> wrote in message news:c5c9f293.04080...

transporting repetitive data from another sheet
How do I transport data from another sheet in a repetitive order? For example: In Column A I would like to transport data from sheet 3, however the data in sheet 3 skips every 4th line (row 4, 8, 12, 16, etc.). I have the formula ='Sheet3'!$E4 The next row should have ='Sheet3'!$E8 etc. When I highlight a series of these cells (all typed correctly and skipping by 4's) and drag the fill tool down (cross hair at bottom right of cell) it does not fill properly. Try =INDIRECT("Sheet3!E"&ROW(A1)*4) Do not alter the A1 - this just gets serial numbers 1,2...

How do I unprotect a protected worksheet that has passwrod protect
I need to unprotect a protected sheet but it has password protection on it and I do not have the password, can anybody help? record a macro and paste the text below into it:- Sub PasswordBreaker() Dim i As Integer, j As Integer, k As Integer Dim l As Integer, m As Integer, n As Integer Dim i1 As Integer, i2 As Integer, i3 As Integer Dim i4 As Integer, i5 As Integer, i6 As Integer On Error Resume Next For i = 65 To 66: For j = 65 To 66: For k = 65 To 66 For l = 65 To 66: For m = 65 To 66: For i1 = 65 To 66 For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To 66 For i5 = 65 To 66: F...

Converting a PDF of an Excel sheet back to Excel?
Hi Everyone, I have just been asked by my boss if it is possible to convert a PDF of an excel worksheet back into excel!I have no idea if it is possible, can anyone please help in this regard? Cheers Ash You should be able to copy and paste the values, but you definitely will lose the formulas. On Thu, 23 Sep 2004 17:31:48 -0700, "Ashley" <anonymous@discussions.microsoft.com> wrote: >Hi Everyone, > >I have just been asked by my boss if it is possible to >convert a PDF of an excel worksheet back into excel!I have >no idea if it is possible, can anyone pl...

Protecting certain cells
I am using formulas to get averages , standard deviation and other figurers... I want to be able to lock the cell so others cannot change my formula but be able to change certain values to get averages and other information .... I was trying to use the protection under tools to no hope... any help would be appreciated -- In Excel, cells are either locked or unlocked; the default is locked. If a worksheet is not protected, there is no difference in behavior, but if you protect the worksheet, the locked cells are protected, but the unlocked cells can be edited. What you need to d...

How to select combobx value with vba?
For an electrical part number quoting application developed in Excel 2000, I have a a userform (userform4) with a combobox (cboQpn) to hold the quoted part number, and another combobox (cboFormula) prepopulated with a named range (Partnum) The user types in a part number in cboQpn and then chooses the correct formula from cboFormula. Now in access I have a table (tblDetail) that holds previously quoted part numbers and their details--the formula used in the previous quote, for example. The formula information is in column 5 in tblDetail I want to emulate the manual selecting o...

VBA help #3
Hi, I'm an excel VBA total novice. I dabble in access vba, but am unsure where to start in excel. What I need is some code I can tie to a macro (& keyboard shortcut) which will find all values under 1000 in the selected range and change them to 1001. Can anyone point me in the right direction? Any help greatly appreciated.....thanks, Jason Look in the vba help index for FINDNEXT. There is a good example. c.value=1001 -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "Jay" <dummy@dummy.dummy> wrote in message news:eYGxqeELIHA.3...

VBA Function to explode string
Hi. I am wanting to know hbow I would go about getting the following 3 cells: Cells A1, A2, A3: :: 1-4,6,8 50 S1B1:: Into a form that looks more like this ::A1 A2 A3 1 8.33 S1B1 2 8.33 S1B1 3 8.33 S1B1 4 8.33 S1B1 6 8.33 S1B1 8 8.35 S1B1:: How it does the rounding (and on which one), it does not matter! Thanks, Tom -- tomjermy ------------------------------------------------------------------------ tomjermy's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24666 View this thread: http://www.excelforum.com/showthread.php?threadid=382414 You haven't exp...

How can I password protect a folder?
File--> Save As Tools--> General Options "NSharp" <NSharp@discussions.microsoft.com> wrote in message news:F35CB4CC-727D-4E00-B312-5620CE8D2C6F@microsoft.com... > Windows XP, like other sophisticated operating systems, does not provide permissions this way. Permissions are based on users and user groups. In XP Pro, make a new user group and call it something useful ("Sekrit") and assign users to that group. Don't forget to assign yourself! Then set permissions on the resource (folder) only allow members of the Sekrit group read/write or whate...

VBA question confused
I have a form set up with a recordset. I'm also using a filter. Me.Filter = "position IN ('custody','sgt','lieutenant','captain', 'other') and shift IN ('a-days','day shift','afternoon shift')" Me.FilterOn = True I'm trying to get an order by the last name field. I read Allen Brown's page about setting the order. I've also read other pages on this board that basically says it can't be done. So, I wrote an SQL that would give me what I want. strSelect = "SELECT * " & _ ...

escape from vba-loop
hi group, I've got an excel-sheet containing some vba-code. There's one sub that does some calculations in a loop which can run for some time. My users now want to be able to escape from this calculation by pressing <escape> or clicking a button on the sheet. But actually excel is frozen while calculating. What is the most common way to achieve this? I guess it must be possible to solve this with excel-events, but I'm not very experienced in using them. thanks for any hints, stephan You can add a "DoEvents" statement inside the loop. Excel checks for any instruc...