Protect column in Shared workbook

[rsk]

Hi,


In shared workbook i will assign task to my team members daily basis. In column B i assign the processor name for eg: AAA, BBB, CCC, etc

Some people reassigning the project from others without my knowledge so i want to protect "B" column alone from other users. kindly help on this.


Eg:

Project id Assigned to Status Tested

123 AAA Completed Yes

124 BBB Completed Yes

125 CCC Completed Yes

126 DDD Completed Yes

127 EEE Completed Yes

128 FFF Completed Yes


I want to protect B column from other user but i have control to change the same.


Thanks and Regards

RSK

 

[SirJB7]

Hi, rsk!

I don't remember in previous versions of Excel, but in 2010 you can go to Review tab (I think it's Review, in spanish is Revisar, it's located between Data and View), then 'Let the users change ranges' (or something like "Permitir que los usuarios modifiquen rangos"), and there you can associate ranges with passwords for updating, so they can't modify column B in this case, but you'll have to enter the pwd to make the assignments.

Regards!

 

[rsk]

Thanks for your reply...

I tried this step but in shared workbook once i entered the password i cant protect the column again.... i need to close the workbook and reopen then only its locked.

Any way i will use this until get some more advance than this


Thank you once again


Is there any way to protect this using VBA ???

 

[SirJB7]

Hi, rsk!

I didn't know that such a thing happens when sharing workbooks. I can't check it now, my notebook is waiting for a HD change... over my desk.

Have you tried the optional button 'Permisos' (I guess 'Access' or 'Permissions') at the bottom left of the window where you define the range and assign the password? It leds you to a new window where you can specify users or groups of users (I bet for this, you may have groups defined into your organisation) and below you have to checkboxes for 'Modify range without password', Let and Deny. Try assigning one group or user and check Deny, maybe it works.

Using VBA it's easy to protect a range (in this case column B, it'll be 'Activesheet.Columns(2).Protect "XXXX"', or something like that, if you can't find it out, I'll try and tell you. Wait a minute now. It's like this:

Sub ProtectColumnB()

Columns("B").Select

Selection.Locked = True

Selection.FormulaHidden = False

ActiveSheet.Protect Password:="XXX", DrawingObjects:=True, Contents:=True, Scenarios:=True

End Sub

Sub UnprotectColumnB()

Activesheet.Unprotect Password:="XXX"

Columns("B").Select

Selection.Locked = False

Selection.FormulaHidden = False

End Sub

Or a combination of both in one that changes the state from protected to unprotected and so on. You can assign it to a CommandButton or execute if from the Macro menu, Alt-F8.

But I'm now wondering that you'll have to add something like this:

If InputBox("Authorization for changing protection of column B", "Message title") = XXX Then

call to sub protect/unprotect

Else

Exit Sub

End If

And protect the VB Project with password, and don't enter it while in shared mode. Maybe it happens like with protecting ranges before.

Give that a chance, and good luck! Else write again. Loop Until you get it!

Regards!

 

[rsk]

Thank you very much for your reply.


I will check this and let you know....


Regards

RSK