In excel how do I protect the format of a cell but let the user change the
cell value?

once the format is set the data entered will match the format
Cheers peterm

"John Louis" wrote:

In excel how do I protect the format of a cell but let the user change the
cell value?

I want to protect the cell format like in a sheet protection of a cell, so
that for other users can change the content of the cell but not use 'Format
Cell Number' to chage the format let say from a time to a %

"John Louis" wrote:

In excel how do I protect the format of a cell but let the user change the
cell value?

If you unlock the cell or range of cells and protect the worksheet, you also
protect the format. Excel has an option to allow users to change formats in
protected cells, which you can tick if you want to allow this.

Cheers,

Kassie

"John Louis" wrote:

I want to protect the cell format like in a sheet protection of a cell, so
that for other users can change the content of the cell but not use 'Format
Cell Number' to chage the format let say from a time to a %

"John Louis" wrote:

In excel how do I protect the format of a cell but let the user change the
cell value?

John

If using Excel 2002 or 2003 and ToolsProtectionProtect Sheet you will find
options to tick on or off.

"Format Cell" is one of these options.

Unlock the cell for data entry then protect the sheet.


Gord Dibben Excel MVP

On Tue, 22 Feb 2005 02:49:02 -0800, "John Louis"
wrote:

I want to protect the cell format like in a sheet protection of a cell, so
that for other users can change the content of the cell but not use 'Format
Cell Number' to chage the format let say from a time to a %

"John Louis" wrote:

In excel how do I protect the format of a cell but let the user change the
cell value?

Gord,
That works for protecting the overall format within the worksheet; however,
if you cut and paste cells from another worksheet or workbook the formatting
gets changed regardless of format protection settings. Is there any way to
lock down and protect cell formatting so that the user cannot change it using
a cut and paste method? I am trying to limit cell entry strictly to values
and number formats. Any ideas?
- Yorck

"Gord Dibben" wrote:

John

If using Excel 2002 or 2003 and ToolsProtectionProtect Sheet you will find
options to tick on or off.

"Format Cell" is one of these options.

Unlock the cell for data entry then protect the sheet.


Gord Dibben Excel MVP

On Tue, 22 Feb 2005 02:49:02 -0800, "John Louis"
wrote:

I want to protect the cell format like in a sheet protection of a cell, so
that for other users can change the content of the cell but not use 'Format
Cell Number' to chage the format let say from a time to a %

"John Louis" wrote:

In excel how do I protect the format of a cell but let the user change the
cell value?

Gord,
That works if you want to protect the cell formatting from the user trying
to change it within the same worksheet. Nevertheless, if a user cuts and
pastes cells from another worksheet or workbook the formatting is altered
regardless of format protection settings setup on the worksheet. Is there
anyway to shut-down the loophole so users cannot alter cell formatting by
using cut and paste, but can still enter value and number formats into the
cell?
- Yorck

"Gord Dibben" wrote:

John

If using Excel 2002 or 2003 and ToolsProtectionProtect Sheet you will find
options to tick on or off.

"Format Cell" is one of these options.

Unlock the cell for data entry then protect the sheet.


Gord Dibben Excel MVP

On Tue, 22 Feb 2005 02:49:02 -0800, "John Louis"
wrote:

I want to protect the cell format like in a sheet protection of a cell, so
that for other users can change the content of the cell but not use 'Format
Cell Number' to chage the format let say from a time to a %

"John Louis" wrote:

In excel how do I protect the format of a cell but let the user change the
cell value?

Yorck

Check out this google search thread.

http://snipurl.com/dn81

Dave Peterson's reply in particular for a workaround.


Gord Dibben Excel MVP

On Thu, 24 Mar 2005 14:31:05 -0800, "Yorck"
wrote:

Gord,
That works for protecting the overall format within the worksheet; however,
if you cut and paste cells from another worksheet or workbook the formatting
gets changed regardless of format protection settings. Is there any way to
lock down and protect cell formatting so that the user cannot change it using
a cut and paste method? I am trying to limit cell entry strictly to values
and number formats. Any ideas?
- Yorck

"Gord Dibben" wrote:

John

If using Excel 2002 or 2003 and ToolsProtectionProtect Sheet you will find
options to tick on or off.

"Format Cell" is one of these options.

Unlock the cell for data entry then protect the sheet.


Gord Dibben Excel MVP

On Tue, 22 Feb 2005 02:49:02 -0800, "John Louis"
wrote:

I want to protect the cell format like in a sheet protection of a cell, so
that for other users can change the content of the cell but not use 'Format
Cell Number' to chage the format let say from a time to a %

"John Louis" wrote:

In excel how do I protect the format of a cell but let the user change the
cell value?

Gord
I also want to be able to stop the formatting change in a protected sheet if
cutting and pasting cells from another worksheet or workbook. I was not able
to access the google search thread and so any suggestions you can give me
would be great.

Thank you
Doreen D

"Gord Dibben" wrote:

Yorck

Check out this google search thread.

http://snipurl.com/dn81

Dave Peterson's reply in particular for a workaround.


Gord Dibben Excel MVP

On Thu, 24 Mar 2005 14:31:05 -0800, "Yorck"
wrote:

Gord,
That works for protecting the overall format within the worksheet; however,
if you cut and paste cells from another worksheet or workbook the formatting
gets changed regardless of format protection settings. Is there any way to
lock down and protect cell formatting so that the user cannot change it using
a cut and paste method? I am trying to limit cell entry strictly to values
and number formats. Any ideas?
- Yorck

"Gord Dibben" wrote:

John

If using Excel 2002 or 2003 and ToolsProtectionProtect Sheet you will find
options to tick on or off.

"Format Cell" is one of these options.

Unlock the cell for data entry then protect the sheet.


Gord Dibben Excel MVP

On Tue, 22 Feb 2005 02:49:02 -0800, "John Louis"
wrote:

I want to protect the cell format like in a sheet protection of a cell, so
that for other users can change the content of the cell but not use 'Format
Cell Number' to chage the format let say from a time to a %

"John Louis" wrote:

In excel how do I protect the format of a cell but let the user change the
cell value?

Doreen

I don't know why you can't access the URL I posted.

http://snipurl.com/dn81

I tried it a minute ago and had no problem. Try it again yourself.

Dave's suggestion was to use a mirrored worksheet with the original formatting
and event code to copy those formats back to original whenever a change is
made or upon saving the workbook.

He posted a couple of different sets of code to cover contingencies.

Of course, macros can be disabled and the "undo" stack is cleared when macro
is run so some disadvantages appear.


Gord

On Thu, 29 Sep 2005 19:09:01 -0700, "Doreen D" <Doreen
wrote:

Gord
I also want to be able to stop the formatting change in a protected sheet if
cutting and pasting cells from another worksheet or workbook. I was not able
to access the google search thread and so any suggestions you can give me
would be great.

Thank you
Doreen D

"Gord Dibben" wrote:

Yorck

Check out this google search thread.

http://snipurl.com/dn81

Dave Peterson's reply in particular for a workaround.


Gord Dibben Excel MVP

On Thu, 24 Mar 2005 14:31:05 -0800, "Yorck"
wrote:

Gord,
That works for protecting the overall format within the worksheet; however,
if you cut and paste cells from another worksheet or workbook the formatting
gets changed regardless of format protection settings. Is there any way to
lock down and protect cell formatting so that the user cannot change it using
a cut and paste method? I am trying to limit cell entry strictly to values
and number formats. Any ideas?
- Yorck

"Gord Dibben" wrote:

John

If using Excel 2002 or 2003 and ToolsProtectionProtect Sheet you will find
options to tick on or off.

"Format Cell" is one of these options.

Unlock the cell for data entry then protect the sheet.


Gord Dibben Excel MVP

On Tue, 22 Feb 2005 02:49:02 -0800, "John Louis"
wrote:

I want to protect the cell format like in a sheet protection of a cell, so
that for other users can change the content of the cell but not use 'Format
Cell Number' to chage the format let say from a time to a %

"John Louis" wrote:

In excel how do I protect the format of a cell but let the user change the
cell value?

Gord
Thanks for that. I tried it this time and got through to it. Maybe it was
timing from when you pasted it originally and I tried it. Thanks very much
for that. I will give that a go and see if that works.
Doreen

"Gord Dibben" wrote:

Doreen

I don't know why you can't access the URL I posted.

http://snipurl.com/dn81

I tried it a minute ago and had no problem. Try it again yourself.

Dave's suggestion was to use a mirrored worksheet with the original formatting
and event code to copy those formats back to original whenever a change is
made or upon saving the workbook.

He posted a couple of different sets of code to cover contingencies.

Of course, macros can be disabled and the "undo" stack is cleared when macro
is run so some disadvantages appear.


Gord

On Thu, 29 Sep 2005 19:09:01 -0700, "Doreen D" <Doreen
wrote:

Gord
I also want to be able to stop the formatting change in a protected sheet if
cutting and pasting cells from another worksheet or workbook. I was not able
to access the google search thread and so any suggestions you can give me
would be great.

Thank you
Doreen D

"Gord Dibben" wrote:

Yorck

Check out this google search thread.

http://snipurl.com/dn81

Dave Peterson's reply in particular for a workaround.


Gord Dibben Excel MVP

On Thu, 24 Mar 2005 14:31:05 -0800, "Yorck"
wrote:

Gord,
That works for protecting the overall format within the worksheet; however,
if you cut and paste cells from another worksheet or workbook the formatting
gets changed regardless of format protection settings. Is there any way to
lock down and protect cell formatting so that the user cannot change it using
a cut and paste method? I am trying to limit cell entry strictly to values
and number formats. Any ideas?
- Yorck

"Gord Dibben" wrote:

John

If using Excel 2002 or 2003 and ToolsProtectionProtect Sheet you will find
options to tick on or off.

"Format Cell" is one of these options.

Unlock the cell for data entry then protect the sheet.


Gord Dibben Excel MVP

On Tue, 22 Feb 2005 02:49:02 -0800, "John Louis"
wrote:

I want to protect the cell format like in a sheet protection of a cell, so
that for other users can change the content of the cell but not use 'Format
Cell Number' to chage the format let say from a time to a %

"John Louis" wrote:

In excel how do I protect the format of a cell but let the user change the
cell value?

The problem also exists if you copy data from one cell to another within the
sheet. I have a sheet in which unlocked cells are used for data entry. As
much of the data entry is often repeated, users tend to make a single data
entry then copy and paste it throughout the sheet as required. This causes
chaos with formatting of cells as the formatting is also copied. If anyone
from Microsoft ever reads this I think this is a basic problem that needs
fixing in the next update of Excel. Locking formatting does not help.
--
A Human


"Yorck" wrote:

Gord,
That works if you want to protect the cell formatting from the user trying
to change it within the same worksheet. Nevertheless, if a user cuts and
pastes cells from another worksheet or workbook the formatting is altered
regardless of format protection settings setup on the worksheet. Is there
anyway to shut-down the loophole so users cannot alter cell formatting by
using cut and paste, but can still enter value and number formats into the
cell?
- Yorck

"Gord Dibben" wrote:

John

If using Excel 2002 or 2003 and ToolsProtectionProtect Sheet you will find
options to tick on or off.

"Format Cell" is one of these options.

Unlock the cell for data entry then protect the sheet.


Gord Dibben Excel MVP

On Tue, 22 Feb 2005 02:49:02 -0800, "John Louis"
wrote:

I want to protect the cell format like in a sheet protection of a cell, so
that for other users can change the content of the cell but not use 'Format
Cell Number' to chage the format let say from a time to a %

"John Louis" wrote:

In excel how do I protect the format of a cell but let the user change the
cell value?