Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old November 13th 07, 12:13 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2007
Posts: 1
Default 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.

  #2   Report Post  
Old November 13th 07, 01:23 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 35,218
Default 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
  #3   Report Post  
Old November 13th 07, 01:40 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2007
Posts: 4
Default 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

  #4   Report Post  
Old November 13th 07, 02:48 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 35,218
Default 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
  #5   Report Post  
Old November 13th 07, 03:03 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2007
Posts: 4
Default 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



  #6   Report Post  
Old November 13th 07, 05:32 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 35,218
Default 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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
conditional Formatting based on cell formatting Totom Excel Worksheet Functions 3 January 20th 07 02:02 PM
conditional Formatting based on cell formatting Totom Excel Worksheet Functions 0 January 15th 07 04:35 PM
Setting one cell = to another, INCLUDING FORMATTING. manxman Excel Discussion (Misc queries) 0 May 5th 06 05:20 PM
How to create a link including all formatting from origin cell? PlatinSam Excel Worksheet Functions 2 January 17th 06 06:45 PM
Protect Cell Formatting Bill Schnur Excel Worksheet Functions 1 March 23rd 05 02:53 AM


All times are GMT +1. The time now is 11:31 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017