Protect Sheet and Lock Cells

Hi, is it possible to protect a worksheet so users can 
only select unlocked cells in Excel 2000?  I have a 
worksheet with certain cells protected.  Users of the 
worksheet can select all of the cells and modify only the 
unprotected cells.  I want them to only be able to select 
(and modify) the unprotected cells.  I know how to do it 
in Excel 2002, but can't figure it out in Excel 2000.  
Thanks for any help.
0
anonymous (74718)
8/25/2004 9:55:47 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
323 Views

Similar Articles

[PageSpeed] 14

You could have a macro that does the protecting--put it in your auto_open
or workbook_open event:

Option Explicit
Sub auto_open()
    With Worksheets("sheet1")
        .Protect Password:="hi"
        .EnableSelection = xlUnlockedCells
    End With
End Sub

Excel won't remember this setting after you close it and reopen the workbook
(that's why it's in auto_open).



DB wrote:
> 
> Hi, is it possible to protect a worksheet so users can
> only select unlocked cells in Excel 2000?  I have a
> worksheet with certain cells protected.  Users of the
> worksheet can select all of the cells and modify only the
> unprotected cells.  I want them to only be able to select
> (and modify) the unprotected cells.  I know how to do it
> in Excel 2002, but can't figure it out in Excel 2000.
> Thanks for any help.

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
8/25/2004 11:35:39 PM
You actually have to do each worksheet one at a time.  But you could put your
code into a loop and pick them up that way:

Option Explicit
Sub auto_open()
    Dim wks As Worksheet
    For Each wks In Worksheets(Array("sheet1", "sheet2"))
        With wks
            .Protect Password:="hi"
            .EnableSelection = xlUnlockedCells
        End With
    Next wks
End Sub



DB wrote:
> 
> Thanks, the macro works great.  One more question, how can
> I have this macro apply to more than one worksheet
> ("sheet1" and "sheet2")?  I tried this: With Worksheets
> ("sheet1", "sheet2"), but it didn't work.  Thanks again.
> 
> >-----Original Message-----
> >You could have a macro that does the protecting--put it
> in your auto_open
> >or workbook_open event:
> >
> >Option Explicit
> >Sub auto_open()
> >    With Worksheets("sheet1")
> >        .Protect Password:="hi"
> >        .EnableSelection = xlUnlockedCells
> >    End With
> >End Sub
> >
> >Excel won't remember this setting after you close it and
> reopen the workbook
> >(that's why it's in auto_open).
> >
> >
> >
> >DB wrote:
> >>
> >> Hi, is it possible to protect a worksheet so users can
> >> only select unlocked cells in Excel 2000?  I have a
> >> worksheet with certain cells protected.  Users of the
> >> worksheet can select all of the cells and modify only
> the
> >> unprotected cells.  I want them to only be able to
> select
> >> (and modify) the unprotected cells.  I know how to do it
> >> in Excel 2002, but can't figure it out in Excel 2000.
> >> Thanks for any help.
> >
> >--
> >
> >Dave Peterson
> >ec35720@msn.com
> >.
> >

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
8/26/2004 9:37:38 PM
Reply:

Similar Artilces:

protecting outlined data
I want to be able to use the expand/collapse function in outlines when the worksheet is protected. Copy this macro in a normal module It will run automatic when you open the workbook You can also use the workbook open event Sub auto_open() With Worksheets("sheet1") .Protect Password:="rbelecki", userinterfaceonly:=True .EnableOutlining = True End With End Sub Look in the VBA help for more information about protect(userinterfaceonly) -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl "rbelecki" <rbelecki@l...

Password Protect Outlook #2
I work in an offcie environment and sometimes have to leave my PC unattended for just a few minutes. Unfortunately some confidential emails have been read by my staff and I need a way of safeguarding them other than turning off the PC, is there any way I can password protect access into my Outlook 2000, so all I do is close Outlook. (Sorry still operating under W95) Many thanks You can set a password on a Personal Folders File. If the folder list isn't visible in Outlook, click View | Folder List. Right-click the root folder (usually "Personal Folders" or "Outlook T...

linking cells in a column (result) to cells in a row (source)
MS XP Pro / Excel 2007: Good day all. I have data in two consecutive rows. The 1st one is text and the second contains formulae which results in values. (Call these the "source") On another sheet in the same workbook I have two columns (Call these the destination) which need to be linked to the source rows if value (other than zero) is present in the 2nd source row. There could be zero value or blank cells within the source rows. I need code to sequentially seek values (other than zero) in the cells in the 2nd row of the source and when found, the next available cell ...

Lock a field
Hi all, I'd like to know how to lock a field after pressing the next/previous button on the record status bar. Some of the fields are only unlocked when pressing the add button on my form. However, I found that the fields are still unlocked with pressing the next/previous button on the record status bar after pressing the add button on my form. Do you mind to advise which event I should put the lock property to? Thank you for your kindness. ...

Outlook 2007 locks up / freezes frequently
Sadly, as of Jan 2010, this issue persists with Outlook 2007. In my opinion, the issue is most noticeable when (1) opening an email received from someone-@-AOL.com or (2) opening an email that contains resources located on the internet (graphics). I tried disabling all non-Microsoft add-ins from the Trust Center, as one post suggested (http://social.microsoft.com/forums/en-US/tabspace/thread/a7fac3ef-6e09-43ca-bc2b-00b37d639c6a/) but doing so did not provide a remedy to the issue on my WinXP Pro system. Regardless, here are the steps I took: Outlook > Tools > Trust ...

Quicken Password protected
I just got Money 2004 Deluxe, I am trying to import my data from Quicken and the program says there is a password for my quicken account that is required, but I never had a password. No way to get past this. Any ideas? If your data file is Q04, Money04 can only deal with Q03 or older. Otherwise, who knows? "Stephen Weiss" <stephenaweiss@comcast.net> wrote in message news:012f01c3cb22$9a957ac0$a101280a@phx.gbl... > I just got Money 2004 Deluxe, I am trying to import my > data from Quicken and the program says there is a password > for my quicken account that is req...

Insert a graphic in a cell
I'd like to be able to enbed a logo in a cell. Not possible. Graphic objects reside on the drawing layer "above" the cells. Cells can contain formulae or values only. Workaround. Insert your graphic, and size it so that it exactly covers the cell. Right-click it, choosing Format Picture. In the Properties tab of the Format Picture dialog, choose the Move and Size with cells radio button. In article <F253C07B-E71A-445E-B612-0189187A09D9@microsoft.com>, Pete_Escher fan <Pete_Escher fan@discussions.microsoft.com> wrote: > I'd like to be able to enbed a...

If text exists pull text from another cell....how to do it?
I have a schedule with multiple sheets that I have made for my staff. What I am having trouble with is getting text from one sheet to another. I have Servers, Host, BarBus sheets as well as Mon, Tue, Wed, Thur, Fri, Sat, and Sun sheets. In the Servers sheet in A1 Column I have their names B1:O1 are their shifts. The setup is the same for Host and BArBus sheets. B1 has three shifts AM, OCA, XPOA B2 has four shifts PM, BR, OCP, XPOP What I can not figure out is how to pull the names of the people that are working. Example: On Sheet Mon I have Servers______Host______Bar_______Bus I need...

Re: Specification sheet
Dear Aladdin I have had another look at selecting the various specification items via check boxes, and the best way to go about this is by using user-forms. The down side to this is the fact that there will be a certain amount of coding that will need to be done in order to get the form working in conjunction with the contractors works order. But by no means impossible. If you do not feel confident in doing it yourself, I am sure that there are a number of IT contractors in your area who would be willing to help. Another alternative, and possibly not viable for you, is to use a data...

Password protection on split database
I have a database which is split into front end and back end. The front end is password protected but the backend isn't Is it possible to protect both with the same password? I've tried protecting the backend with a password but when I open the front end it can't "see" the backend. Any ideas? Thanks Tony Put a password on the backend, then, after opening the front-end, use the Linked Table Manager to update the links (just like you would if you had moved the backend). You should be prompted for the 'missing' password during this process. You should only...

How to protect 2. axis in an Excel Chart to stay if data changes
A Pivot table is created on Tab1 in an Excel workbook. On Tab2 there is a Line Chart created with data from Tab 1 using 2 axis. If data in Tab1 change the 2nd axis on Tab 2 disappears, jumps ober and overrules axis 1. How can I protect chart area with 2 axis being able to change referring data in the pivot table. ...

pivot table empty cell
I need to have an empty cell be in the pivot table. I have multiple pivot tables I have generated for a report. I have created a worksheet that reads the data from the pivot table (so it looks nicer). I have tried to check the box in options that says to put a 0 or another symbol (*) in an empty cell and it doesn't work. I want people to be able to input their own data and refresh the pivot table. If I have four response options (sometimes, always, never, usually) and the new data does not have data for one of the options then the table that reads the data gets messed up with its numb...

Cell ref is it possibile to calculate / change this in a formula
Any SUPER USERS uot there? I am trying to construct a formula where I need to copy it down 6500+ rows but I need one of the cell refs to be calculated in sted of having its row number to raise by one for each row copied. sample, Normel coping: =IF(AND(F25<=201012,F25<>$F$1),B25,'MASTER tot'!C25) =IF(AND(F26<=201012,F26<>$F$1),B26,'MASTER tot'!C26) What I am trying to get: =IF(AND(F25<=201012,F25<>$F$1),B25,'MASTER tot'!C25) =IF(AND(F26<=201012,F26<>$F$1),B26,'MASTER tot'!C16) For every line the for...

Adding cells with a particular comment
Hi all, is there any simple to add all cells in a row which have a particular comment without using macro. pls help. Regards, NC HI, Use the SumIF function -- anurag. ----------------------------------------------------------------------- anurag.d's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=895 View this thread: http://www.excelforum.com/showthread.php?threadid=31476 well i can use it for particular values but how to use it with comments. My problem is a particular comment for e.g "reject" or "accept" ia added to that cell, & ...

Tab
Tab is supposed to move one cell to the right. Mine moves from column A to S, to AK to BC and so on. It always moves in hughe ranges. The arrow works, however, I am used to the TAB and want to continue using that. Ther is nothing in the set-up screens that would indicate this possibility. What am I doing wrong? -- Thanks for helping Click on Tools/Options in Excel's menu bar, then select the Transition tab and uncheck the CheckBox labeled "Transition navigation keys". Rick "Dies-und-Das" <DiesundDas@discussions.microsoft.com> wrote in message news:E119...

Printing Patterns in Cells
Hell, I am trying to print a worksheet I created with colors and patterns. The colors print, but the patterns don't. They show up in print preview. Can anyone enlighten me please? Oh, and is there a limit to the width of a worksheet. Mind will only go as far as column IV. Thanks for your help Patty ...

I Need to change reference sheet for all cells on a form
Good afternoon, I am copying a spreadsheet to make summary sheet and I need to change which sheet the cells are referncing on my copy. Some of the cells refer to for example, =PRIOR YEAR!B3, some of the cells use a command to Round(PRIOR YEAR!B3/100), I would like to change all the references that currently refer to "PRIOR YEAR" sheet and make them "CURRENT YEAR" sheet, so my references would look like this =CURRENT YEAR!B3 and Round(CURRENT YEAR!B3/100). I am curious if a paste special, or shortcut exists that can do this quickly so I dont have to go in and manual...

Deleting Parts of Cells
I have a list of information in a column. All the information has the format of having numbers and letters then a / and more data. I only want to look at the data to the left of the /. So, I would like the data to the right of the / deleted, including the /. For example, I need YYY9/5 to read YYY9. Any suggestions? You could bring it into Excel as a txt file open the txt file in excel Text Import Wizard will appear select >delimited check > other and type the "/" into the box finish you should have a separation where the / was. "GOL" wrote: >...

"Cannot shift objects off sheet"
I am new to Excel 2007. When I try to add rows/columns I get the message "Cannot shift objects off sheet". How do I fix this? http://lmgtfy.com/?q=Cannot+shift+objects+off+sheet http://blog.contextures.com/archives/2009/03/16/cannot-shift-objects-off-sheet-in-excel-2007/ http://support.microsoft.com/kb/211769 "Vickie" <Vickie@discussions.microsoft.com> wrote in message news:54A28B3A-CE1C-4130-AA70-D0FFF300A319@microsoft.com... >I am new to Excel 2007. When I try to add rows/columns I get the message > "Cannot shift objects off sheet&quo...

Sorting Multiple Sheets
I have a workbook with multiple sheets. The main sheet has 2 columns, 1 for first name, 1 for last name. The remaining sheets all have formulas to pull the first and last names automatically from the 1st sheet. If a user sorts the names on the first sheet, they will sort on subsequent sheets, but the information that goes with those names on subsequent sheets will not be included in the sort, thereby misplacing information. Is there anyway to remedy this, short of sorting each sheet each time there is an addition to any one sheet? ...

Locked out of Clip Art on Office On-line; also locked out of hyper
All of a sudden, I cannot access clip art from Microsoft Office on-line when in Publisher 2003. (However, On-line help DOES work). I have made no changes to my system except the following: I downloaded Netscape 8.0 browser, didn't like it, and uninstalled it (leaving version 7.2 on my system). Other than that, no changes. I disabled my firewall (Zone Alarm), Anti-virus (Norton), & Anti-spyware (Counter Spy) to see if any of them were preventing access--still nothing. The only other strange thing that happened earlier this week involved a hyperlink I inserted in a Publisher...

limit no of characters in cell
How can I limit the number of charaters (including spaces) in a cell to 48? Additionally, I'm not sure if this is possible, say it applied to cell A1, once 48 characters are reached, it will then automatically drop down to the cell below to A2 and do the same again. thanks -- rocket0612 ------------------------------------------------------------------------ rocket0612's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=19492 View this thread: http://www.excelforum.com/showthread.php?threadid=375572 Try validation from the Data menu, and select text length...

Protection
Hi everyone OK my problem is I am using an offie computer that everyone has access to and we cannot - and do not want to - change this. Is there a way that I can put a password for my profile on Microsoft outlook so that no one can read the new AND the old messages found in my inbox? You can put a password on your PST file, presuming you use a PST file... Emad Kamel wrote: > Hi everyone, > OK my problem is I am using an offie computer that everyone has > access to and we cannot - and do not want to - change this. Is there > a way that I can put a password for my profile on Micr...

Lock Column in Excel 2000
I have a non-Microsoft program that exports data into Microsoft Excel 2000. I have it setup so after the data is exported into Excel, I insert a column. In the new column, I put a function (for the same of example, Let's say it is in column 'c', starting in row '5', =a5*b5. There are many columns of Data after column c. Is there any way to setup up a template Excel file, so that column c in Excel is "locked" (or whatever the term for this is), so the data is exported to columns 'a', then 'b', then skips c, then the next column is 'd', then...

CANNOT PROTECT
Trying to password and read only protect a Microsoft Word docuement that is embedded in Excel. When I open the "protected" document, the read-only prompt does not appear...and I can still make changes and close the docuement. HELP! Try this: create the document directly in word, protect it and save it to disk. Then copy the text you want and past it special as an OLE object in excel Peter ...