MS Excel: Protected Columns and other items

[snoopy92211]

Hello All. Here's my issue

I have a spreadsheet, a portion of which is shown below. All of the columns in the worksheet have an auto filter. I want my analysts to be able to filter any and all of the columns. However, I only want them to be able to edit column c. I want Columns A and B to be password protected; because only the administrator of the worksheet should be able to edit these columns. In sum, how can I

1. lock columns a and b, (password protect it for the admin to update these columns)
2. leave c open for editing
3. Have all users filter the rows (but I still want A and B locked).

Keep in mind that the first row has header fields (No, Type and Price respectively)

A B C
No. Type Price
11 cat 5.66
23 dog 6.33
35 mouse 8.99
45 rabbit 6.77
5 snake 6.66


Thanks in advance!!

Snoopy92211

 

[kphu]

Highlight columnC and right click select "Format Cells".

Click "Protection" Tab Uncheck "Locked"

Clik "OK".


Go to "Tools" then "Protection" and select "Protect Sheet"

create a password.

 

[KenWright]

Not quite as easy as that kphu depending on what version of Excel it is

http://www.contextures.com/xlautofilter03.html#Protect

Regards
Ken..........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
---------------- Click here to help the tsunami victims ----------------

 

[snoopy92211]

This is great! However, is there a way I can click on one of the locked fields and have it prompt to the password? My users need very simple. Nothing's simpler than clicking a button. Thanks!

 

[01Patrik]

That would be a worksheet_selection.change event.

Click Alt+F11, doubleclick the sheetname in the top-left window. In the main frame, paste this.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Set isect = Application.Intersect(Target, Range("A:B"))
If isect Is Nothing Then Exit Sub

pw = InputBox("You have clicked a cell in column A or B. Please enter password to continue", "Enter password")
If pw = "" Then Exit Sub

ActiveSheet.Unprotect pw

End Sub

// Patrik
______________________________

To the optimist, the glass is half full. To the pessimist, the glass is half empty. To the IT Professional, the glass is twice as big as it needs to be.

 

[kphu]

yeah kenwright is as usual right.

Different versions of excel have different protection capabilities.

That's what happens when you try and jump the gun before the forum leaders get on it.

hey give the little fish a try.

 

[snoopy92211]

Patrik,

Does your code replace (Tools>Protect Sheet)? or is it in addition to? (I should note that I am using Excel version 2002. Because when I follow the steps (prior to your response), then add your code it doesn't seem to function.

thanks!

snoopy

 

[KenWright]

If you have Excel 2002 then you have the option to protect the sheet and still allow use of Autofilter, but you must check that option in the list presented to you when you hit Tools / Protection / protect sheet (it's near the bottom).

Other than that I would have thought you just unlock cells in Col C, protect the sheet and allow autofilter.

If anyone is using a version other than 2002 though then they will NOT be able to filter.

Regards
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
---------------- Click here to help the tsunami victims ----------------

 

[snoopy92211]

Ken,

Yes that part is actually working. My issue is that when a user tries to click on a locked cell, the password prompt doesn't automatically pop up. That's what I'm trying to fix and that's what that last question is in response to.



You guys rock!

 

[KenWright]

How about

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set sh = ActiveWorkbook.ActiveSheet
On Error GoTo ErrHandler:
  
  With Target
     If sh.ProtectContents = True And .Locked = False Then
        Exit Sub
     ElseIf sh.ProtectContents = True And .Locked = True Then
        Application.EnableEvents = False
        PWORD = Application.InputBox("Please enter the password to unlock")
        ActiveSheet.Unprotect PWORD
        Application.EnableEvents = True
     End If
   End With

ErrHandler:
If Err.Number = 1004 Then
   MsgBox "Wrong Password, sorry"
   Application.EnableEvents = True
   Exit Sub
End If

End Sub

in the sheet mnodule

Regards
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
---------------- Click here to help the tsunami victims ----------------

 

[snoopy92211]

I don't understand why this code isn't working!

This is what I did

1. selected the columns I didn't want to be locked. format cells>protection>unlocked the columns i needed to haev open

2. copied Ken's formula in the view code setction of my sheet.

3. navigated to Tools>Protection>Protect Sheet

checked protect worksheet and contents of cells
unchecked select locked cells
checked select unlocked cells
checked sort
checked use Auto Filter.
entered password twice.

When I click on a locked cell, the password prompt should appear. it doesn't. HAYLP!

 

[01Patrik]

If you uncheck "select locked cells" the way you have described, then noone can click the locked cells - and the code will never be activated.

Have it checked, and both my code as well as Kens will work. (Also make sure the code is in the worksheet, not in a module)

// Patrik
______________________________

To the optimist, the glass is half full. To the pessimist, the glass is half empty. To the IT Professional, the glass is twice as big as it needs to be.

 

[snoopy92211]

ok. one last question. The code definitely works. However, let's say column A is locked. and I try to choose A12. the prompt pops up and the cell is locked. How do I lock the entire sheet when the user prompts for the password(given that they supply the correct PW)

 

[01Patrik]

If I understand you correctly - which I'm not entirely sure I do - then you want to know how to turn the protection back on again after it's been removed.

You do this (easiest) the same way you put it on in the first place - tolls | protection.

If you want the user to apply the pw him/herself, then a command button would be the easiest way (I think).

put the following code in a MODULE (insert menu), not in the worksheet event.

sub protect_sheet()

activesheet.protect contents:=true, password:=XXXX

end sub

Put the command button anywhere, rightclick on it, apply macro, choose the protect_sheet macro.


If you want to remove the lock on the selected cell only, then you should use Kens code, with some modification:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set sh = ActiveWorkbook.ActiveSheet
On Error GoTo ErrHandler:
  
  With Target
     If sh.ProtectContents = True And .Locked = False Then
        Exit Sub
     ElseIf sh.ProtectContents = True And .Locked = True Then
        Application.EnableEvents = False
        PWORD = Application.InputBox("Please enter the password to unlock")
        ActiveSheet.Unprotect PWORD
        .Locked = False
        sh.Protect Password:="hej", contents:=True
        Application.EnableEvents = True
     End If
   End With

ErrHandler:
If Err.Number = 1004 Then
   MsgBox "Wrong Password, sorry"
   Application.EnableEvents = True
   Exit Sub
End If

End Sub

// Patrik
______________________________

To the optimist, the glass is half full. To the pessimist, the glass is half empty. To the IT Professional, the glass is twice as big as it needs to be.

 

[01Patrik]

Oh sorry, you need to change the 'password:="hej"' part in the middle to your own pw.

// Patrik
______________________________

To the optimist, the glass is half full. To the pessimist, the glass is half empty. To the IT Professional, the glass is twice as big as it needs to be.

 

[snoopy92211]

Thanks Patrik. Basically the code allows a user to select the locked cell if they enter a password. Which is fine, if I only need a few cells locked. However, I want the user to select any locked cell, and upon entering the password, have the entire locked section unlock.

for example
Column A,B,C are locked.

I select cell A2. It prompts me for a password. Instead of the code only unlocking A2, I want, Columns, A,B, and C to unlock as well.

Did that make sense?

Snoopy92211/Angel

 

[01Patrik]

My first reply (code) does just that. So does Kens. Just disregard my last couple of posts. But then I don't understand your last wuestion - what do you mean?



// Patrik
______________________________

To the optimist, the glass is half full. To the pessimist, the glass is half empty. To the IT Professional, the glass is twice as big as it needs to be.

 

[snoopy92211]

Oh! Sorry for the confusion .

Ok this is my last question (sOrry, I'm an Excel VBA Newbie)

Ok so user unlocks the cells that they need.

I want to be able to do 2 more things.

1. a command button (or something like that) that lets the user add other types to the spreadsheet. For Col. b, I'm using data validation that's a List(drop down list) and I have the range named animals.

I want to be able to programatically add a different type of animal to the animal range.

A B C
No. Type Price
11 cat 5.66
23 dog 6.33
35 mouse 8.99
45 rabbit 6.77
5 snake 6.66

2. Lock the workbook upon closing the program.

Thanks!

Angel