Forum Discussion
Unable to lock cell formatting
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.
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 Sub
mtarler 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.
mtarler I've been thinking more about your solution.... Repairing the formatting after a paste would be easier if each cell somehow "remembered" its own proper formatting. This could be done by storing a duplicate of the original data area (with properly formatted cells) on a private worksheet. Then Worksheet_Change would simply access the correct formatting from the private worksheet and apply it to the each cell of the target.
