?
Solved

Protect Top Header without Affecting Cell Value Options

Posted on 2011-04-20
13
Medium Priority
?
285 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:Carlynne
  • 7
  • 6
13 Comments
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35431272
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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35431318
See the snapshots :)

Sid
Untitled-0.jpg
Untitled-0.5.jpg
Untitled1.jpg
0
 

Author Comment

by:Carlynne
ID: 35431483
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35431491
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
 

Author Comment

by:Carlynne
ID: 35431513
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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35434765
>>>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
 

Author Comment

by:Carlynne
ID: 35437967
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
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 2000 total points
ID: 35438131
>>>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
 

Author Comment

by:Carlynne
ID: 35438171
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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35438174
Sorry.

The password is

1

Sid
0
 

Author Comment

by:Carlynne
ID: 35438234
Thanks!

Carlynne
0
 

Author Closing Comment

by:Carlynne
ID: 35438235
Sid is always a tech lifeline for me!

carlynne
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35438238
Glad to be of help :)

Sid
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

755 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question