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.
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.
perkin_warbeck If that is your "fixed" formatting that should be pretty easy to "hard code" into the VBA. You idea of making a copy to a hidden sheet would work and as long as it isn't too big a file shouldn't cause too much delay. Here is an example of my last suggestion above where you lock the entire sheet and just paste the data where ever they click. It isn't "perfect" but could work for you:
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Const noPasteCell = "$A$1" confirm = MsgBox("Paste your data here?", vbYesNo) On Error GoTo fail If confirm = vbYes Then Application.EnableEvents = False ActiveSheet.Unprotect Target.PasteSpecial (xlPasteValues) ActiveSheet.Protect End If fail: Range(noPasteCell).Select Application.EnableEvents = True If Err Then MsgBox ("Paste Failed. Check your copy and try again.") Err.Clear End If End SubI defined "noPasteCell" as A1 just as a place to auto move the selection to so it is intuitive for them to click where they want to paste the data. You could add additional checks to make sure they aren't pasting data in incorrect areas. You could also add a Password to prevent them from circumventing it if you wanted.
mtarler I just lost a long reply because "authentication failed" when I posted. So I'll re-type an abbreviated version. I decided to go with partial locking, rather than full sheet locking. Paste fails if the clipboard data would overlap onto locked cells, which is a very desirable behavior. There is no partial paste. Since Excel is protecting the worksheet, there is no need to take the extra precautions in your example.
There are also controls on the worksheet (screenshot). I unlocked these so they can be used when the sheet is protected. Since those controls call macros that modify locked cells, the macros will have to temporarily unprotect the sheet.
mtarler I should have mentioned that I am very new to VBA, so I'm trying out most of these things for the first time. I think you have pointed me in a a couple of good directions.
Repairing the formatting is certainly one way to do it. I don't mind the application being complex if it spares the user from having to remember to Paste Values.
But you say "You can also use on selection in VBA to determine where they want to paste the data and then use VBA to paste values." It's an interesting solution, but it would require another button to accomplish the paste. I'd like users to be able to paste normally.
I will mark your response as "Best Response" because you have given me several good ideas. Thanks!
