Protect (Lock) Cell Formatting ONLY

  • Thread starter Thread starter GraceJean Jones
  • Start date Start date
G

GraceJean Jones

I understand how to protect a worksheet, and I know how to "unlock" certain
cells in a worksheet, so that users of the worksheet can enter information
into these unlocked cells. I have a worksheet where all I want the users to
be able to do in certain cells is to be able to 'put in or insert' numbers.
I don't want the users of this worksheet to be able to change the font, font
color, font size, borders, etc. In other words, I want to create a worksheet
where only numbers can be entered into certain cells, but no changes
whatsoever can be made to the cell formatting of these cells. Thanks for
your assistance.
 
I'm glad to hear that you know how to protect a sheet and how to lock and
unlock cells. You therefore know how to answer your question.
 
Thanks for your reply, however, my question was not answered. I want to be
able to create cells in a worksheet where users can input numbers, but cannot
change any of the formatting of the cell, such as font style, font size, font
color, borders, etc. Thanks again.
 
Hi GraceJean
I don't know what version of excel you are using but if you protect sheet,
you can't format,change fonts or resize etc... i am using excel3.
Regards
Cimjet
 
Which version of Excel are you using?
The cells that you want to change the values you have unlocked with Format/
Cells/ Unlock?
You have gone to Tools/ Protection/ Protect Sheet? The default at that
stage (at least in my Excel 2003) is to allow users to select cells, but not
to allow them to format cells, so that default will do what you want. Did
you change any of the options at that stage? What is your problem having
protected the sheet? Is it allowing users to change format although when
you protected the sheet you have not selected the option to allow
formatting? Is it not allowing users to select cells which you have
unlocked?
 
Thanks again for your response.

In the worksheet I have unlocked certain cells using like you said: Format/
Cells/ Unlock.

Then, like you said, I go to Tools/ Protection/ Protect Sheet.

If you allow users to "select cells," this option means the users of the
worksheet can put their cursor on "locked" cells & select them. This DOES
NOT mean that users, once selecting a "locked" cell can do anything with the
cell at all. If you do not allow users to "select cells," this then means
that the user of the worksheet cannot even get their cursor to move onto a
cell that has not been unlocked. If the user trys clicking on a "locked
cell" or using the arrows to move around, the cursor will just "jump" over
cells that have been "locked" (if the "select cells" if left unchecked). So,
in other words, by checking "select cells" all this does is it allows users
to get their cursor moved onto or over "locked" cells.

If I unlock a cell, users have access to this cell, to input numbers. But
the users unfortunately also have full access to change the color, to change
the font, to change the borders of the cell, etc.

I still cannot figure out a way to unlock a cell so that the only thing
users can do in this cell is input a number, & nothing else.

Thanks again for your feedback, if you know of something else I can try,
that would be great.
 
You failed to answer my first question:
"Which version of Excel are you using?"

In Excel 2003, and in any other version which I have used, the method which
I described does what you ask. I allow users to select cells, and that
allows them to change the content of, but not the format of, the unlocked
cells (but doesn't allow them to change unlocked cells). In whichever
version you have, are you saying that if you allow users to select locked
and unlocked cells (the default settings), then the unlocked cells behave
exactly the same as the locked cells? [Perhaps you'd better check again
that you have actually unlocked the cells that you think you've unlocked?]
I would be fascinated to hear if anyone else suffers from these same
symptoms.

Have a look at Excel help. The topic "About worksheet and workbook
protection" and the sub-topic "Protecting worksheet elements" address the
relevant area. If your Excel isn't behaving the way that it should do, you
may need to reinstall, but I would suggest checking again carefully that
you've got the settings correct on your cells and on your worksheet before
you resort to that drastic step.
 
Thanks again for your reply. Your time in trying to help is very much
appreciated.

First of all, I was using Excel 2003 at somebody else's computer. I do not
have Excel 2003 on my computer to test this all out.

However, I went to the MS website that you suggested. I have included a few
comments that I think might explain what is going on:

* By default the Select locked cells check box is selected. This check box
enables users to select cells with the Locked check box selected in the
Format Cells dialog box. When the Select locked cells check box is selected,
the Select unlocked cells check box is automatically selected.

* By default the Select unlocked cells check box is selected. This check
box enables users to select cells with the Locked check box cleared in the
Format Cells dialog box. When the Select unlocked cells check box is cleared,
the Select locked cells check box is automatically cleared. If there are no
unlocked cells on a protected sheet and this check box is not selected, users
cannot select any cells on the worksheet.

* Note: You cannot permit formatting of unlocked cells only.

Maybe you can test out the issue I am having (if you have a moment).
Unfortunately, this makes me kind of mad, because I'm not at a computer right
now that has Excel 2003 running, if I did, I would do this myself. However,
when I was at my friend's computer earlier this week, I'm pretty sure I
already did what I'm asking below. And...it did not work the way I wanted it
to. i.e., my friend wanted me to lock all but certain cells on a worksheet
that she was going to give to people to input numbers. My friend was not
happy, though, that the users of the worksheet could input numbers just fine
into unlocked cells, BUT...they also were able to change the formatting of
the cell (color, font, font size, borders, etc.), which she did not want them
to be able to do. We tried & tried & tried, we could not figure out a way to
do what she wanted.

Anyway, if you can try the following, you will see what I am talking about.

-Open Excel 2003
-Put your cursor in cell C3 & Highlight the block of cells C3 thru E5.
-Select Format/ Cells/ Unlock
-Select Tools/ Protection/ Protect Sheet
-Make sure the "Select locked cells" box is NOT checked

You will then see that all this does it that it does not allow you as the
user to get your cursor into any cells other than those that you previously
unlocked (i.e., the range of C3 thru E5) If your cursor is in cell E5 & you
hit the down arrow, your cursor will not move to cell E6, it will move to
cell C3. i.e., the user is not able to select any locked cells whatsoever
anywhere on the worksheet. The user will only be able to select cells that
have been previously unlocked.

If you are able to tell me the further steps at this point that I need to go
thru to allow users of this worksheet to be able to input numbers in to the
range of cells of C3 thru E5, but NOT be able to change any of the formatting
of these cells, please just pass along the additional steps that I need to
take to be able to do this.

I can call my friend and walk her thru the steps over the phone.

Again, your time in helping me out is greatly appreciated. If I can figure
this out, this will really be great, and will help us out a lot.

Thanks again.





David Biddulph said:
You failed to answer my first question:
"Which version of Excel are you using?"

In Excel 2003, and in any other version which I have used, the method which
I described does what you ask. I allow users to select cells, and that
allows them to change the content of, but not the format of, the unlocked
cells (but doesn't allow them to change unlocked cells). In whichever
version you have, are you saying that if you allow users to select locked
and unlocked cells (the default settings), then the unlocked cells behave
exactly the same as the locked cells? [Perhaps you'd better check again
that you have actually unlocked the cells that you think you've unlocked?]
I would be fascinated to hear if anyone else suffers from these same
symptoms.

Have a look at Excel help. The topic "About worksheet and workbook
protection" and the sub-topic "Protecting worksheet elements" address the
relevant area. If your Excel isn't behaving the way that it should do, you
may need to reinstall, but I would suggest checking again carefully that
you've got the settings correct on your cells and on your worksheet before
you resort to that drastic step.
--
David Biddulph

GraceJean Jones said:
Thanks again for your response.

In the worksheet I have unlocked certain cells using like you said:
Format/
Cells/ Unlock.

Then, like you said, I go to Tools/ Protection/ Protect Sheet.

If you allow users to "select cells," this option means the users of the
worksheet can put their cursor on "locked" cells & select them. This DOES
NOT mean that users, once selecting a "locked" cell can do anything with
the
cell at all. If you do not allow users to "select cells," this then means
that the user of the worksheet cannot even get their cursor to move onto a
cell that has not been unlocked. If the user trys clicking on a "locked
cell" or using the arrows to move around, the cursor will just "jump" over
cells that have been "locked" (if the "select cells" if left unchecked).
So,
in other words, by checking "select cells" all this does is it allows
users
to get their cursor moved onto or over "locked" cells.

If I unlock a cell, users have access to this cell, to input numbers. But
the users unfortunately also have full access to change the color, to
change
the font, to change the borders of the cell, etc.

I still cannot figure out a way to unlock a cell so that the only thing
users can do in this cell is input a number, & nothing else.

Thanks again for your feedback, if you know of something else I can try,
that would be great.
 
No more steps needed. As I said before, in that situation you can put
numbers in the unlocked cells but can't change the formatting.
--
David Biddulph

GraceJean Jones said:
Thanks again for your reply. Your time in trying to help is very much
appreciated.

First of all, I was using Excel 2003 at somebody else's computer. I do
not
have Excel 2003 on my computer to test this all out.

However, I went to the MS website that you suggested. I have included a
few
comments that I think might explain what is going on:

* By default the Select locked cells check box is selected. This check
box
enables users to select cells with the Locked check box selected in the
Format Cells dialog box. When the Select locked cells check box is
selected,
the Select unlocked cells check box is automatically selected.

* By default the Select unlocked cells check box is selected. This check
box enables users to select cells with the Locked check box cleared in the
Format Cells dialog box. When the Select unlocked cells check box is
cleared,
the Select locked cells check box is automatically cleared. If there are
no
unlocked cells on a protected sheet and this check box is not selected,
users
cannot select any cells on the worksheet.

* Note: You cannot permit formatting of unlocked cells only.

Maybe you can test out the issue I am having (if you have a moment).
Unfortunately, this makes me kind of mad, because I'm not at a computer
right
now that has Excel 2003 running, if I did, I would do this myself.
However,
when I was at my friend's computer earlier this week, I'm pretty sure I
already did what I'm asking below. And...it did not work the way I wanted
it
to. i.e., my friend wanted me to lock all but certain cells on a
worksheet
that she was going to give to people to input numbers. My friend was not
happy, though, that the users of the worksheet could input numbers just
fine
into unlocked cells, BUT...they also were able to change the formatting
of
the cell (color, font, font size, borders, etc.), which she did not want
them
to be able to do. We tried & tried & tried, we could not figure out a way
to
do what she wanted.

Anyway, if you can try the following, you will see what I am talking
about.

-Open Excel 2003
-Put your cursor in cell C3 & Highlight the block of cells C3 thru E5.
-Select Format/ Cells/ Unlock
-Select Tools/ Protection/ Protect Sheet
-Make sure the "Select locked cells" box is NOT checked

You will then see that all this does it that it does not allow you as the
user to get your cursor into any cells other than those that you
previously
unlocked (i.e., the range of C3 thru E5) If your cursor is in cell E5 &
you
hit the down arrow, your cursor will not move to cell E6, it will move to
cell C3. i.e., the user is not able to select any locked cells whatsoever
anywhere on the worksheet. The user will only be able to select cells
that
have been previously unlocked.

If you are able to tell me the further steps at this point that I need to
go
thru to allow users of this worksheet to be able to input numbers in to
the
range of cells of C3 thru E5, but NOT be able to change any of the
formatting
of these cells, please just pass along the additional steps that I need to
take to be able to do this.

I can call my friend and walk her thru the steps over the phone.

Again, your time in helping me out is greatly appreciated. If I can
figure
this out, this will really be great, and will help us out a lot.

Thanks again.





David Biddulph said:
You failed to answer my first question:
"Which version of Excel are you using?"

In Excel 2003, and in any other version which I have used, the method
which
I described does what you ask. I allow users to select cells, and that
allows them to change the content of, but not the format of, the unlocked
cells (but doesn't allow them to change unlocked cells). In whichever
version you have, are you saying that if you allow users to select locked
and unlocked cells (the default settings), then the unlocked cells behave
exactly the same as the locked cells? [Perhaps you'd better check again
that you have actually unlocked the cells that you think you've
unlocked?]
I would be fascinated to hear if anyone else suffers from these same
symptoms.

Have a look at Excel help. The topic "About worksheet and workbook
protection" and the sub-topic "Protecting worksheet elements" address the
relevant area. If your Excel isn't behaving the way that it should do,
you
may need to reinstall, but I would suggest checking again carefully that
you've got the settings correct on your cells and on your worksheet
before
you resort to that drastic step.
--
David Biddulph

GraceJean Jones said:
Thanks again for your response.

In the worksheet I have unlocked certain cells using like you said:
Format/
Cells/ Unlock.

Then, like you said, I go to Tools/ Protection/ Protect Sheet.

If you allow users to "select cells," this option means the users of
the
worksheet can put their cursor on "locked" cells & select them. This
DOES
NOT mean that users, once selecting a "locked" cell can do anything
with
the
cell at all. If you do not allow users to "select cells," this then
means
that the user of the worksheet cannot even get their cursor to move
onto a
cell that has not been unlocked. If the user trys clicking on a
"locked
cell" or using the arrows to move around, the cursor will just "jump"
over
cells that have been "locked" (if the "select cells" if left
unchecked).
So,
in other words, by checking "select cells" all this does is it allows
users
to get their cursor moved onto or over "locked" cells.

If I unlock a cell, users have access to this cell, to input numbers.
But
the users unfortunately also have full access to change the color, to
change
the font, to change the borders of the cell, etc.

I still cannot figure out a way to unlock a cell so that the only thing
users can do in this cell is input a number, & nothing else.

Thanks again for your feedback, if you know of something else I can
try,
that would be great.

:

Which version of Excel are you using?
The cells that you want to change the values you have unlocked with
Format/
Cells/ Unlock?
You have gone to Tools/ Protection/ Protect Sheet? The default at
that
stage (at least in my Excel 2003) is to allow users to select cells,
but
not
to allow them to format cells, so that default will do what you want.
Did
you change any of the options at that stage? What is your problem
having
protected the sheet? Is it allowing users to change format although
when
you protected the sheet you have not selected the option to allow
formatting? Is it not allowing users to select cells which you have
unlocked?
--
David Biddulph

message Thanks for your reply, however, my question was not answered. I
want
to
be
able to create cells in a worksheet where users can input numbers,
but
cannot
change any of the formatting of the cell, such as font style, font
size,
font
color, borders, etc. Thanks again.

:

I'm glad to hear that you know how to protect a sheet and how to
lock
and
unlock cells. You therefore know how to answer your question.
--
David Biddulph

in
message I understand how to protect a worksheet, and I know how to
"unlock"
certain
cells in a worksheet, so that users of the worksheet can enter
information
into these unlocked cells. I have a worksheet where all I want
the
users
to
be able to do in certain cells is to be able to 'put in or
insert'
numbers.
I don't want the users of this worksheet to be able to change the
font,
font
color, font size, borders, etc. In other words, I want to create
a
worksheet
where only numbers can be entered into certain cells, but no
changes
whatsoever can be made to the cell formatting of these cells.
Thanks
for
your assistance.
 
I am having a similar problem. I can protect cells so that the user cannot select them, and unprotect the data-entry cells so that the user can enter data. "Format cells" is unchecked, so the "Format cells" option in the right-click menu is greyed out, and the "Cells" option on the "Format" menu is likewise greyed out. However, if the user copies one data-entry cell and pastes it into another, the formatting is pasted too.

To demonstrate this:
1. Format column A as text, column B as date and column C as number. On the Protection tab, uncheck "locked".

2. Protect the sheet. Make sure "Format Cells" is unchecked.

3. Select a cell in column A and copy it to the clipboard with <Ctrl-C>.

4. Paste that cell into cells B1 and C1.

5. Turn sheet protection off.

6. Right-click on cell B1 and select "Format cells". The format has been changed to Text. Likewise for C1.

How can I protect cells so that pasting another cell will only paste the source cell's value but not its formatting?
 
Private Sub Worksheet_Change(ByVal Target As Range)
'retain target cell formatting when a cell is copied over
Dim myValue
With Application
.EnableEvents = False
myValue = Target.Value
.Undo
Target = myValue
.EnableEvents = True
End With
End Sub


Gord Dibben MS Excel MVP
 
Try http://www.eggheadcafe.com/software/aspnet/33295991/formatting-not-protected-when-paste.aspx

A neat VBA solution that locks out ALL format changes for a sheet or a workbook.

Mike
I understand how to protect a worksheet, and I know how to "unlock" certain
cells in a worksheet, so that users of the worksheet can enter information
into these unlocked cells. I have a worksheet where all I want the users to
be able to do in certain cells is to be able to 'put in or insert' numbers.
I don't want the users of this worksheet to be able to change the font, font
color, font size, borders, etc. In other words, I want to create a worksheet
where only numbers can be entered into certain cells, but no changes
whatsoever can be made to the cell formatting of these cells. Thanks for
your assistance.
On Monday, February 25, 2008 5:14 PM David Biddulph wrote:
I am glad to hear that you know how to protect a sheet and how to lock and
unlock cells. You therefore know how to answer your question.
"Which version of Excel are you using?"

In Excel 2003, and in any other version which I have used, the method which
I described does what you ask. I allow users to select cells, and that
allows them to change the content of, but not the format of, the unlocked
cells (but doesn't allow them to change unlocked cells). In whichever
version you have, are you saying that if you allow users to select locked
and unlocked cells (the default settings), then the unlocked cells behave
exactly the same as the locked cells? [Perhaps you'd better check again
that you have actually unlocked the cells that you think you've unlocked?]
I would be fascinated to hear if anyone else suffers from these same
symptoms.

Have a look at Excel help. The topic "About worksheet and workbook
protection" and the sub-topic "Protecting worksheet elements" address the
relevant area. If your Excel isn't behaving the way that it should do, you
may need to reinstall, but I would suggest checking again carefully that
you've got the settings correct on your cells and on your worksheet before
you resort to that drastic step.
 
Hi, I'm new to this group and have exactly the same issue. I need to protect certain aspects of the sheet, but allow users to input simple data into unlocked cells. These unlocked cells have formats that I still want to protect.
I have found that if you Edit->Clear->All, this wipes everything including cell formats, cell merges, font, etc.
Does anyone know how to overcome this without macros (unfortunately enabling macros on the PCs using this worksheet is a bigger hassle).
I'm particularly interested in an earlier posting about protecting the workbook structure. This didn't seem to have any effect on my sheet. Anyone else have better luck.

Submitted via EggHeadCafe - Software Developer Portal of Choice
Auto-Generate Code for LINQ to SQL Repository Pattern using T4
http://www.eggheadcafe.com/tutorial...-linq-to-sql-repository-pattern-using-t4.aspx
 
Does anyone know how to overcome this without macros

Sorry but it's not possible. Even with macros you're at the mercy of the
user enabling them. And workbook structure protection has nothing to do
with pasting.
 
Back
Top