Selecting Some Locked Cells in the protected sheet...

[devbhatia]

Hi Chandoo,

This is my first ever query since i have been a BIGGG fan of you. Please help me out .

I have a spreadsheet in which I have some locked and some unlocked cells. I protected my sheet without the option of being able to select locked cells.


Is it possible for SOME of the cells that I locked to be selectable (in fact Copy able only) as well, but not editable, while keeping most of them unselectable??


For example, COLUMN A: LOCKED, COLUMN B: UNLOCKED, COLUMN C: JUST COPY ABLE (or SELECTABLE) ONLY


Thanks in advance!

 

[sgmpatnaik]

Hi Devbhatia


Firstly welcome to Chandoo.org Forums


you can restrict the access using with Protect Sheet and you can use the allow users to edit the ranges


how to use the restrict users


1 In the Ribbon you can find the Review


2 In the Review Button you can find Protect Sheet, Protect WorkBook, Share Workbook and Allow users to Edit Ranges


3 Select the Allow users to Edit Ranges just click the new button


4 In the Range Dialog Box you can add the Ranges to Edit the users


5 Now Protect the Sheet


With This option users can view, copy and paste in another cell but they can't change from protected cell's


Hope this will help you


Thanks


SP

 

[devbhatia]

Hi SP,


Thanks for your reply.


I used that way already. It allows editing fully but I just want few locked cells to be COPYABLE only and not editable.


Thanks,

Dev

 

[Luke M]

With built in capability, the answer is no. You can't make some of the protected cells selectable and others not selctable. If you really need this capability, I would suggest 2 ideas:


1. Setup some unprotected cells that are simply linked to the protected cells. Users can copy these cells and if they change it, won't affect your original data.

2. This one involves VBA. You unprotect the copyable cells, but then add some code to check if those cells get changed. Let's say the copyable cells are B2:B10. Worksheet module code (right-click on sheet tab, view code) would be:

[pre]

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B2:B10")) Is Nothing Then Exit Sub

'We're going to make a change, so turn this off
'so that macro doesn't get called infinitely
Application.EnableEvents = False
Application.Undo
MsgBox "Can't touch this!", vbCritical + vbOKOnly, "Hammer Time"
Application.EnableEvents = True
End Sub

[/pre]

 

[Stephen.Groenewald]

VAB work just as I needed it. Man you make us look like experts in our business. Thanks a ton.