08-21-2020 02:34 PM
08-21-2020 02:34 PM
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:
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?
08-21-2020 04:44 PM
@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
08-21-2020 07:40 PM
@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.
08-21-2020 09:11 PM - edited 08-21-2020 09:14 PMSolution
@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.
08-22-2020 09:24 AM
@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!
08-22-2020 10:11 AM
@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.
08-22-2020 11:03 AM
@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 Sub
I 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.
08-22-2020 02:09 PM
@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.