Can I "unlock" just one cell in a protected worksheet?

Is there any way I can unlock just one cell in a protected worksheet
without removing the protection from the entire worksheet?



I am working on a fairly complex worksheet that has 10-15 data entry
cells. The rest of the worksheet contains formulas and informational
text.

I have the formulas pretty much debugged and working. I am now down to
making mostly cosmetic changes to the text and formatting.

I have marked all of the data entry cells in a different color to help
me keep from overwriting a formula, but it still happens occasionally.
I usually catch it right away and can recover with Ctrl-Z, but once or
twice I had to reenter a formula.

It would be very handy if I could temporarily unlock just the cell I
want to change long enough to make a few minor changes to the wording,
font, aligment, etc.

What would be really great would be to be able to:

a. Right-click a locked cell.
b. Be offered an option to do some kind of "temporary" unlock of just
that cell.
c. Have Excel remember that that cell is supposed to be locked and is
only temporarily unlocked.
d. Make my changes.
e. Right-click anywhere on the worksheet and be offered the option to
remove all temporary unlocks.

This would greatly speed development and reduce errors.

Is anything like this available?


I am using excel 2007.
0
SquarePeg (160)
9/10/2008 8:15:08 AM
excel 39879 articles. 2 followers. Follow

1 Replies
1078 Views

Similar Articles

[PageSpeed] 9

Hi

The following 2 pieces of code seem to achieve what you want.
Change the Range C2:C6 to match the range of cells you want the action to 
apply to.
Right clicking on a locked cell will unlock it, but will also give you the 
dropdown to carry out other tasks.
Just left click in the cell and make your change.
Immediately you hit enter and leave the cell, then the Selection Change 
event kicks in and will lock the cell again.

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As 
Boolean)
If Not Intersect(Target, Range("C2:C6")) Is Nothing Then
ActiveSheet.Unprotect
Selection.Locked = Not Selection.Locked
ActiveSheet.Protect
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("C2:C6")) Is Nothing Then
ActiveSheet.Unprotect
Selection.Locked = Not Selection.Locked
ActiveSheet.Protect
End If
End Sub

Copy the code above,>right click on the sheet tab>View Code>Paste into the 
white Pane that appears>Alt+F11 to Return to Excel
-- 
Regards
Roger Govier

"Square Peg" <SquarePeg@Round.Hole> wrote in message 
news:bovec41oojtdvoungc2p9eje7a4tpvp3kk@4ax.com...
> Is there any way I can unlock just one cell in a protected worksheet
> without removing the protection from the entire worksheet?
>
>
>
> I am working on a fairly complex worksheet that has 10-15 data entry
> cells. The rest of the worksheet contains formulas and informational
> text.
>
> I have the formulas pretty much debugged and working. I am now down to
> making mostly cosmetic changes to the text and formatting.
>
> I have marked all of the data entry cells in a different color to help
> me keep from overwriting a formula, but it still happens occasionally.
> I usually catch it right away and can recover with Ctrl-Z, but once or
> twice I had to reenter a formula.
>
> It would be very handy if I could temporarily unlock just the cell I
> want to change long enough to make a few minor changes to the wording,
> font, aligment, etc.
>
> What would be really great would be to be able to:
>
> a. Right-click a locked cell.
> b. Be offered an option to do some kind of "temporary" unlock of just
> that cell.
> c. Have Excel remember that that cell is supposed to be locked and is
> only temporarily unlocked.
> d. Make my changes.
> e. Right-click anywhere on the worksheet and be offered the option to
> remove all temporary unlocks.
>
> This would greatly speed development and reduce errors.
>
> Is anything like this available?
>
>
> I am using excel 2007. 

0
Roger
9/10/2008 3:18:56 PM
Reply:

Similar Artilces:

Unlock VBA project #2
Hi I need to unlock the VBA project in an Excel file, but I don't know the password. I know, that there is a brute-force password cracker for protected worksheets, but only for worksheets - no VBA projects. Is there any other tool for this? Note that I already know, how to extract source codes from Excel file (using modified ClamAV) - but I need also to modify it, not only extract... Thanks in advance! T. ...

unlock toolbar
Hello, I'm getting the message, "This modification is not allowed because document is locked. I went to Microsoft Help and they suggested clicking on the comments section from the Insert toolbar. Comment is gray and not available to do this. As far as that goes, I am unable to do anything on Word now at all. How do I unlock protection and work on a new and/or existing document? Bruce My guess is that either you have not activated your Office software, or you have an expired trial version of Office 2003. What happens if you start Word or Excel and then click on Help | Activate P...

Lock or Unlock cell references in a formula for auto fill purposes
Are there any tricks (control or alt + something for example) to locking or unlocking (probably not using the correct terms) of cell references in a formula in order to set it up for the autofill scenario I want to do? For example if I have the following formula: =a1+b1 How can I easily change it to =$a$1+$b$1 or =a$1+b$1 or =$a1+$b1 I may just be crazy, but if you have any quicker command button suggestions to change these scenarios easier than having to type them every time that would be great. Thank you. -- David P. Select the relevant cell references in the formula bar and press ...

How can I unlock the grouping in Excel while locking cells?
When protecting a worksheet I would like to expand and contract groups while select cells are protected ...

Creating fields like gateways, when met, unlock next step
I am researching how to create "gates" in a database so that when someone initilializes the process filling out a form, they cannot progress beyond a certain point in the process until a specified field is "electronically" signed by the "keyholder" person authorized to approve progression to the next step. The "next step" could be gaining entry into another form or subform. I've not been able to come across anything helpful yet. Any help is much appreciated. Thank you in advance, "Aso" <Aso@discussions.microsoft.com>...

Unlocked protected hyperlink to defined range not work but others
I’m using Excel 2003 and having some problems with hyperlinking. The workbook has several sheets and this problem is only happening with the one sheet – the other sheets work fine for all kinds of hyperlinking. The problem on the one sheet is this: When unprotected everything is working normally. When protected (all relevent cells are unlocked): Hyperlinks to other worksheet work, Hyperlinks to external website work, Hyperlinks to defined ranges (by name) within same worksheet DO NOT work. (There are two defined ranges that I defined myself that I’m linking to within the worksheet)...

Personal.XLS is locked for editing. How to unlock it?
1/14/2010 WHEN USING EXCEL 2003 KEEP RECEIVING: PERSONAL.XLS IS LOCKED FOR EDITING - HOW TO UNLOCK IT AND PREVENT THIS FROM HAPPENING? THANKS http://www.microsoft.com/wn3/aspx/postui.aspx?query=Personal.XLS+is+locked+for+editing.++How+to+unlock+it%3f&cat=en-us-excel&lang=en&cr=US&guid=&sloc=en-us&dg=&base=http://www.microsoft.com/office/community/en-us/wizard.mspx&bclr=ececec&fclr=000000&sxml=http://www.microsoft.com/office/community/en-us/site.xml&stgxml=http://www.microsoft.com/office/community/en-us/settings.xml&sdgloc=en-US&fwiza...

Lock and Unlock cells using VBA
Hello All Anyone know how to lock cells using VBA, also unlock others currently locked. Page protection will be in use at the time this needs to be done. Also, is there anywhere on the net with a complete list of VBA functions, including a short statement of what each one does, with or without examples. If not, any good books that cover the above area. I'm really (strange as it might seem), enjoying playing with VBA and seeing exactly what it can do. Regards Peter The easy questions... Excel's Help is a very good source. take a look at Peter Nonely's workbook that describes...

How can I merge unlocked cells in a worksheet that has been protec
In my worksheet, I turned on "Protect Sheet" so that users can't view or modify locked cells. But since I still wanted them to be able to modify unlocked cells any way they wanted, I selected all the options in the list of "Allow all users of this worksheet to" features in the "Protect Sheet" dialogue box (except for "select locked cells" obviously). However, the users can't seem to merge unlocked cells. Is there a way of including this functionality in the allowed actions? You could create a macro to unprotect the sheet, merge the sele...

how do I unlock the document?
i cannot enter any informatio on the template givin to me?? Hi whyt error message do you get? -- Regards Frank Kabel Frankfurt, Germany brian wrote: > i cannot enter any informatio on the template givin to me?? ...

Can I "unlock" just one cell in a protected worksheet?
Is there any way I can unlock just one cell in a protected worksheet without removing the protection from the entire worksheet? I am working on a fairly complex worksheet that has 10-15 data entry cells. The rest of the worksheet contains formulas and informational text. I have the formulas pretty much debugged and working. I am now down to making mostly cosmetic changes to the text and formatting. I have marked all of the data entry cells in a different color to help me keep from overwriting a formula, but it still happens occasionally. I usually catch it right away and can recover with ...

How can I Unlock cell/worksheet?
I received a spreadsheet that has been used as a form and only the cells where information is to be populated are accessible. I thought I could do a Save As or even copy all the contents and paste them into a new spreadsheet so I can alter the cells that are not accessible, but I am unable to do this. I cannot contact the originator for an editable version. How can I work around this? XL passwords are not very secure... Check out this link... http://www.mcgimpsey.com/excel/removepwords.html Please use these powers for good instead of evil... -- HTH... Jim Thomlinson ...

Unlock takes me back to first subform record
Here is my code..... Private Sub cmdOK_Click() Dim sPassword As String sPassword = Nz(Me.txtPassword.Value, "") ' if it is a valid password, we let them edit the ' form that called this form. The calling form is ' responsible for check that the current user is ' either the QA tech on the record, or a member ' of the admins or full permissions group, this form ' only checks to see if the password they are entering ' to unlock that form is valid If ufnIsValidUserNamePassword([Forms]![frmSupervisor]![txt...

What happens with forms after windows is unlocked
I have several subforms set to function for data entry only (edits, deletes, navigation disabled; additions, data entry enabled). Everything works fine until I lock and unlock my computer. Several seconds after the computer is unlocked both subforms select and display an existing record. In one case it's the first record in the table, in the other a record in the middle and I can't figure out what's special about it. I've tried sticking event handlers on what I though were the most like events to blame but all I've managed to figure out is that whatever is going on is oc...

Access Record locking file created automaticaly for unlocked datab
How do i stop Microsoft Access from creating a access Record locking file for an anministrator on my computer who has nothing to do with the database on my computer and only exists as a place holder for the IT Department. Are you talking about an .ldb file? If so when and where does this file show up? -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Geoffrey Starr" wrote: > How do i stop Microsoft Access from creating a access Record locking file > for an anministrator on my computer who has nothing to do with...

Unlocking a workbook
This may be an easy question, but we have a worksheet that has somehow become locked in a strange way. There is no way to maximize it, and it has lost its upper right min, max and cancel buttons. It is difficult to work with because you cannot move it either. Any suggestions? -- Audubon ------------------------------------------------------------------------ Audubon's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=19032 View this thread: http://www.excelforum.com/showthread.php?threadid=398139 Audubon wrote: > This may be an easy question, but we have a...

Unlocking a password locked word document
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I have saved a document ages ago (in 2006) and password locked it. I am unable to open it now because I have forgotten what password I assigned. Is there any way to unlock it? It isn't so much a matter of 'when' you applied the pw as it is a matter of which version of the program was used. Different versions have employed somewhat different methods. Even so, the only thing you can do on your own is keep guessing... There is no "Disregard the Password" button :-) Depending on how critical...

Unlock VBA Project
Hi, In one of our client, the VBA project is locked with a password and the password is locked. Can anyone help in unlocking the project. We need to modify the customization done earlier in VBA. Your help in this will be highly appreciated. Regards, Siraj shismail@netsoftgroup.com.sa ...

Taborder with unlocked cells in protected worksheets
Excel 2003, sp2 Hi, When you lock cells in a worksheet you can jump between the unlocked cells using the TAB key. Is there any way to influence the tab order (without having to use a lot of code)? Regards Pete When using the unlocked cells method your order can only be left to right and top to bottom. There are other methods...........one of which is using a named range with no protection. See Bob Phillips' site for this method. http://www.xldynamic.com/source/xld.xlFAQ0008.html Other methods would use VBA code. Gord Dibben MS Excel MVP On Wed, 23 Jan 2008 16:46:34 +0100, ...

How can i modify document and unlock
After installing microsoft office 2003 I used it a few times, now when I go into type a document I get a message at the bottom of the screen saying the modification of the document is not allowed as it is locked. Please can anyone help I am falling behind in my assignments. A reply for someone not that great on computers would help. Thanks Check this out:- http://groups.google.ca/group/microsoft.public.office.misc/browse_thread/thr ead/6a0a8cde4502ec8b/e6cef89656dc2210%23e6cef89656dc2210 Epinn "Blondie" <Blondie@discussions.microsoft.com> wrote in message news:9032B876...

Unlocking Cells
Hi, I'd like to know if there's a simple way to unlock or lock cells. I'm not sure if "Lock" is the correct term here. For example, if you have a formula that has C$14. Is there an easy way to unlock (remove the "$") from the formula or to ad it without manually doing it? Thanks for your help! Lisa You can use a macro to change the reference style (absolute vs relative), but you can also do it manually. Select the portion of the formula (just a single cell reference or the entire formula if you want) and hit the F4 key to cycle through all 4 options. lj ...

unlocked cells
hi i have a protected worksheet with some unlocked cells for input of data, if i hit the tab button i step thru the unlocked cells horizontally row by row, if i press enter i step thru them vertically column by column - is there anyway of dictating the order cells are visited after the input of data?? for example after inputting data into cell a1, the next unlocked cell might be b1, is there a way of jumping direct to z10 after pressing the enter button, instead of b1? thanks andrew andrew, hava a look here, http://www.xldynamic.com/source/xld.xlFAQ0008.html -- Paul B Always backup you...

Can I conditionally lock/unlock some cells
Hi I am making a journal on a spread sheet whereby each row is a successive date. I would like to make it so that the user can ONLY change the current row (date = TODAY). I figured out how to use conditional formatting to make the current date row a different color but I would like to go further and make it so the user can only change the current line. Can this be done? How? Thanks in advance Hi, you can use 'Data - Validation' and check if the date in one cell is equal to TODAY() Frank Another way is to use a macro that unprotects the worksheet, locks all the cells, unlo...

unlock document
how do you unlock a modified document, that was already stored to your computer. I can not got back into the document and make changes. If you are seeing a message in the status bar that says “This modification is not allowed because the document is locked,” and if this is Word 2007, then most likely it is a trial version that has expired. If you're getting the error message in Word 2003, or if this is an activated retail copy of Word 2007, see http://support.microsoft.com/default.aspx?SCID=kb;en-us;818852 -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type ...

how do I unlock my COLUMN drop-down menu key in excel?
I've been using the drop-down menu button at the top of a column in my excel sheet to do searches. now when I click on it nothing happens as if its locked or turned off/inabled? Sometimes the AutoFilter dropdown buttons stop working, for no apparent reason. Often, if you close the file, then open it again, the filters resume working. tom from tooling wrote: > I've been using the drop-down menu button at the top of a column in my excel > sheet to do searches. now when I click on it nothing happens as if its locked > or turned off/inabled? -- Debra Dalgleish Excel FAQ...