0

I have a worksheet containing a column of numbers. The column is formatted with a background color, number formats, etc. The column is unlocked. I protect the sheet manually by right-clicking on the tab and selecting Protect. In the Protect Sheet dialog, 'format cells' is unchecked. I interpret to mean that the user should not be able to format cells. Yet when the user pastes into the column, formats are pasted along with values.

If I protect the sheet in VBA using sh.Protect UserInterfaceOnly:=True I get the same result: formats are pasted. I do not specify AllowFormattingCells:=False because the default is False.

I have seen posts suggesting that formats can be restored by copying and pasting them from a shadow area. I have used this solution before I started protecting worksheets and found it overly complex. I had hoped this was something protection would handle. If there is a way to handle it, I'd like to do it in VBA.

aynber
  • 22,380
  • 8
  • 50
  • 63
jisner
  • 13
  • 3

2 Answers2

1

This question is a little old, but I had the same one, and so have many people in the past. With a bit of browsing I came up with a solution which seems quite clean and appears to work. Am I missing something?

Private Sub Worksheet_Change(ByVal rngTarget As Range)

Dim vPaste As Variant

With Application.CommandBars("Standard").Controls("&Undo")
    If Not .Enabled Then Exit Sub
    If .ListCount < 1 Then Exit Sub
    If .List(1) <> "Paste" Then Exit Sub
End With

vPaste = rngTarget.Value2

On Error Resume Next
Application.EnableEvents = False
Application.Undo
rngTarget.Value2 = vPaste
Application.EnableEvents = True
On Error GoTo 0

End Sub

This could go in Workbook_SheetChange, but not if you already have code in Worksheet_Change, because the worksheet event handler gets called before the workbook event handler. But this code can go in a module to keep things tidy.

Boddle
  • 13
  • 4
0

There is no built-in protection option you can use to achieve your desired result.

The only thing that works in this case is the clunky workaround that you mention, i.e. use a Worksheet_Change event that ensures the correct format after a cell has been modified.

Since there are many different ways to paste content, i.e. via various menus, ribbon commands, keyboard shortcuts, etc., any VBA solution that tries to intercept pasting will become very complex, much more complex than the change event that restores the format to its original state.

Another option might be user education and training (so they know to paste values only), although user behaviour may be the toughest element to change in the whole scenario.

teylyn
  • 34,374
  • 4
  • 53
  • 73
  • I expected to get that answer! I'm still curious why the Protect Sheet dialog gives you a "format cells" checkbox when it's meaningless, and also why there's an AllowFormattingCells property in sheet.protect. They must have some significance. – jisner Oct 26 '21 at 00:02
  • Training users? What about training myself :-) It's a mistake I constantly make. On Windows, remembering to select paste values is easy because it's the 123 icon when you right click. On the Mac, you have to navigate to an option under Paste Special. I wish there was a shortcut for that. – jisner Oct 26 '21 at 00:08
  • Command + Option + V opens the Paste Special dialog – teylyn Oct 26 '21 at 01:55