Change paste to only paste values

[Chris Trygstad]

I have a spreadsheet that is nice and formatted just the way I want it
to be, but the people using the spreadsheet are mainly copying and
pasting data from another spreadsheet into the target spreadsheet, and
they rarely remember to "paste special" ---> "values" to preserve the
formatting. I've tried to protect the formatting via protect
worksheet, but that seems to get overridden if the values are pasted
in.

So now, I'm trying to devise a way to use VB to make all paste
operations default to paste only values. I saw this code,
Sub New_paste() ActiveCell.PasteSpecial XlValues End Sub, on the web,
but it doesn't do anything, and I don't know much about VB, but I do
know how to get into the VB editor. Is there an easy way to make all
paste operations (shortcut on toolbar, right-click menu, edit menu,
ctrl+v) default to paste values only?

 

[Chris Trygstad]

It actually looks like this does the trick:

Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Target As Range)
Dim myValue
With Application
..EnableEvents = False
myValue = Target.Value
..Undo
Target = myValue
..EnableEvents = True
End With
End Sub

If anyone has a better suggestion, let me know. The only downfall i
see is the inability to "Undo" after the Paste operation is complete.

Chris