Protect Cell Formatting including Conditional Formatting
 

Hi all,

I'm working with a Government client in the UK to collect and then analyse
data from 400+ respondents. For a variety of reasons (too boring to discuss)
we couldn't use the web so after much discussion we've used Excel to provide
a questionnaire and a simple collation mechanism to extract the data from
multiple responses for analysis. To avoid security complications we have
agreed NOT to use macros. The audience is NOT assumed to be experienced with
Excel.

The questionnaire is pretty much finished, with lots of lookups (to
pre-complete forms where possible), lots of validation checks, very nicely
presented and with lots of hyperlink arrows to guide them through. Excellent
feedback from the initial sample. The workbook and sheets are protected
throughout.

The only fly in the ointment (though minor) is that if they copy a cell and
paste it into an input cell the conditional formatting for that cell is lost.
While this isn't a serious problem I'd rather stop it if possible.

Does anyone know a way to do this ? When the sheet is protected there are a
host of things which can be turned on/off including formatting. This is all
protected, but the paste operation seems to override the protection. I could
try and explain to users that they should use Paste-Special and Values, but
it's more complicated than I wanted to make it.

Protect Cell Formatting including Conditional Formatting
 

With your limitations, I think you'll find that it becomes a training issue.


Mick Jennings wrote:

Hi all,

I'm working with a Government client in the UK to collect and then analyse
data from 400+ respondents. For a variety of reasons (too boring to discuss)
we couldn't use the web so after much discussion we've used Excel to provide
a questionnaire and a simple collation mechanism to extract the data from
multiple responses for analysis. To avoid security complications we have
agreed NOT to use macros. The audience is NOT assumed to be experienced with
Excel.

The questionnaire is pretty much finished, with lots of lookups (to
pre-complete forms where possible), lots of validation checks, very nicely
presented and with lots of hyperlink arrows to guide them through. Excellent
feedback from the initial sample. The workbook and sheets are protected
throughout.

The only fly in the ointment (though minor) is that if they copy a cell and
paste it into an input cell the conditional formatting for that cell is lost.
While this isn't a serious problem I'd rather stop it if possible.

Does anyone know a way to do this ? When the sheet is protected there are a
host of things which can be turned on/off including formatting. This is all
protected, but the paste operation seems to override the protection. I could
try and explain to users that they should use Paste-Special and Values, but
it's more complicated than I wanted to make it.

--

Dave Peterson

Protect Cell Formatting including Conditional Formatting
 

OK thanks Dave, I figured as much. I did think of a hack, which works fine
for me. The cells to be copied are a list of Government Authorities. It's
important they get the name right (and spell it properly) as that's used for
lookups. If I copy the formatting from the input cell to the list of
authority names before I protect the sheet then the formatting gets copied
back in. It's a fudge, bit it works.

Cheers

"Dave Peterson" wrote:

With your limitations, I think you'll find that it becomes a training issue.


Mick Jennings wrote:

Hi all,

I'm working with a Government client in the UK to collect and then analyse
data from 400+ respondents. For a variety of reasons (too boring to discuss)
we couldn't use the web so after much discussion we've used Excel to provide
a questionnaire and a simple collation mechanism to extract the data from
multiple responses for analysis. To avoid security complications we have
agreed NOT to use macros. The audience is NOT assumed to be experienced with
Excel.

The questionnaire is pretty much finished, with lots of lookups (to
pre-complete forms where possible), lots of validation checks, very nicely
presented and with lots of hyperlink arrows to guide them through. Excellent
feedback from the initial sample. The workbook and sheets are protected
throughout.

The only fly in the ointment (though minor) is that if they copy a cell and
paste it into an input cell the conditional formatting for that cell is lost.
While this isn't a serious problem I'd rather stop it if possible.

Does anyone know a way to do this ? When the sheet is protected there are a
host of things which can be turned on/off including formatting. This is all
protected, but the paste operation seems to override the protection. I could
try and explain to users that they should use Paste-Special and Values, but
it's more complicated than I wanted to make it.

--

Dave Peterson

Protect Cell Formatting including Conditional Formatting
 

You may want to do some testing to see how the format|conditional formatting
rules are copied over.

Mick Jennings wrote:

OK thanks Dave, I figured as much. I did think of a hack, which works fine
for me. The cells to be copied are a list of Government Authorities. It's
important they get the name right (and spell it properly) as that's used for
lookups. If I copy the formatting from the input cell to the list of
authority names before I protect the sheet then the formatting gets copied
back in. It's a fudge, bit it works.

Cheers

"Dave Peterson" wrote:

With your limitations, I think you'll find that it becomes a training issue.


Mick Jennings wrote:

Hi all,

I'm working with a Government client in the UK to collect and then analyse
data from 400+ respondents. For a variety of reasons (too boring to discuss)
we couldn't use the web so after much discussion we've used Excel to provide
a questionnaire and a simple collation mechanism to extract the data from
multiple responses for analysis. To avoid security complications we have
agreed NOT to use macros. The audience is NOT assumed to be experienced with
Excel.

The questionnaire is pretty much finished, with lots of lookups (to
pre-complete forms where possible), lots of validation checks, very nicely
presented and with lots of hyperlink arrows to guide them through. Excellent
feedback from the initial sample. The workbook and sheets are protected
throughout.

The only fly in the ointment (though minor) is that if they copy a cell and
paste it into an input cell the conditional formatting for that cell is lost.
While this isn't a serious problem I'd rather stop it if possible.

Does anyone know a way to do this ? When the sheet is protected there are a
host of things which can be turned on/off including formatting. This is all
protected, but the paste operation seems to override the protection. I could
try and explain to users that they should use Paste-Special and Values, but
it's more complicated than I wanted to make it.

--

Dave Peterson


--

Dave Peterson

Protect Cell Formatting including Conditional Formatting
 

seems to work - wouldn't stop them from copying from another workbook tho

"Dave Peterson" wrote:

You may want to do some testing to see how the format|conditional formatting
rules are copied over.

Mick Jennings wrote:

OK thanks Dave, I figured as much. I did think of a hack, which works fine
for me. The cells to be copied are a list of Government Authorities. It's
important they get the name right (and spell it properly) as that's used for
lookups. If I copy the formatting from the input cell to the list of
authority names before I protect the sheet then the formatting gets copied
back in. It's a fudge, bit it works.

Cheers

"Dave Peterson" wrote:

With your limitations, I think you'll find that it becomes a training issue.


Mick Jennings wrote:

Hi all,

I'm working with a Government client in the UK to collect and then analyse
data from 400+ respondents. For a variety of reasons (too boring to discuss)
we couldn't use the web so after much discussion we've used Excel to provide
a questionnaire and a simple collation mechanism to extract the data from
multiple responses for analysis. To avoid security complications we have
agreed NOT to use macros. The audience is NOT assumed to be experienced with
Excel.

The questionnaire is pretty much finished, with lots of lookups (to
pre-complete forms where possible), lots of validation checks, very nicely
presented and with lots of hyperlink arrows to guide them through. Excellent
feedback from the initial sample. The workbook and sheets are protected
throughout.

The only fly in the ointment (though minor) is that if they copy a cell and
paste it into an input cell the conditional formatting for that cell is lost.
While this isn't a serious problem I'd rather stop it if possible.

Does anyone know a way to do this ? When the sheet is protected there are a
host of things which can be turned on/off including formatting. This is all
protected, but the paste operation seems to override the protection. I could
try and explain to users that they should use Paste-Special and Values, but
it's more complicated than I wanted to make it.

--

Dave Peterson


--

Dave Peterson

Protect Cell Formatting including Conditional Formatting
 

I wasn't sure what your rules are for your conditional formatting.

But glad it worked ok for you.

Mick Jennings wrote:

seems to work - wouldn't stop them from copying from another workbook tho

"Dave Peterson" wrote:

You may want to do some testing to see how the format|conditional formatting
rules are copied over.

Mick Jennings wrote:

OK thanks Dave, I figured as much. I did think of a hack, which works fine
for me. The cells to be copied are a list of Government Authorities. It's
important they get the name right (and spell it properly) as that's used for
lookups. If I copy the formatting from the input cell to the list of
authority names before I protect the sheet then the formatting gets copied
back in. It's a fudge, bit it works.

Cheers

"Dave Peterson" wrote:

With your limitations, I think you'll find that it becomes a training issue.


Mick Jennings wrote:

Hi all,

I'm working with a Government client in the UK to collect and then analyse
data from 400+ respondents. For a variety of reasons (too boring to discuss)
we couldn't use the web so after much discussion we've used Excel to provide
a questionnaire and a simple collation mechanism to extract the data from
multiple responses for analysis. To avoid security complications we have
agreed NOT to use macros. The audience is NOT assumed to be experienced with
Excel.

The questionnaire is pretty much finished, with lots of lookups (to
pre-complete forms where possible), lots of validation checks, very nicely
presented and with lots of hyperlink arrows to guide them through. Excellent
feedback from the initial sample. The workbook and sheets are protected
throughout.

The only fly in the ointment (though minor) is that if they copy a cell and
paste it into an input cell the conditional formatting for that cell is lost.
While this isn't a serious problem I'd rather stop it if possible.

Does anyone know a way to do this ? When the sheet is protected there are a
host of things which can be turned on/off including formatting. This is all
protected, but the paste operation seems to override the protection. I could
try and explain to users that they should use Paste-Special and Values, but
it's more complicated than I wanted to make it.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson