Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prevent Paste
Hi,
Is there a way of preventing users from Pasting into a worksheet? Can I allow them to Paste values only? thanks Simon |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prevent Paste
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 "Simon Shaw" wrote: Hi, Is there a way of preventing users from Pasting into a worksheet? Can I allow them to Paste values only? thanks Simon |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prevent Paste
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 "Patrick Molloy" wrote: 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 "Simon Shaw" wrote: Hi, Is there a way of preventing users from Pasting into a worksheet? Can I allow them to Paste values only? thanks Simon |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Prevent cut & paste in a protected worksheet. | Excel Discussion (Misc queries) | |||
Prevent Cut and Paste | Excel Worksheet Functions | |||
Prevent cut and paste over a range | Excel Worksheet Functions | |||
How do I prevent certain Paste options | Excel Discussion (Misc queries) | |||
prevent the copy and paste function | Excel Discussion (Misc queries) |