View Single Post
  #11   Report Post  
Old June 28th 08, 12:05 AM posted to microsoft.public.excel.misc
Gord Dibben Gord Dibben is offline
external usenet poster
First recorded activity by ExcelBanter: Jul 2006
Posts: 22,908
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?