Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Preventing pasting except for Paste Values
Within this Discussion Group, I have searched for "restrict pasting" and
"prevent pasting", but I have been unable to find a thread that solves my problem. I want to prevent users from pasting anything in a cell except for values. So when a user copies some data and attempts to paste it in a cell using Edit | Paste, Ctrl-v, the Paste button, or the right-click menu and selecting Paste, I want the paste operation to behave as if the user had selected Paste Special | Values. Can this be done programmatically? If so, and being a novice to VBA, I would be grateful for any assistance. Thanks, Bob |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Preventing pasting except for Paste Values
I'm not sure I understand what it is you are trying to restrict. Paste
Special Values takes the output of a copied cell (or cells), normally with a formula in it (them) and pastes the evaluated value (number or text) into a different cell (or cells), but it also copies raw data (either numbers or text) and pastes them as well. Exactly what it is you want to allow to be pasted or, put another way, what is it you are trying to restrict from being pasted? -- Rick (MVP - Excel) "Bob" wrote in message ... Within this Discussion Group, I have searched for "restrict pasting" and "prevent pasting", but I have been unable to find a thread that solves my problem. I want to prevent users from pasting anything in a cell except for values. So when a user copies some data and attempts to paste it in a cell using Edit | Paste, Ctrl-v, the Paste button, or the right-click menu and selecting Paste, I want the paste operation to behave as if the user had selected Paste Special | Values. Can this be done programmatically? If so, and being a novice to VBA, I would be grateful for any assistance. Thanks, Bob |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Preventing pasting except for Paste Values
What about a macro that simply removes any leading or trailing spaces
instead... Sub RemoveLeadingTrailingBlanks() Dim X As Long, Z As Long, LastRow As Long LastRow = Cells(Rows.Count, "A").End(xlUp).Row For X = 1 To LastRow For Z = 1 To 5 Cells(X, Z).Value = Trim(Cells(X, Z).Value) Next Next End Sub -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... I'm not sure I understand what it is you are trying to restrict. Paste Special Values takes the output of a copied cell (or cells), normally with a formula in it (them) and pastes the evaluated value (number or text) into a different cell (or cells), but it also copies raw data (either numbers or text) and pastes them as well. Exactly what it is you want to allow to be pasted or, put another way, what is it you are trying to restrict from being pasted? -- Rick (MVP - Excel) "Bob" wrote in message ... Within this Discussion Group, I have searched for "restrict pasting" and "prevent pasting", but I have been unable to find a thread that solves my problem. I want to prevent users from pasting anything in a cell except for values. So when a user copies some data and attempts to paste it in a cell using Edit | Paste, Ctrl-v, the Paste button, or the right-click menu and selecting Paste, I want the paste operation to behave as if the user had selected Paste Special | Values. Can this be done programmatically? If so, and being a novice to VBA, I would be grateful for any assistance. Thanks, Bob |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Preventing pasting except for Paste Values
Ignore this last message (the one with the RemoveLeadingTrailingBlanks macro
in it)... this was meant for another thread. My questions posed in my first message still need to be answered. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... What about a macro that simply removes any leading or trailing spaces instead... Sub RemoveLeadingTrailingBlanks() Dim X As Long, Z As Long, LastRow As Long LastRow = Cells(Rows.Count, "A").End(xlUp).Row For X = 1 To LastRow For Z = 1 To 5 Cells(X, Z).Value = Trim(Cells(X, Z).Value) Next Next End Sub -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... I'm not sure I understand what it is you are trying to restrict. Paste Special Values takes the output of a copied cell (or cells), normally with a formula in it (them) and pastes the evaluated value (number or text) into a different cell (or cells), but it also copies raw data (either numbers or text) and pastes them as well. Exactly what it is you want to allow to be pasted or, put another way, what is it you are trying to restrict from being pasted? -- Rick (MVP - Excel) "Bob" wrote in message ... Within this Discussion Group, I have searched for "restrict pasting" and "prevent pasting", but I have been unable to find a thread that solves my problem. I want to prevent users from pasting anything in a cell except for values. So when a user copies some data and attempts to paste it in a cell using Edit | Paste, Ctrl-v, the Paste button, or the right-click menu and selecting Paste, I want the paste operation to behave as if the user had selected Paste Special | Values. Can this be done programmatically? If so, and being a novice to VBA, I would be grateful for any assistance. Thanks, Bob |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Preventing pasting except for Paste Values
Rick,
Forgive me for not being more clear in describing the restrictions. I want to prevent users from overwriting the cell formats (including conditional formatting) and validation rules I have established. Hence, my need to restrict users to just pasting only "values". Bob "Rick Rothstein" wrote: I'm not sure I understand what it is you are trying to restrict. Paste Special Values takes the output of a copied cell (or cells), normally with a formula in it (them) and pastes the evaluated value (number or text) into a different cell (or cells), but it also copies raw data (either numbers or text) and pastes them as well. Exactly what it is you want to allow to be pasted or, put another way, what is it you are trying to restrict from being pasted? -- Rick (MVP - Excel) "Bob" wrote in message ... Within this Discussion Group, I have searched for "restrict pasting" and "prevent pasting", but I have been unable to find a thread that solves my problem. I want to prevent users from pasting anything in a cell except for values. So when a user copies some data and attempts to paste it in a cell using Edit | Paste, Ctrl-v, the Paste button, or the right-click menu and selecting Paste, I want the paste operation to behave as if the user had selected Paste Special | Values. Can this be done programmatically? If so, and being a novice to VBA, I would be grateful for any assistance. Thanks, Bob |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Preventing pasting except for Paste Values
On Aug 28, 8:46*am, Bob wrote:
Rick, Forgive me for not being more clear in describing the restrictions. *I want to prevent users from overwriting the cell formats (including conditional formatting) and validation rules I have established. *Hence, my need to restrict users to just pasting only "values". Bob "Rick Rothstein" wrote: I'm not sure I understand what it is you are trying to restrict. Paste Special Values takes the output of a copied cell (or cells), normally with a formula in it (them) and pastes the evaluated value (number or text) into a different cell (or cells), but it also copies raw data (either numbers or text) and pastes them as well. Exactly what it is you want to allow to be pasted or, put another way, what is it you are trying to restrict from being pasted? -- Rick (MVP - Excel) "Bob" wrote in message ... Within this Discussion Group, I have searched for "restrict pasting" and "prevent pasting", but I have been unable to find a thread that solves my problem. I want to prevent users from pasting anything in a cell except for values. So when a user copies some data and attempts to paste it in a cell using Edit | Paste, Ctrl-v, the Paste button, or the right-click menu and selecting Paste, I want the paste operation to behave as if the user had selected Paste Special | Values. Can this be done programmatically? *If so, and being a novice to VBA, I would be grateful for any assistance. Thanks, Bob- Hide quoted text - - Show quoted text - Hi Bob, Instead of changing the way Excel behaves during paste, I would rather not allow the user to change the data (Disable the copy-paste). I would use a macro on some button and provide the paste feature. I am not sure if it works for you, but you may want to consider this option. Here is the code that you need to put on any button - 'this will copy only values from the range B2 to B6 and paste them from C2 to C6. Sub PasteOnlyValues() With Worksheets(1) .Range("B2:B6").Copy .Range("C2").Select Selection.PasteSpecial Paste:=xlPasteValues, _ Operation:=xlNone, _ skipblanks:=False, _ Transpose:=False End With Application.CutCopyMode = False End Sub HTH, Regards, Satish |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Preventing pasting except for Paste Values
Satish,
Thanks for your suggestion, but I don't want to disable the ability for a user to paste. I just want to restrict pasting to pasting only values. The users typically copy data from MS-Word documents into my worksheet. And they typically (but not always) use the Ctrl-v keyboard shortcut. However, when they do this, they wipe out all my conditional formatting and validation rules. That's what I'm trying to protect. Hence, my request for a solution that will allow users to paste data in the cells, but only "values". Bob "Satish" wrote: On Aug 28, 8:46 am, Bob wrote: Rick, Forgive me for not being more clear in describing the restrictions. I want to prevent users from overwriting the cell formats (including conditional formatting) and validation rules I have established. Hence, my need to restrict users to just pasting only "values". Bob "Rick Rothstein" wrote: I'm not sure I understand what it is you are trying to restrict. Paste Special Values takes the output of a copied cell (or cells), normally with a formula in it (them) and pastes the evaluated value (number or text) into a different cell (or cells), but it also copies raw data (either numbers or text) and pastes them as well. Exactly what it is you want to allow to be pasted or, put another way, what is it you are trying to restrict from being pasted? -- Rick (MVP - Excel) "Bob" wrote in message ... Within this Discussion Group, I have searched for "restrict pasting" and "prevent pasting", but I have been unable to find a thread that solves my problem. I want to prevent users from pasting anything in a cell except for values. So when a user copies some data and attempts to paste it in a cell using Edit | Paste, Ctrl-v, the Paste button, or the right-click menu and selecting Paste, I want the paste operation to behave as if the user had selected Paste Special | Values. Can this be done programmatically? If so, and being a novice to VBA, I would be grateful for any assistance. Thanks, Bob- Hide quoted text - - Show quoted text - Hi Bob, Instead of changing the way Excel behaves during paste, I would rather not allow the user to change the data (Disable the copy-paste). I would use a macro on some button and provide the paste feature. I am not sure if it works for you, but you may want to consider this option. Here is the code that you need to put on any button - 'this will copy only values from the range B2 to B6 and paste them from C2 to C6. Sub PasteOnlyValues() With Worksheets(1) .Range("B2:B6").Copy .Range("C2").Select Selection.PasteSpecial Paste:=xlPasteValues, _ Operation:=xlNone, _ skipblanks:=False, _ Transpose:=False End With Application.CutCopyMode = False End Sub HTH, Regards, Satish |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Preventing pasting except for Paste Values
use the worksheet change event might do this
right click the sheet tab and select View Code Private Sub Worksheet_Change(ByVal Target As Range) If Application.CutCopyMode Then Target.PasteSpecial xlPasteValues End If End Sub "Bob" wrote: Within this Discussion Group, I have searched for "restrict pasting" and "prevent pasting", but I have been unable to find a thread that solves my problem. I want to prevent users from pasting anything in a cell except for values. So when a user copies some data and attempts to paste it in a cell using Edit | Paste, Ctrl-v, the Paste button, or the right-click menu and selecting Paste, I want the paste operation to behave as if the user had selected Paste Special | Values. Can this be done programmatically? If so, and being a novice to VBA, I would be grateful for any assistance. Thanks, Bob |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Preventing pasting except for Paste Values
Patrick,
Thanks for your suggestion, but please see my response above to Satish's suggestion. Bob "Patrick Molloy" wrote: use the worksheet change event might do this right click the sheet tab and select View Code Private Sub Worksheet_Change(ByVal Target As Range) If Application.CutCopyMode Then Target.PasteSpecial xlPasteValues End If End Sub "Bob" wrote: Within this Discussion Group, I have searched for "restrict pasting" and "prevent pasting", but I have been unable to find a thread that solves my problem. I want to prevent users from pasting anything in a cell except for values. So when a user copies some data and attempts to paste it in a cell using Edit | Paste, Ctrl-v, the Paste button, or the right-click menu and selecting Paste, I want the paste operation to behave as if the user had selected Paste Special | Values. Can this be done programmatically? If so, and being a novice to VBA, I would be grateful for any assistance. Thanks, Bob |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Preventing copy/paste on validated cells | Excel Discussion (Misc queries) | |||
Copy Paste Macro not Pasting Values when Blank | Excel Programming | |||
Paste Values not pasting numbers | Excel Discussion (Misc queries) | |||
Preventing cutting/pasting | Excel Programming | |||
Preventing user entering duplicate values in a cell range | Excel Worksheet Functions |