Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

protecting worksheet and unprotecting some cells

I would like to password protect my worksheet and unprotect selected cells. Users should be able to write and delete in the unprotected cells. I tried Tools -> Protection -> Allow users to edit ranges. This option allows to write in the unprotected cell but does not allow to delete. Any suggestions?


Post your answer or comment

comments powered by Disqus
Hi,

I have written some code that will select multiple ranges in a worksheet and then lock them. However I get an error at the line highlighted in red and I cant figure out why

I was wondering if somebody could help.

Thanks


	VB:
	
 LockSSR() 
    For Each sht In Worksheets 
         
        If Left(sht.Name, 4) = "SSR_" Then 
            sht.Activate 
            ActiveSheet.Unprotect Password:="password" 
           
[COLOR=red]ActiveSheet.Range("A10:A20,C10:C20,G10:H20,A23:A33,C23:C33,G23:H33,A36:A46,C36:C46,,G36:H46,A53:A82,C53:F82,A85:A114,C85:F114,A117:A146,C117:F146,A152:A161,C152:C161,G152:H161,A164:A167,C164:C167,G164:H167,A170:A173,C170:C173,G170:H173,A179:A188,C179:C188,G179:H188,A191:A194,C191:C194,G191:H194,A197:A200,C197:C200,G197:H200,A206:A221,C206:C221,G206:H221,A226:A239,C226:C239,G226:G239,A244:A246,C244:C246,G244:H246,A249:A251,C249:C251,G249:H251,A254:A269,C254:C269,G254:H269,A272:A274,C272:C274,G272:H274,A277:A282,C277:C282,G277:H282").Select

            [/COLOR]    ActiveSheet.Cells.Locked = True 
            ActiveSheet.Protect Password:="password" 
        End If 
         
         
    Next sht 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I want to be sure I understand this protection thing correctly. I've searched and read several threads on this and here is what I conclude. Please tell me if I'm in the right on this or all wrong.

If a worksheet is protected and my VBA code wants to write to some cells in it, the code will have to include a provision to temporarily unlock the sheet, write the data necessary and then turn the lock protection back on.

There is no way to simply have the VBA code take charge and then when finished all the security will still be in place. The only way the VBA code can take charge is with an Unprotect command which will require the password.

....or is there a better way?

I unprotected specific cells in a worsheet and then protected the worksheet. Now when I send the worksheet to a user and he enters data into the unprotected fields, he is not allowed to print the worksheet. How do I protect some cells and unprotect others and still allow the users to print the worksheet after they enter data?

Thanks for your help.

I have a protected worksheet with unprotected cells/range. I can input
numbers but need to add comments and it will no allow me to.

Hello,
I am using excel 2010. This is my first post, so forgive me if my terminology is off a bit.
My situation:
I have a worksheet that contains both locked and unlocked cells. The unlocked cells I am having the user insert hyperlinks to external files. This worksheet also has some macros running. When I go to protect the worksheet, I have the options to allow the user to do many things. I check "select unlocked cells" and "insert hyperlinks" and proceed to protect the worksheet. I am leaving all the cells where I want hyperlinks inserted as "unlocked". All works fine except now that the worksheet is protected, my macros won't run.
I've done a lot of searching and have found a few ways to allow macros to run on protected worksheets, from entering some VB code on the open command (userinterfaceonly=true) to putting VB code that does the same thing at the beginning of each macro. All of this code unprotects the sheet first and then re-protects the sheet at the end. The problem is that when the code re-protects the worksheet, the options that I previously selected ("select locked cells" and "insert hyperlinks") are no longer selected. It defaults to unchecked.

So I guess my question boils down to this - can macros be run on a protected worksheet while still allowing users to insert hyperlinks in unlocked cells? How is this done? Can code be inserted that upon re-protecting the worksheet, my protection choices will still be valid?

Thank you very much for any help.

I have a worksheet that is used as a form that several people enter data into
and they would like to be able to spellcheck what they enter into the
unprotected cells.

I have certain cells unlocked then I protected the worksheet, only enabling
"Selecting of Unlocked Cells" in the protect sheet options. However, the
Spellcheck functionality is no longer able to be used in the spreadsheet once
it's protected.

Is there an option that will protect the worksheet and still allow
spellcheck of the unlocked cells?

I have a protected worksheet with some macros I'm creating. The macros
call the
Protect Userinterfaceonly:=True to be able to modify the sheet, but
there are still some problems:

- I can't read/duplicate Hidden formulas (I had to disable the Hidden
attribute) (something like Range1.Formula = Range2.Formula doesn't
work if Range2 is hidden)

- Much more important: macros can't manipulate Shapes. I can't
duplicate and delete shapes (I use many formcontrols, and I use macros
to add and delete them)

What can I do? I don't want to put the password of the sheet in the
macro. (like Unprotect password:="topsecret")

--- Bye

I protected the worksheet and made some cells available and others off
limits. Now I want to make some changes to the spreadsheet but
Tools/Unprotect Worksheet is unavailable. I did not password protect the
sheet.
Appreciate any assistance on this.
Thank you.

Hi,

My little loop checks if the value in a cell equals a number. At first, it hides or unhides rows if it equals the number I am looking for(Works Perfect). It then checks another colums to see if it equals another number I am looking for, and it "should" protect or unprotect another cell in the same row(the part where I have problems with).

Here is my code:
Sub HURows()
    BeginRow = 1
    EndRow = 181
    ChkCol = 16
    ChkCol2 = 18
    Password = "TI9779"

    For RowCnt = BeginRow To EndRow
            If Cells(RowCnt, ChkCol).Value = 1 Then
                Cells(RowCnt, ChkCol).EntireRow.Hidden = True
            Else
                Cells(RowCnt, ChkCol).EntireRow.Hidden = False
            End If
            If Cells(RowCnt, ChkCol2).Value = 1 Then
                Sheet2.Unprotect Password
                Range("$G(RowCnt)").Locked = True
                Sheet2.Protect Password, False, False, False, False, False, False, True
                                
                If Cells(RowCnt, ChkCol2).Value = 2 Then
                    Sheet2.Unprotect Password
                    Range("$G(RowCnt)").Locked = False
                    Sheet2.Protect Password, False, False, False, False, False, False, True
                End If
            End If

    Next RowCnt
End Sub


Hi all

I need to protect my worksheet and have some grouped cells which can be expanded and minimised by the user (using the plus and negative signs). Problem is when the sheet is protected the user cannot expand/minimise the groups, is there a way around this?

Thanks for any suggestions.

I have a worksheet that is used as a form that several people enter data into
and they would like to be able to spellcheck what they enter into the
unprotected cells.

I have certain cells unlocked then I protected the worksheet, only enabling
"Selecting of Unlocked Cells" in the protect sheet options. However, the
Spellcheck functionality is no longer able to be used in the spreadsheet once
it's protected.

Is there an option that will protect the worksheet and still allow
spellcheck of the unlocked cells?

Is it possible to protect/unprotect specific cells only? and how do i do this? (Button activated)

I'm trying to run macros on protected worksheets and I have the following code:


	VB:
	
 
Public UniversalPassword As String 
Sub AccelCurrentYours() 
     
    UniversalPassword = "Tampa" 
     
    ActiveSheet.Unprotect Password:=UniversalPassword 
    Sheets("Support Calculations").Unprotect Password:=UniversalPassword 
     
     '[code to be run]
     
    ActiveSheet.Protect Password:=UniversalPassword 
    Sheets("Support Calculations").Protect Password:=UniversalPassword 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The problem with the code is that VBA will not acknowledge the "unprotect" commands above. I've double-checked to make sure the passwords were correct to begin with but when the code runs the "unprotect" commands, I get an error within the excel spreadsheet saying that I'm trying to run an operation on a protected worksheet that needs to be unprotected. I'm not sure what I'm missing above and what's really odd is that similar code was working before. I'm not sure if I accidentally changed a property or something of that nature.

Thanks!

Hi all,

I would like to be able to be able to add comments to a protected worksheet.

I am using Excel 2007 Beta.

When I "protect" the sheet, I do the following...

Yes = Select locked cells
Yes = Select unlocked cells
Yes = Edit Objects

Once the sheet is protected, I am able to click on add-comment, but then I
am told the sheet is protected and this is not allowed.

Any ideas?

Best Regards,

Darrell

Excel 2003 worksheet
I have data in B1 being the header row through to Y366.
I have selected all cells and unlocked them. I have then selected B1:Y1 and
locked them as they are my header row.
I then went tools,protection,allow users to edit and input a range of
B2:Y366. did not want to give password permission, clicked apply and then
clicked protect sheet and checked unlocked cells and sort and then OK.
When I then go to sort data it tells me "The cell or chart you are trying to
change is protected and therefore read-only."
To modify a protected cell or chart, first remove protection using the
Unprotect the sheet command (Tools menu, Protection submenu)> You may be
prompted for a password.
What am I doing wrong. Should it not sort even though the protection is on
if I have a user range input?
Someone please help.
I did have columns hidden but they have all be displayed prior to the first
step as above.
I want it protected as I have formula's that will be tucked away on my
hidden columns that I don't want people to get to and wipe by mistake.
Thanks
Sue

By default, my whole workbook is protected.I have this button where people who want to access the workbook could enter the password but would only be able to access "entry feilds" (button unprotects the cells for these fields). how can i do this?

I have a protected worksheet. How could I copy and paste within the
same worksheet without unprotect it? Any help will be highly
appreciated!

Excel 2003 worksheet
I have data in B1 being the header row through to Y366.
I have selected all cells and unlocked them. I have then selected B1:Y1 and
locked them as they are my header row.
I then went tools,protection,allow users to edit and input a range of
B2:Y366. did not want to give password permission, clicked apply and then
clicked protect sheet and checked unlocked cells and sort and then OK.
When I then go to sort data it tells me "The cell or chart you are trying to
change is protected and therefore read-only."
To modify a protected cell or chart, first remove protection using the
Unprotect the sheet command (Tools menu, Protection submenu)> You may be
prompted for a password.
What am I doing wrong. Should it not sort even though the protection is on
if I have a user range input?
Someone please help.
I did have columns hidden but they have all be displayed prior to the first
step as above.
I want it protected as I have formula's that will be tucked away on my
hidden columns that I don't want people to get to and wipe by mistake.
Thanks
Sue

I am working in Excel 2003.

I want to lock the values and formats of all of the cells on the page, but still be able to sort/use an autofilter.

To do this I went to Tools --> Protection --> Protect Sheet
Then I checked "protect worksheet and contents of locked cell" (it won't let me hit ok without checking that)
Then under "allow all users of this worksheet to:"
And I selected, "Select locked cells", "Select unlocked cells", "Sort" & "Use AutoFilter"

From my logic and what I have read online, this should allow me to sort in my worksheet....
However, when I highlight what I want to sort and select the sort, I get the error message:
"The cell or chart you are trying to change is protected and therefore read-only. To modify a protected cell or chart, first remove protection using the Unprotect Sheet command."

What am I doing wrong?

I need to protect a worksheet and still have the user be able to sort data by
column. I have tried using the "Unlock" feature in the Format
Cells-->Protection dialog box but it doesn't make a difference. Once you
protect, you can't sort. Ideas?

In a password-protected worksheet, I have several cells and ranges which are unlocked to allow user input.

In testing, I can enter new data or edit existing without problem. Other users however are able to enter new data, but when they double-click to edit they are prompted to enter a password. Clicking Cancel allows them to proceed, but why is this happening?

Any suggestions appreciated.

I'm using excel 2010. I have a protected worksheet, inwhich there are cells that users can edit, including adding comments. In most cases this works fine. If I unprotect and reprotect a sheet the "Edit Objects" becomes unchecked and if not rechecked we cannot add comments. Does anyone have an idea why this happens, or how to prevent it from unchecking itself. This did not occur when we used office 2003, so hopefully it's just a setting somewhere.

Hi all, I have been spending many hours looking for some code to do some relatively simple comparison task but I haven't been able to find an answer to my particular problem.

I have 2 worksheets "today" and "yesterday", each with the same number of columns but potentially different number of rows (each row is a different project). The structure of each sheet is:

NAME // IDNUMBER // COST YEAR1 // COST YEAR2 // MANY OTHER COLUMNS
aa // ABT-100 // 100 // 800 // etc...
bb // SRT-876 // 245 // 445 // etc...
and so on... (please note that the IDNUMBER has a string of letters and numbers separated by highphens).

I am looking for some VBA code to compare "yesterday" and "today" worksheets for any changes by ID NUMBER and paste any item that has changed in a third worksheet "changes" with some highlighting:

1) any item (IDNUMBER) that is in "yesterday" but not in "today" should be copied in the worksheet "changes" with the whole row highlighted in red (dropped item).
2) any item (IDNUMBER) that is not in "yesterday" but that is in "today" should be copied in the worksheet "changes" with the whole row highlighted in green (new item).
3) any item (IDNUMBER) that is both in "yesterday" and "today" should only be copied in the worksheet "changes" when some cell of the row has been modified (i.e. difference between "yesterday" and "today"), in which case, the modified cells should be highlighted in yellow. Ideally, when some changes occured for an item, I would like to be able to present both values in the same cell next to each other ("yesterday",s value in red and "today"'s value in green).
4) do nothing with items that haven't changed between "yesterday" and "today".

I am guessing that this would probably involve some pretty intense coding so efficiency of the code is probably important here.

I have tried my best to do this but just can't do it!

Please!!!! Can someone help me!!!

Many thanks.

I have a protected worksheet and want to allow the users to change the groupings (i.e. add or remove rows from the grouping) on this sheet.
I have read this post http://www.ozgrid.com/Excel/outlining-protected.htm which was very helpful in allowing users to show and hide a group of rows. But it does not allow the user to add or remove rows to and from the group.

Would anyone be able to provide the additional VBA code to add this functionality.

Some more info on the particular file I am working on:
The worksheet I have has 4 sections which the users must fill out. Each section has 100 rows, 10 of which are visible. The other 90 are grouped and hidden. I want to allow the users to show as many rows as they need (for example 20). They must be able to remove rows from the group. The sheet is ofcourse protected.

Thanks,


No luck finding an answer? You could always try Google.