LinkBack Thread Tools Search this Thread Display Modes
  #11   Report Post  
Old June 28th 08, 12:05 AM posted to microsoft.public.excel.misc
external usenet poster
First recorded activity by ExcelBanter: Jul 2006
Posts: 22,907
Default Protecting cell 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
Target = myValue
.EnableEvents = True
End With
End Sub

Gord Dibben MS Excel MVP

On Thu, 26 Jun 2008 11:19:23 -0700, Sam Hills wrote:

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?

  #12   Report Post  
Old January 9th 19, 09:11 AM posted to microsoft.public.excel.misc
external usenet poster
First recorded activity by ExcelBanter: Jan 2019
Posts: 1
Default Protect (Lock) Cell Formatting ONLY

Ok so I had similar problem and at least for me what solved most of it is applying a catch all conditional format,that basically always amounts to true this overrides anything the user might do color wise at least as far as font size ive still got nothing lol

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
Protect Cell Formatting including Conditional Formatting Mick Jennings Excel Discussion (Misc queries) 5 November 13th 07 05:32 PM
How do I lock/protect cells so they can't be changed Julie Excel Worksheet Functions 3 November 4th 06 03:49 AM
how to only protect/lock cell formats? Glen Excel Discussion (Misc queries) 0 October 10th 06 01:19 AM
Protect/Lock Specific Cells srf1030 Excel Discussion (Misc queries) 6 July 6th 05 08:05 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 02:44 PM.

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

About Us

"It's about Microsoft Excel"


Copyright © 2017