Prevent Paste

[Guest]

Hi,

Is there a way of preventing users from Pasting into a worksheet? Can I
allow them to Paste values only?

thanks

Simon

 

[Guest]

No
(1)you could protect the sheet or "critical" cells to prevent the user from
changing them
(2)you could use th esheet's CVhange event to convert formula to values and
to set any formatting to a default of your own.

here's an example. range name a table "MyTable"
Elsewhere create a region with randon numbers, then copy/paste so that the
pasted area overlaps into your table

Private Sub Worksheet_Change(ByVal Target As Range)
Dim overlap As Range
Set overlap = Intersect(Target, Range("MyTable"))
If Not overlap Is Nothing Then
' convert to values
overlap.Value = overlap.Value
'reset format
SetMyTableFormat
End If

End Sub

Private Sub SetMyTableFormat()
Application.EnableEvents = False
With Range("MyTable")
.Interior.ColorIndex = 34
.NumberFormat = "0.00"
End With
Application.EnableEvents = True
End Sub

 

[Guest]

Hi Patrick,

Thanks, this is the type of solution I have done in the past...

I usually create a toolbar with a Paste Values button (among others), and
instruct users to click it when pasting, however, someone inevitably pastes
the 'normal' way and can make a mess of the sheet, not to mention, it ignors
the cell validation. I then have to put conditional formatting to ensure the
items in the list are valid.

Thanks

simon