Protect Top Header without Affecting Cell Value Options

Hi,

The attached spread sheet is exactly how I want it except I want the additional protection of preventing the user from accidentally changing the column titles or any of the data validation rules for the cells in each column.

So, how do I protect just the top column field titles without affecting the worksheet's current set up?

For example, when I try to lock the sheet, it affects the users ability to enter values as I want them to. I  want the user to be able to copy and paste values from A-I, but for J-X, I don't want them to be able to copy and paste values as I have already coded for.

But when I try to protect the sheet, it doesn't allow me to drag values for A-I anymore.

See attached. I hope this makes sense.

carlynne
ERA-Temp-2011-FINAL3.xlsm
CarlynneAsked:
Who is Participating?
 
SiddharthRoutConnect With a Mentor Commented:
>>>I think it would be best to protect all the cells and just allow them to enter data without cutting and pasting or dragging.

I usually do this with my workbooks where I don't want my users to change the formatting of the workbook.

Check the sample attached. You can edit values but you can't edit the column headers

Sid
ERA-Temp-2011-FINAL3.xlsm
0
 
SiddharthRoutCommented:
Select All Cells
Right Click And under protection tab, select 'unlocked'
Now select Row 1
Right Click And under protection tab, select 'locked'
Click on "Home" Tab
Click "Format" under "Cells" section
Click on protect Sheet
Enter password
Re-enter password
click ok
and you are done :)

Sid
0
 
SiddharthRoutCommented:
See the snapshots :)

Sid
Untitled-0.jpg
Untitled-0.5.jpg
Untitled1.jpg
0
Cloud Class® Course: 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.

 
CarlynneAuthor Commented:
Hi Sid,

Thanks for your response and for helpful screenshots.  

However, after protecting the worksheet, the user cannot cut and paste any values from columns A-I.  I was wondering if there's any  way to protect the worksheet without altering the current coding which allows user to cut and paste values in columns A-I, but not I-X.

Or maybe it's just not good practice to allow the user to cut and paste any values? I thought it would be good to allow them to cut and paste or drag values when they have hundreds of repeat value, say for the state or township columns, whereas I wanted to make sure for other columns that didn't drag values.

Please let me know your thoughts.

thanks!
carlynne
0
 
SiddharthRoutCommented:
If you want to allow cut and paste in Col A-I then you will have to select those columns and set their locked property to "False" Once that is done then select Row1 and set's locked property to 'True'.

Sid
0
 
CarlynneAuthor Commented:
I feel a bit like I am going in a circle now.  This is how I originally coded the spreadsheet to allow cutting and pasting of values for A-I and then disallowed for I-X.  So, I'm confused how protecting the column titles affected this coding.

Any ideas? I don't quite follow your instructions above as to whether they have to do with inserting new code or a method similar to the first one you described.

Many thanks!

carlynne
0
 
SiddharthRoutCommented:
>>>pasting of values for A-I and then disallowed for I-X.

carlynne:

1) Do you want to allow cutting and pasting of values for Col I?
2) What exact columns do you want to leave unprotected? I understand that Row 1 neds to be left protected.

Sid
0
 
CarlynneAuthor Commented:
Hi Sid,

Sorry for confusion.  I would like to allow cutting and pasting of values or dragging values from the above cell in columns A-H.  I don't want them to be able to do anything but enter data for I-X.

However, I am concerned if I leave A-H unprotected to allow them to be able to cut and paste, it will be possible that they accidentally change the column titles and/or the data validation rules.  If it's not possible to protect cells while allowing for cutting and pasting, I think it would be best to protect all the cells and just allow them to enter data without cutting and pasting or dragging.

Please let me know your thoughts.

I appreciate your help a lot!

Carlynne
0
 
CarlynneAuthor Commented:
Thanks.

Yeah, the more I think about, the more I think it would be better to just protect the entire worksheet.

Can you tell me what the password is on the worksheet you sent me? It seems to be password protected now.

carlynne
0
 
SiddharthRoutCommented:
Sorry.

The password is

1

Sid
0
 
CarlynneAuthor Commented:
Thanks!

Carlynne
0
 
CarlynneAuthor Commented:
Sid is always a tech lifeline for me!

carlynne
0
 
SiddharthRoutCommented:
Glad to be of help :)

Sid
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.