Select & Copy from a range but not edit

[Lost_On_Earth]

Hello Everyone

Wondering if anyone has come across this before. I have a Excel sheet which I want to protect except the a certain range in this range I want the user to be able to select the range & copy the data but I don't want to give them the option to edit.

I have tried playing around with the Protect Worksheet options in Excel but the closest I have got is they can select the range & copy BUT can also edit , the editing bit quite crucial I don't want to allow them to edit the cell. Just select & copy.

Does anyone know of a way I can achieve what i want?

Thanks

 

[SteveO59L]

what will the user do with the data once copied?

 

[Lost_On_Earth]

Thank you Steve well they could do what they want to it, paste to Powerpoint, another Excel or Word. If the copy/paste could work like it normally does from a unprotected sheet that would be ideal but I'm open to ideas. I know of a way to paste into PowerPoint which come across as a image but the users want more flexibility.

Thanks for you help!

 

[Eric Golf]

Hi,

When you protect the sheet, if the cells are locked and if you tick "Select locked cells" & "Select unlocked cells" options only then you should be able to select & copy but not edit?

Hope this helps,

Eric

 

[Lost_On_Earth]

Hello Eric not quite I'm afraid....

So there's a worksheet with a range say C5:I30 that's the range I want to allow copying only, anything outside of the range I don't want them to be able to select/copy at all. To achieve this I have to unlock my range of cell C5:I30, lock everything else outside this range & the protect the sheet with only 'Select Unlocked Cells' selected. This allows them to select the range (what I need), Not select outside the range (what I need) But crucially they can edit the edit the range I don't want to be able to edit the range at all.

Using your suggestion allows them to select the range but also outside the range too (something I really don't want to allow) they can copy & not edit. Its close what I need but not quite.

What a need is this:
Select the range ONLY
Copy the range
Cannot Edit the range at all

I've tried playing around with different combinations of protect/lock cells but I cant get it do what I need.

Thanks for your help!

 

[JerriNicci]

I know this thread is old, and my answer will likely be too late to help you, Lost_On_Earth, but thought I'd share my answer for others that might be searching for the same thing since this thread was at the top of my search, and I never came across a satisfactory answer that did not require VBA (VBA is disabled at work, and not an option).

First: I tried making locked cells selectable and marking any cell I didn't want them able to copy as hidden. Unfortunately, I didn't like how it made it harder to tell which cells in my form were unlocked, short of tabbing through. I also had problems with extra tabs or spaces or something being added to the end of the cell's contents when copied. So, I kept fiddling with it.

My solution: Have the cells unlocked, but set with data validation to only allow one choice. I turned off "ignore blank cells" and "in-cell dropdown". I also edited the Stop message to read something like, "this cell may not be altered or cleared". (Make sure it's a Stop message, not Warning or Information). Of course, it can be quite tedious to enter each cell's data validation one-by-one. To add data validation to all your cells at the same time, copy the range to a new Worksheet (or copy the whole sheet). Select all of the cells to set validation, and set a relative reference to the cells in the other sheet. For example, you selected C5:I30, and C5 is your "main" selected cell in Sheet1. You have copied the sheet as Sheet1Ref to refer to, and set Data Validation as "='Sheet1Ref'!C5" (note the lack of $ in a relative reference). Now, all the cells in the range will refer to their counterpart on the reference sheet. Just make sure, if you change something on the reference sheet, it does not automatically update the corresponding cell on the main sheet; you must then go and change it manually. Lock and hide the reference sheet, and you're good to go!

 

[GIS2013]

Hi JerriNicci,
Your solution really helped recently, and I didn't know about the option to make a reference in data validation..
Thanks