Excel CHALLENGE anyone??

To be clear the goal is to protect the ws so employees do not inadvertently erase the numerous formulas there. Without a ps word, they can unprotect the sheet and resize the column as they wish. However there are some columns there that should be protected by a ps within the same non-password protected sheet.
I really want to know if excel can do something like this.
Thanks,
nee

When you protect a sheet the restrictions apply to all locked cells. If you want some columns to be editable then select the column/s and untick the locked cells box (Format|Cells|Protection) before protecting the sheet.

Now when applying protection (Tools|Protection|Protect Sheet) choose the options Format Cells, Format Columns & Format Rows and this will allow everybody to do formatting but not change the value in the cells that are locked.

hth

Hi Parry,
Thanks -- and thats what I think -- I mean like you said the restriction applies to all locked cells on a ws.
And your suggestion is a very good alternative if the main goal is to keep the financial columns not only unmodifiable but also unviewable.
Best regards,

Nee

Parry,
Now I tried applying protection (Tools|Protection|Protect Sheet) I dont see the options like you said --> Format Cells, Format Columns & Format Rows ...
I only see Content, Object, Scenario ???

Nee

Instead of duplicating your thread, why don’t you do exactly as I stated at:

http://www.mrexcel.com/board2/viewtopic.php?t=77063&highlight=

Ekim (Mike)
Thanks for the guide but like I said earlier, my goal is to protect the ws without a ps -- then within this same worksheet I want to have some columns protected by a ps.
I might not be clear earlier ... sorry

And there is no 2nd thought when I duplicate the thread (I just thought the 1st one I posted was confusing -- thats all).

Again, thanks for the tip. I am still curious to see if excel is flexible enough to do what I want above.

Regards,
Nee

Ekim said:

Instead of duplicating your thread, why don’t you do exactly as I stated at:

http://www.mrexcel.com/board2/viewtopic.php?t=77063&highlight=

Click to expand...

Just wanted to point out that I have locked the thread at the above link. As this particular thread has the majority of the posts, I am redirecting all duplicate posts to this one thread.

Please do not create new threads for the same topic.

Hi Nee,

You could always put some code on the worksheet, using the SelectionChange event and code to require a password if the user wants to select certain cells. For example the following code protects cells B2:C4 and the entire columns of E & F. Remember, you'd need to password protect your VB project to keep users from seeing your code and discovering your password.

HTH,

<font face=Courier New><SPAN style="color:#00007F">Dim</SPAN> booLocked <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>

<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_SelectionChange(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)
<SPAN style="color:#007F00">' Example of how to protect a portion of a WS using</SPAN>
<SPAN style="color:#007F00">' VB instead of Excel's built-in WS protection.</SPAN>

    <SPAN style="color:#00007F">Dim</SPAN> strPassword <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, strMsg <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
    
    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Intersect(Target, Union(Columns("E:F"), Range("B2:C4"))) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN>
        <SPAN style="color:#00007F">If</SPAN> booLocked <SPAN style="color:#00007F">Then</SPAN>
            strMsg = "Please enter the password needed to work on these cells." & vbCr & _
                    "Note: Password protection will reset once you select outside the protected area."
            strPassword = InputBox(strMsg, "Protected Columns")
            booLocked = <SPAN style="color:#00007F">Not</SPAN> (strPassword = "Secret")
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
        <SPAN style="color:#00007F">If</SPAN> booLocked <SPAN style="color:#00007F">Then</SPAN>
            Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN>
            Application.Goto Range("D" & Target.Row)
            Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN>
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#00007F">Else</SPAN>
        booLocked = <SPAN style="color:#00007F">True</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

ntruong said:

Parry,
Now I tried applying protection (Tools|Protection|Protect Sheet) I dont see the options like you said --> Format Cells, Format Columns & Format Rows ...
I only see Content, Object, Scenario ???

Nee

Click to expand...

Hi, I have Excel 2002 which has those options but if you have an earlier version the options are not separated but are grouped into general headings of Content, Object and Scenario. Look in Excel Help under 'Protect Worksheets from Changes' and a sub-heading of 'Limit viewing and editing of an individual worksheet' for a description of these three protection groups.

Basically Format Cells, Format Columns & Format Rows are like sub-groups of the Content option and unfortunately with version 2000 or less you dont have the protection options broken down to this level.

It may be able to be handled with Application.Undo or something similar in a Worksheet_Change event but Im not sure about it being that robust as I couldnt get this working to my satisfaction.

From your point of view I would say protecting the data is more important than having a user being able to format a cell. If it is important to users then I guess you have to create a separate method for formatting by say creating a form and unprotecting the sheet then re-protecting it.

Maybe someone else has a better idea?