Only allow Paste Special / Values

[Sir Babydum GBE]

Hi, I have a sheet with validation and conditional formatting that many people have to fill out.

Some are pasting information from other sheets to save time, but this is overwriting formatting and validation that I need in place.

Is it possible for vba to detect whether the user is using paste special/values, and to disallow it if not - with a message like "Pasting information in this way can damage this spreadsheet, please paste the values only (Edit / Paste Special / Values) to input your information."?

Even better, could the macro change the paste execution from a normal paste to a values paste without the user ever knowing it?

Then once the paste special function has finished, another warning such as "Please check the area you have pasted into. Illegal entries will appear red"

Many thanks

Sir BD

[lenze]

I asked this same question some time ago on another board. Damon Ostrander of Colorado gave me this code.

If you are only concerned about pasting values vs formulas and not about cell formatting, you can use the following code:

Private Sub Worksheet_Change(ByVal Target As Range) 
      Dim A As Variant 
      If Not Intersect(Target, [Prange]) Is Nothing Then 
           A = [Prange] 
           [Prange] = A 
      End If 
End Sub

where "Prange" is the name of the range or a range specifier like "C5:G23". This code must be placed in the worksheet's event code module.

This code simply deletes all the formulas in the range (leaving the values) any time any cell value in the range is modified by paste (or any other method).

HTH

lenze

[Jacob Hilderbrand]

This will replace the on action event on the Edit menu or any other menu where it might be hiding.

[vba]
Option Explicit

Sub ChangePaste()

Dim CmdBar As CommandBar
Dim CmdCtl As CommandBarControl

For Each CmdBar In CommandBars
Set CmdCtl = CmdBar.FindControl(ID:=22, recursive:=True)
If Not CmdCtl Is Nothing Then
CmdCtl.OnAction = "MyPaste"
End If
Next CmdBar

Set CmdBar = Nothing
Set CmdCtl = Nothing

End Sub

Sub ResetPaste()

Dim CmdBar As CommandBar
Dim CmdCtl As CommandBarControl

For Each CmdBar In CommandBars
Set CmdCtl = CmdBar.FindControl(ID:=22, recursive:=True)
If Not CmdCtl Is Nothing Then CmdCtl.OnAction = ""
Next CmdBar

Set CmdBar = Nothing
Set CmdCtl = Nothing

End Sub

Sub MyPaste()

On Error Resume Next
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
On Error GoTo 0

End Sub
[/vba]

Just call these macros on the open/activate and close/deactivate events. You can use OnKey to take over the Ctrl+v hot key.

I am not sure how to change the on action for the Paste icon on the Standard toolbar. It is a drop down, but I don't see the action Paste action. So maybe someone else has an idea for this.

Thanks

[Sir Babydum GBE]

This will replace the on action event on the Edit menu or any other menu where it might be hiding.

[vba]
Option Explicit

Sub ChangePaste()

Dim CmdBar As CommandBar
Dim CmdCtl As CommandBarControl

For Each CmdBar In CommandBars
Set CmdCtl = CmdBar.FindControl(ID:=22, recursive:=True)
If Not CmdCtl Is Nothing Then
CmdCtl.OnAction = "MyPaste"
End If
Next CmdBar

Set CmdBar = Nothing
Set CmdCtl = Nothing

End Sub

Sub ResetPaste()

Dim CmdBar As CommandBar
Dim CmdCtl As CommandBarControl

For Each CmdBar In CommandBars
Set CmdCtl = CmdBar.FindControl(ID:=22, recursive:=True)
If Not CmdCtl Is Nothing Then CmdCtl.OnAction = ""
Next CmdBar

Set CmdBar = Nothing
Set CmdCtl = Nothing

End Sub

Sub MyPaste()

On Error Resume Next
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
On Error GoTo 0

End Sub
[/vba]

Just call these macros on the open/activate and close/deactivate events. You can use OnKey to take over the Ctrl+v hot key.

I am not sure how to change the on action for the Paste icon on the Standard toolbar. It is a drop down, but I don't see the action Paste action. So maybe someone else has an idea for this.

Thanks

Hi Jake,

Thanks for your help with this. It's interesting, but it wasn't pasting the values as expected. So I disabled the error trapping in "MyPaste" and found that I got an error "PasteSpecial Method of Range Class Failed". So, double checking there wasn't a typo in there or something I recorded a macro of me pasting values - which worked fine, but playing it back got me the same error.

Any ideas as to what's behind this contradictory behaviour?

[Sir Babydum GBE]

Hmm,

A little experimentation has revealed the following (I tested this on a completely fresh workbook with no others open).

If my code contains an instruction to Copy, and then later to PasteSpecial, it works fine. But if I'm allready in Copy mode when i run the macro - I.e. the macro doesn't contain the instruction to copy, only to paste, then the code fails every time. (edit: in fact, it's not just PasteSpecial, no paste method works)

I'm using Excel97 - surely there has to be a way round this bug - if it is a bug.

[Sir Babydum GBE]

Even weirder:

more experimentation has revealed that if there is non-excel data on the clipboard, then all my paste macros work. I only get the problem when I have cells on the same or a different worksheet selected and in copy mode

[lucas]

I just used the mypaste sub and attached it to a button....copied cells with formula's and used the button to paste...same sheet etc. got no errors?

[Sir Babydum GBE]

I just used the mypaste sub and attached it to a button....copied cells with formula's and used the button to paste...same sheet etc. got no errors?

Well, that is spooky indeed - for that suggestion worked - and what's more, having done that, what I was trying to do earlier is now working. i'm flabergasted. But my problems aren't over.

In the actual worksheet that i'm concerned about, i have the following code which is turning copy mode off as a side-effect:
[vba]
Private Sub Worksheet_Activate()
Range("B5").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
Sheets("Control Panel").Visible = xlVeryHidden
Sheets("Project MI").Visible = xlVeryHidden
Sheets("People MI").Visible = xlVeryHidden
Sheets("Summary MI").Visible = xlVeryHidden
Sheets("Record Of Updates").Visible = xlVeryHidden
End Sub


Private Sub Worksheet_Calculate()
Dim cell As Range
On Error GoTo ws_exit:
Application.EnableEvents = False
For Each cell In Range("D1" & Me.Cells(Me.Rows.Count, "D").End(xlUp).Row)
With cell
Select Case .Value
Case "Overtime (single time equivalent)": .Offset(0, 1).NumberFormat = "#,##0.00_ ;-#,##0.00 "
Case Else: .Offset(0, 1).NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
End Select
End With
Next cell

ws_exit:
Application.EnableEvents = True
End Sub
[/vba]

So of course this could all have been academic anyhow. Any suggestions?

Thanks a whole lot for your help so far!