How can I default to Paste Special?

[Guest]

I have created a spreadsheet to be used by my staff for inputing data. Many
of the cells have formulas in them that are protected. However, occasionally
my staff will cut and paste or copy and paste cells from one row or colum to
another, creating errors in the spreadsheet becuase the formula follows. Is
there a way to automatically have the sheet use Paste=>Special when the
copy=>paste function is used?

Thanks in advance to anyone who may have some input on this...

 

[Frank Kabel]

Hi
AFAIK not possible. But you could record a macro while using paste special
and assign a shortcut for this macro

 

[Don Guillett]

You could actually use a worksheet change event to create your formula
values. Then, there is no formula left to copy, just values. Right click
sheet tab>view code>copy this example.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 1 Or Target.Row < 2 Then Exit Sub
Target.Offset(0, 1) = Target * 3.34
End Sub

 

[Peter T]

Couple of ideas for you. Neither very satisfactory and wouldn't want them
myself, but maybe of use for you. Paste into the ThisWorkbook module

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Excel.Range)
Dim retval
If Application.CutCopyMode <> False Then
''''''''''''''''
'''''method 1
'retval = Application.Dialogs(xlDialogPasteSpecial).Show(3)
'If retval = False Then Application.CutCopyMode = False
''''''''''''''''

'''''''''''''''
'''''Method 2
retval = MsgBox("Yes: Paste values & Formats" & vbCr & _
"No: Paste values only", vbYesNoCancel, _
"Paste special No Undo")
If retval <> vbCancel Then
Selection.PasteSpecial Paste:=xlValues
If retval = vbYes Then
Selection.PasteSpecial Paste:=xlFormats
End If
Else
SendKeys "{Esc}", True
End If
'''''''''''''''''
End If
End Sub


Regards,
Peter