Allow users to select but not modify

I've got a spreadsheet which I want users to be able to select cells, and coloums for copying but lock them out for editing.

Ive tried protecting sheets but then the user cant select cells.

I did think about using VBA and on cell change finding the old value and inserting it into the cell so any changes will be lost, however cant find any way of referencing old data.

Does anyone have any ideas how to allow users to select but not modify?

Thank you
tonelm54Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rory ArchibaldCommented:
When you protect a sheet, the top two options are to allow selecting Locked cells and Unlocked cells. By default both should be checked and users should be able to select all cells.
0
tonelm54Author Commented:
But if you select that the user can still modify the cell contents
0
Rory ArchibaldCommented:
Not if you have the cells' Locked property set.
0
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

[ fanpages ]IT Services ConsultantCommented:
Hi,

I wouldn't interrupt rorya's discussion, but I wish to add to something you touched on in your question:

...cant find any way of referencing old data.

You would need to store the value of the cell selected in the Worksheet_SelectionChange() event code (in a [Private worksheet, or Public] variable), then reinstate this in the Worksheet_Change() event.

However, simply using the Visual Basic for Applications statement Application.Undo within the Worksheet_Change() event code should reverse an edit made to a cell (without the need to store the previous value).

BFN,

fp.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Don ThomsonCommented:
If you simply change the permissions on the Excel file to read only, for those readers that should mot be able to modify cells but can copy them, you should be able to do what you want. They could in effect modify the cells but any changes would not be saved
0
[ fanpages ]IT Services ConsultantCommented:
If you simply change the permissions on the Excel file to read only, for those readers that should mot be able to modify cells but can copy them, you should be able to do what you want. They could in effect modify the cells but any changes would not be saved

Unless they used [F12] "Save As", closed MS-Excel, deleted the original file, & renamed the newly created copy *.

Setting folder permissions on a network drive is, perhaps, a suggestion, though.

* Unless you meant permissions on files within a folder, rather than simple file system attributes.
0
Don ThomsonCommented:
Yes - I assumed that the file was on a network share and that the file permissions within a folder were set to read only.

Using Save-As of course is always possible - however I also have to assume that we are dealing with an issue that they don't wish employees to change the values in the file.

We have a quick solution to employees who would purposely change files by trying to go around the security/ It's call a "Pink Slip"
0
[ fanpages ]IT Services ConsultantCommented:
...It's call a "Pink Slip"

We call it a petticoat in the UK ;)
0
Don ThomsonCommented:
Call it anything you want - But you don't want that kind of employee working for you.

We've use the KISS principle for many thing that would cost an arm and a leg to implement from an IT point of view. We tell the client - Send out a memo that needs to be signed by each employee stating that these are the rules.  If you don't abide by the rules, you will be subject to termination without any further warning.  

This works great in areas where people were spending almost as much time texting or connected to social media then they were actually working.

It was simple - turn off your smart phone when you come through the door and don't use the company PCs for getting your Facebook  fix for the day. Else you are gone!
0
[ fanpages ]IT Services ConsultantCommented:
...subject to termination without any further warning.

Instant death is extreme, but it would certainly resolve the issue.
0
tonelm54Author Commented:
Sorry, this seems to have slipped away.

What I wanted users to do is able to select and modify certain cells (so making the file read-only wont work), but be able to select and copy other cells without being able to modify them.
0
Rory ArchibaldCommented:
You never replied to my suggestions: lock the cells you want locked, then protect the sheet but allow selecting locked cells.
0
[ fanpages ]IT Services ConsultantCommented:
PS. Re "ID: 39508813" & Application.Undo

I used this in a solution yesterday, if you wish to view it "in action":

[ http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Q_28259782.html#a39551996 ]
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.