PDA

View Full Version : Solved: hide and protect column



MNJ
12-04-2006, 02:29 AM
:hi:!!

I wanna give a big thanks for those who helped me. :thumb Right now, I'm nearing the end of my project. And I've got to say u guys helped me a bunch.

But I would like to pose my seemingly last question which has been bothering me. And yes, I did my homework and search thru the forum but none is as close as it gets. So i'm forced to trouble you guys once again!:banghead:

anyway, I need a macro which hides and protects a range of cells.
Meaning.. even if the user unhides the columns, they are still not able to modify the range of cells. Because it is protect by a password.

Is this possible? Tried using recording a macro but only work for sheets.

Pls :help ! Thanks

Bob Phillips
12-04-2006, 02:55 AM
You cannot protect a range per se, you protect the sheet. But the trick is to unlock all cells (Format>Protection), and then unlock those columns that you want protected, and protect the sheet.

MNJ
12-05-2006, 07:09 PM
thanks for your prompt response. I don't really understand what you meant. Pls advise me on what to do.

Right now, I only want to lock two columns : Column I & Column L. The rest i want it unprotected.

So i did what you told me and got this pop up window. Could u pls tell me how to go from here. Many thanks. :bow:

(sorry i can't post links. since i've only currently 4 post) but i click on tools>protection>protection sheet to get the pop up window.

lucas
12-05-2006, 08:36 PM
and then unlock those columns that you want protected, and protect the sheet.

I'm not positive but I think Bob meant lock those columns you want protected....

With your sheet open, click the square just above 1 on the left side of the screen where the row of numbers are....just to the left of the A which is your columns. That should turn the page blue which means the entire sheet has been selected.

Right click on the blue area and select "format cells"
look for the Protection tab at the top of the dialog box and select it.
Now remove the check where it says "Locked" and click OK

Now click the I to select the entire column...now hold down the control key and select the L for the entire column.

Now right click on the blue area again and this time make sure that Locked is checked and click OK.

Now protect your sheet and try to use column I or L

lucas
12-05-2006, 08:45 PM
Sub Macro1()
Cells.Select
Selection.Locked = False
' Selection.FormulaHidden = False
Range("I:I,L:L").Locked = True
' Selection.FormulaHidden = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Range("A1").Select
End Sub

MNJ
12-05-2006, 10:42 PM
Thanks for your prompt reply and your useful suggestions. The latter code was what i was finding for.


Private Sub Worksheet_Activate()

I used this code in replace for " Sub macro1() ".

However, I had another code which uses this sentence too. Thus the code won't work.

May I know how to combine both codes?



' for zooming in at 85%

Private Sub Worksheet_Activate()
Call Zoomer
End Sub

' code that u provided me with

Private Sub Worksheet_Activate()
Cells.Select
Selection.Locked = False
' Selection.FormulaHidden = False
Range("I:I,L:L").Locked = True
' Selection.FormulaHidden = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Range("A1").Select
End Sub




Pls advise me. Because this would help me for future uses. Thanks. :bow:


MNJ

Bob Phillips
12-06-2006, 03:59 AM
just combine them



Private Sub Worksheet_Activate()
Cells.Select
Selection.Locked = False
' Selection.FormulaHidden = False
Range("I:I,L:L").Locked = True
' Selection.FormulaHidden = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Range("A1").Select

' for zooming in at 85%
Call Zoomer
End Sub

MNJ
12-06-2006, 06:59 PM
Hi XLD,

Thanks. i didn't know it was so easy. but when i tried to run the code again, the line "Selection.Locked = False" was highlighted, along with a pop up box " Run time error 1004"



Private Sub Worksheet_Activate()
Application.ScreenUpdating = True
Cells.Select
Selection.Locked = False
' Selection.FormulaHidden = False
Range("I:I,L:L").Locked = True
' Selection.FormulaHidden = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Range("A1").Select

' for zooming in at 85%
Call Zoomer
Application.ScreenUpdating = True
End Sub





Pls advise me. Thanks a million

lucas
12-06-2006, 11:36 PM
It runs ok for me....no error...protects I & L columns.
I didn't run it in an activate code though...just a module.
you should also change your first:
Application.ScreenUpdating = True
to false

MNJ
12-06-2006, 11:56 PM
Hi,

It was fine when i run it the first time. But when i run the macro the second time consecutively, it had run time error " 1004 ". :doh:

Pls :help

Thanks :bow:

lucas
12-07-2006, 12:07 AM
I see....seems you have to unprotect the sheet first.
Private Sub Worksheet_Activate()
Application.ScreenUpdating = False
ActiveSheet.Unprotect
Cells.Select
Selection.Locked = False
' Selection.FormulaHidden = False
Range("I:I,L:L").Locked = True
' Selection.FormulaHidden = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Range("A1").Select

' for zooming in at 85%
Call Zoomer
Application.ScreenUpdating = True
End Sub

MNJ
12-07-2006, 12:12 AM
Thanks for your reply as well as patience. Now the macro works like a treat! :super: