Forum Discussion
Unable to lock cell formatting
When a user pastes data onto a worksheet, how can I prevent the pasted data from erasing the formatting (cell borders, background color, number formats, etc.)?
The following method (which I found in several places on the web) does not work:
- Select all cells on the worksheet
- Home tab -> Cells group -> Format -> Format Cells... -> uncheck "Locked" -> OK
- Home tab -> Cells group -> Format -> Protect Sheet -> make sure "format cells" is unchecked" and do not enter a password -> OK (see screenshot below)
After doing this, the formatting groups in the Home tab are all grayed out (good). However, when I paste into the sheet, the formatting in the pasted range is erased. What am I doing wrong?
perkin_warbeck yes you are correct and I'm sure you read my sample code. I delete any formats they brought in and then added the new formats. I apply the new formats to all pasted cells but sounds like you have to be more selective.
How about a completely different approach where you have them paste the data in a different sheet and then you use VBA to copy and paste values only.
Or if they past in a different sheet then you just use a formula to reference that data from the sheet with the formats.
You can also use on selection in VBA to determine where they want to paste the data and then use VBA to paste values or give them a pop-up. You can lock the entire sheet but allow selection so they can paste anywhere but VBA would do the work instead.
There are many ways to skin a cat in excel, it's hard to suggest the best option for you without knowing more about the application.
7 Replies
perkin_warbeck I don't know that you can get around that. You could do it using VBA. I don't know what sort of formatting you have but here is a sample of what you might do in VBA (I assume you still lock the worksheet to make sure they only paste where you want them to paste). The following VBA would be added to the specific sheet you want to control in VBA.
Private Sub Worksheet_Change(ByVal Target As Range) ActiveSheet.Unprotect Application.EnableEvents = False With Target .ClearFormats .Font.FontStyle = "Times New Roman" .Font.Bold = True .Interior.Color = RGB(250, 250, 0) .Locked = False End With Application.EnableEvents = True ActiveSheet.Protect End Submtarler My application is written entirely in VBA, and I am already handling Worksheet_Change events. My understanding is that Worksheet_Change is called AFTER the change occurs. In that case, the code would repair formats erased by the paste. I will consider doing it that way, but it will be messy because the user might paste into several columns of data, and each column has a different background color. I was hoping to avoid that.
perkin_warbeck yes you are correct and I'm sure you read my sample code. I delete any formats they brought in and then added the new formats. I apply the new formats to all pasted cells but sounds like you have to be more selective.
How about a completely different approach where you have them paste the data in a different sheet and then you use VBA to copy and paste values only.
Or if they past in a different sheet then you just use a formula to reference that data from the sheet with the formats.
You can also use on selection in VBA to determine where they want to paste the data and then use VBA to paste values or give them a pop-up. You can lock the entire sheet but allow selection so they can paste anywhere but VBA would do the work instead.
There are many ways to skin a cat in excel, it's hard to suggest the best option for you without knowing more about the application.
