Most Valuable Professional


View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Subscribe to our mailing list

* indicates required

Audit !!!

Check out our RefTreeAnalyser
the ultimate Excel formula auditing tool.

Trainings

Excel VBA Masterclass (English)
Excel VBA for Financials (Dutch)

Third party tools

Speed up your file

FastExcel
The best tool to optimise your Excel model!

Repair your file

Stellar Phoenix Excel Repair
Best tool to repair corrupt Excel sheets and objects
Home > English site > Articles > Catch Paste

Catching Paste Operations

Introduction

You know the situation: You have carefully setup a workbook with intricate Validation schemes. But then along comes your user and he copies and pastes at will. Result: Validation zapped, workbook structure violated.

What to do? The only way I find reliable is to catch all possible paste operations. But this isn't very easy, since there are a zilion ways to paste:

This article shows one way to intercept all these paste operations.

Explanation Of Parts Of The Code

Catching Keyboard Shortcuts

The only way to catch paste keyboard shortcuts is by assigning them to your own paste code. E.g. like this:

    Application.OnKey "^v", "MyPasteValues"
    Application.OnKey "^{Insert}", "MyPasteValues"
    Application.OnKey "+{Insert}", "MyPasteValues"
    Application.OnKey "~", "MyPasteValues"
    Application.OnKey "{Enter}", "MyPasteValues"
    

(the MyPasteValues routine is shown further below)

Catching Clicks On Toolbar And Menu Controls

To catch clicks on Commandbar controls, I used a class module in which a variable is declared of type commandbarcontrol, using the WithEvents keyword. Then an instance of this class module is created for each control I want to intercept.

The class (named clsCommandBarCatcher), contains this code:

'-------------------------------------------------------------------------
' Module    : clsCommandBarCatcher
' Company   : JKP Application Development Services (c)
' Author    : Jan Karel Pieterse
' Created   : 4-10-2007
' Purpose   : This class catches clicks on Excel's commandbars to be able to prevent pasting.
'-------------------------------------------------------------------------
Option Explicit

Public WithEvents oComBarCtl As Office.CommandBarButton

Private Sub Class_Terminate()
    Set oComBarCtl = Nothing
End Sub

Private Sub oComBarCtl_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
    CancelDefault = True
    Application.OnTime Now, "MyPasteValues"
End Sub

And the code to create an instance of this class could look like this:

    Dim oCtl As CommandBarButton
    Dim CCatcher As clsCommandBarCatcher
    Set oCtl = Application.CommandBars("Cell").FindControl(ID:=3185, recursive:=True)
    Set CCatcher = New clsCommandBarCatcher
    Set CCatcher.oComBarCtl = oCtl

To keep the instances of the classes "alive" I create a (module) collection variable and add each instance to the collection.

The paste is handled by this routine:

Public Sub MyPasteValues()
'-------------------------------------------------------------------------
' Procedure : EnableDisableControl
' Author    : Jan Karel Pieterse www.jkp-ads.com
' Created   : 24-9-2007
' Purpose   : Propriatary paste values routine called from control event
'             handler in clsCommandBarCatcher and from various OnKey macros.
'-------------------------------------------------------------------------
    If Application.CutCopyMode <> False Then
        If MsgBox("Normal paste operation has been disabled. You are about to Paste Values (cannot be undone), proceed?" & vbNewLine & "Tip: to be able to undo a paste, use the paste values button on the toolbar.", vbQuestion + vbOKCancel, GSAPPNAME) = vbOK Then
            On Error Resume Next
            Selection.PasteSpecial Paste:=xlValues
            IsCellValidationOK Selection
        End If
    ElseIf Application.MoveAfterReturn Then
        On Error Resume Next
        Select Case Application.MoveAfterReturnDirection
        Case xlUp
            ActiveCell.Offset(-1).Select
        Case xlDown
            ActiveCell.Offset(1).Select
        Case xlToRight
            ActiveCell.Offset(, 1).Select
        Case xlToLeft
            ActiveCell.Offset(, -1).Select
        End Select
    End If
End Sub

Note that the code above also mimicks the MoveAfterReturn behaviour of Excel.

Catching clicks on the Excel Ribbon (2007 and up)

To catch clicks on the various Excel 2007 (and up) paste controls, you have to include CustomUI with your Excel 2007-2013 format xlsm file. The relevant Ribbon XML code is listed here:

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
    <commands>
        <command idMso="Paste" onAction="MyPasteValues2007"/>
        <command idMso="PasteSpecial" onAction="MyPasteValues2007"/>
        <command idMso="PasteFormulas" onAction="MyPasteValues2007"/>
        <command idMso="PasteFormatting" onAction="MyPasteValues2007"/>
        <command idMso="PasteValues" onAction="MyPasteValues2007"/>
        <command idMso="PasteNoBorders" onAction="MyPasteValues2007"/>
        <command idMso="PasteTranspose" onAction="MyPasteValues2007"/>
        <command idMso="PasteLink" onAction="MyPasteValues2007"/>
        <command idMso="PasteSpecial" onAction="MyPasteValues2007"/>
        <command idMso="PasteAsHyperlink" onAction="MyPasteValues2007"/>
        <command idMso="PastePictureLink" onAction="MyPasteValues2007"/>
        <command idMso="PasteAsPicture" onAction="MyPasteValues2007"/>
    </commands>
</customUI>

This page by Ron de Bruin clearly explains how to add ribbon customisations to your files

Cell Drag And Drop Mode

Dragging the fill handle is another way to wreck your validation. Hence this is turned off too. Unfortunately changing this setting empties the clipboard, so this setting cannot be toggled at will when switching workbooks. If you do turn it on and off when switching to and from your workbook, copying and pasting from another workbook to the one with the code becomes impossible.

Validation

The code I showed above redirects all paste operations to one routine, which does a paste special, values. But pasting does not trigger any validation checks. Also, there is no way of knowing what cells may be affected by the paste before the actual paste has been done. Luckily, after the paste, the Selection object is equal to the cells affected by the paste. Using the "Validation" object of the Range object it is possible to check whether the cells adhere to their validation settings. I created this function, to which I pass the range that has been changed. The function returns False as soon as any cell violates its validation rule:

Public Function IsCellValidationOK(oRange As Object) As Boolean
'-------------------------------------------------------------------------
' Procedure : ValidateCells
' Author    : Jan Karel Pieterse www.jkp-ads.com
' Created   : 21-11-2007
' Purpose   : This routine checks if entries pasted into the cells in oRange
'             are not violating a validation rule.
'             Returns False if any cell's validation is violated
'-------------------------------------------------------------------------
    Dim oCell As Range
    If TypeName(oRange) <> "Range" Then Exit Function
    IsCellValidationOK = True
    For Each oCell In oRange
        If Not oCell.Validation Is Nothing Then
            If oCell.HasFormula Then
            Else
                If oCell.Validation.Value = False Then
                    IsCellValidationOK = False
                    Exit For
                End If
            End If
        End If
    Next
    If IsCellValidationOK = False Then
        MsgBox "Warning!!!" & vbNewLine & vbNewLine & _
            "The paste operation has caused illegal entries to appear" & vbNewLine & _
            "in one or more cells containing validation rules." & vbNewLine & vbNewLine & _
            "Please check all cells you have just pasted " & vbNewLine & _
            "into and correct any errors!", vbOKOnly + vbExclamation, GSAPPNAME
        oRange.Select
    End If
End Function

Switching On And Off

Of course I want this paste restriction to work on just the workbook with the code. This means some code in the ThisWorkbook module is needed, to be precise in the Workbook_DeActivate and Workbook_Activate events. This is all code I have in ThisWorkbook:

Option Explicit

'Holds time of scheduled ontime macro
'(Workbook_Before_Close and Workbook_Deactivate)
Private mdNextTimeCatchPaste As Double

Private Sub Workbook_Activate()
    CatchPaste
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    StopCatchPaste
    mdNextTimeCatchPaste = Now
    Application.OnTime mdNextTimeCatchPaste, "'" & ThisWorkbook.Name & "'!CatchPaste"
    Application.CellDragAndDrop = True
End Sub


Private Sub Workbook_Deactivate()
    StopCatchPaste
    On Error Resume Next
    'Cancel scheduled macro's,
    'because we might be closing the file
    Application.OnTime mdNextTimeCatchPaste, "'" & ThisWorkbook.Name & "'!CatchPaste", , False
End Sub

Private Sub Workbook_Open()
    CatchPaste
End Sub

Putting It All Together

The entire module that handles the initialisation and processes the paste operation looks like this:

'-------------------------------------------------------------------------
' Module    : modHandlePaste
' Author    : Jan Karel Pieterse
' Created   : 24-9-2007
' Purpose   : Module that ensures paste and paste formats is disabled
'-------------------------------------------------------------------------
Option Explicit
Option Private Module

Dim mcCatchers As Collection

Sub CatchPaste()
'-------------------------------------------------------------------------
' Procedure : CatchPaste
' Author    : Jan Karel Pieterse www.jkp-ads.com
' Created   : 24-9-2007
' Purpose   : This routine ensures all paste operations are redirected to our own.
'             This way we avoid overwriting styles and validations.
'-------------------------------------------------------------------------
    StopCatchPaste
    Set mcCatchers = New Collection
    'Paste button
    AddCatch "Dummy", 22
    'Paste button (with dropdown)
    EnableDisableControl 6002, False
    'Paste Special button
    AddCatch "Dummy", 755
    'Paste As Hyperlink button
    AddCatch "Dummy", 2787
    'Paste Formats bottun
    AddCatch "Dummy", 369
    'Insert Cut cells button
    AddCatch "Dummy", 3185
    'Insert Copied Cells button
    AddCatch "Dummy", 3187
    Application.OnKey "^v", "MyPasteValues"
    Application.OnKey "^{Insert}", "MyPasteValues"
    Application.OnKey "+{Insert}", "MyPasteValues"
    Application.OnKey "~", "MyPasteValues"
    Application.OnKey "{Enter}", "MyPasteValues"
    
    'Changing the celldragdrop mode clears the clipboard.
    'This means if you switch from another workbook back to this one, you would be unable to copy
    'information and paste it into the template. This is why we do not reinstate the
    'celldragdropmode when switching away from a B1 template and vice versa: switch it off
    'when we return to a template.
    If Application.CellDragAndDrop Then
        'If the user has manually changed this mode to true, the clipboard WILL be emptied due to the next line
        Application.CellDragAndDrop = False
    End If
End Sub

Sub StopCatchPaste()
'-------------------------------------------------------------------------
' Procedure : StopCatchPaste
' Author    : Jan Karel Pieterse www.jkp-ads.com
' Created   : 24-9-2007
' Purpose   : Resets the paste operations to their defaults
'-------------------------------------------------------------------------

    Dim lCount As Long
    On Error Resume Next
    Set mcCatchers = Nothing
    EnableDisableControl 6002, True
    Application.OnKey "^v"
    Application.OnKey "^{Insert}"
    Application.OnKey "+{Insert}"
    Application.OnKey "~"
    Application.OnKey "{Enter}"
    'Changing the celldragdrop mode clears the clipboard.
    'This means if you switch from another workbook back to this one, you would be unable to copy
    'information and paste it into the template. This is why we do not reinstate the
    'celldragdropmode when switching away from a B1 template and vice versa: switch it off
    'when we return to a template.
    'Next line disabled for this reason!!!
'    Application.CellDragAndDrop = True
End Sub

Sub AddCatch(sCombarName As String, lID As Long)
'-------------------------------------------------------------------------
' Procedure : AddCatch
' Author    : Jan Karel Pieterse www.jkp-ads.com
' Created   : 24-9-2007
' Purpose   : Adds a commandbarcontrol to be monitored
'-------------------------------------------------------------------------
    Dim oCtl As CommandBarControl
    Dim CCatcher As clsCommandBarCatcher
    Dim oBar As CommandBar
    Set oCtl = Nothing
    On Error Resume Next
    Set oBar = Application.CommandBars(sCombarName)
    If oBar Is Nothing Then
        Set oBar = Application.CommandBars.Add(sCombarName, , , True)
        oBar.Controls.Add ID:=lID
        oBar.Visible = True
    End If
    With oBar
        Set oCtl = .FindControl(ID:=lID, recursive:=True)
        If oCtl Is Nothing Then
            Set oCtl = .Controls.Add(ID:=lID)
        End If
    End With
    'Try Insert copied/cut cells separately through the cells shortcut menu
    If oCtl Is Nothing And (lID = 3185 Or lID = 3187) Then
        Set oCtl = Application.CommandBars("Cell").FindControl(ID:=lID, recursive:=True)
    End If
    Set CCatcher = New clsCommandBarCatcher
    Set CCatcher.oComBarCtl = oCtl
    mcCatchers.Add CCatcher
    Set CCatcher = Nothing
    oBar.Delete
    Set oBar = Nothing
End Sub

Private Sub EnableDisableControl(lID As Long, bEnable As Boolean)
'-------------------------------------------------------------------------
' Procedure : EnableDisableControl
' Author    : Jan Karel Pieterse www.jkp-ads.com
' Created   : 24-9-2007
' Purpose   : Enables or disables a specific control on all commandbars
'-------------------------------------------------------------------------

    Dim oBar As CommandBar
    Dim oCtl As CommandBarControl
    On Error Resume Next
    For Each oBar In CommandBars
        Set oCtl = oBar.FindControl(ID:=lID, recursive:=True)
        If Not oCtl Is Nothing Then
            oCtl.Enabled = bEnable
        End If
    Next
End Sub

Public Sub MyPasteValues()
'-------------------------------------------------------------------------
' Procedure : EnableDisableControl
' Author    : Jan Karel Pieterse www.jkp-ads.com
' Created   : 24-9-2007
' Purpose   : Propriatary paste values routine called from control event
'             handler in clsCommandBarCatcher and from various OnKey macros.
'-------------------------------------------------------------------------
    If Application.CutCopyMode <> False Then
        If MsgBox("Normal paste operation has been disabled. You are about to Paste Values (cannot be undone), proceed?" & vbNewLine & "Tip: to be able to undo a paste, use the paste values button on the toolbar.", vbQuestion + vbOKCancel, GSAPPNAME) = vbOK Then
            On Error Resume Next
            Selection.PasteSpecial Paste:=xlValues
            IsCellValidationOK Selection
        End If
    ElseIf Application.MoveAfterReturn Then
        On Error Resume Next
        Select Case Application.MoveAfterReturnDirection
        Case xlUp
            ActiveCell.Offset(-1).Select
        Case xlDown
            ActiveCell.Offset(1).Select
        Case xlToRight
            ActiveCell.Offset(, 1).Select
        Case xlToLeft
            ActiveCell.Offset(, -1).Select
        End Select
    End If
End Sub

Public Function IsCellValidationOK(oRange As Object) As Boolean
'-------------------------------------------------------------------------
' Procedure : ValidateCells
' Author    : Jan Karel Pieterse www.jkp-ads.com
' Created   : 21-11-2007
' Purpose   : This routine checks if entries pasted into the cells in oRange
'             are not violating a validation rule.
'             Returns False if any cell's validation is violated
'-------------------------------------------------------------------------
    Dim oCell As Range
    If TypeName(oRange) <> "Range" Then Exit Function
    IsCellValidationOK = True
    For Each oCell In oRange
        If Not oCell.Validation Is Nothing Then
            If oCell.HasFormula Then
            Else
                If oCell.Validation.Value = False Then
                    IsCellValidationOK = False
                    Exit For
                End If
            End If
        End If
    Next
    If IsCellValidationOK = False Then
        MsgBox "Warning!!!" & vbNewLine & vbNewLine & _
            "The paste operation has caused illegal entries to appear" & vbNewLine & _
            "in one or more cells containing validation rules." & vbNewLine & vbNewLine & _
            "Please check all cells you have just pasted " & vbNewLine & _
            "into and correct any errors!", vbOKOnly + vbExclamation, GSAPPNAME
        oRange.Select
    End If
End Function

Sample File

I prepared a sample file so you can see how to put this all together. The download contains two workbooks; one for Excel 2003 and earlier, the other for Excel 2007 and up.


Comments

All comments about this page:


Comment by: utham (1/28/2008 10:41:14 PM)

can this been done without the help of VBA

 


Comment by: Jan Karel Pieterse (1/29/2008 3:39:55 AM)

Hi utham,

No, I'm afraid not.

 


Comment by: Brent Leslie (4/1/2008 2:28:47 AM)

Hey Jan, this is an excellent article and solves a problem I have been thinking about for a while. Thanks for putting the effort into sorting this all out, it means I don't have to worry about it so much and can get on with the rest of our development.

 


Comment by: Brent Leslie (4/1/2008 3:18:05 AM)

One problem though - you don't declare the GSAPPNAME variable anywhere - I take it it's a global that normally holds the name of your application.

Otherwise, this works sensationally! I am even using it in Excel 07 without any problems.

Thanks again for saving me having to code this myself!

 


Comment by: Jan Karel Pieterse (4/1/2008 4:26:03 AM)

Hi brent,

Well spotted. I'll correct this.

 


Comment by: Brent Leslie (4/1/2008 7:11:30 PM)

Hi Jan,

No worries. One more potential improvement though I have made in my code which you may find useful.

I often used merged cells in my excel template. The "MyPasteValues" procedure uses the line "Selection.PasteSpecial Paste:=xlValues" which appears to not work for merged ranges. By "not work" I mean the value is not pasted into the merged cells.

Instead I have replaced this with the following:

If Selection.MergeCells = True Then
ThisWorkbook.Worksheets("SheetNameWithTempRange").Range("TempRange_SingleCell").PasteSpecial xlPasteValues

Range(Left(Selection.Address, InStr(1, Selection.Address, ":", vbTextCompare) - 1)).Value = _
ThisWorkbook.Worksheets("SheetNameWithTempRange").Range("TempRange_SingleCell").Value
Else
Selection.PasteSpecial Paste:=xlValues
End If

This checks whether the selected cell is merged. If it is not, the code simply works as you have above. If it is merged, the value of the clipboard range gets put into a temporary cell. The VALUE of this cell is then assigned to the VALUE of the selected merge cells. The IsCellValidationOK procedure then runs as per your code.

This indirection appears to allow merged cells to be able to obtain the copied value. This does have a small downside though - if the sheet is temporary range ("TempRange_SingleCell" in the above snippet) is not on the screen a small screen flicker occurs. This isn't a problem with my application so I am not finding a way to fix this (until a user complains!) but it could probably be fixed with a couple of Application.ScreenUpdating calls.

Thanks again!

 


Comment by: Mike D (6/13/2008 7:56:10 AM)

Jan,

I am a VBA novice so I applogize if this is a dumb question. I am trying to use
the CatchPasteDemo.xls file in Excel 2003, but I get an "Invalid procedure call or
argument (Error 5)" on the this line of code "Set oBar = Application.CommandBars
(sCombarName)" in the modHandlePaste module. Can yo tell how to fix this so the
demo will work. Thanks

Mike

 


Comment by: Ale G. (8/29/2008 7:21:12 PM)

Very good piece of code! Thanks Jan!
I only added "EnableDisableControl 108, False" in CatchPaste sub for disabling format
painter (and obviously "EnableDisableControl 108, true" in StopCatchPaste).

Another thing I would disable is the contextmenu "paste options" showing paste format
option.
This menu appear using "paste values" button on excel standard toolbar.

I found this page on MS help & support site listing all IDs but that:
http://support.microsoft.com/default.aspx?scid=kb;en-us;213552

Any suggestions?

 


Comment by: Jan Karel Pieterse (8/31/2008 9:00:47 PM)

Hi Ale,
I'm not sure what you mean by the Paste options context menu. Do you mean the paste
dropdown on the standard toolbar?

 


Comment by: Sherry (10/2/2008 7:31:58 AM)

This is outstanding! I have been racking my brains out on how to get through to
people that use paste when they need to use paste special > values. This morning I
had another example, smeone overwrote all my error checking conditional formatting
when the did a traditional paste. Which totally defeated the purpose of the having
the error checking in the first place. Love this code! Thanks so much for taking
the time to write this, it is priceless!

 


Comment by: Jan Karel Pieterse (10/2/2008 10:53:12 AM)

Hi Sherry,
Thanks!

 


Comment by: Aaron Pitman (10/15/2008 3:51:33 AM)

Excellent code. In the MyPasteValues routine, regarding the portion of the code
that checks MoveAfterReturnDirection and decides which direction to move. . .Is
there any way to preserve Excel's feature wherein selecting cell B1 then pressing
TAB, TAB, TAB, then ENTER moves back to B2? This code has the cell E2 selected.

 


Comment by: Jan Karel Pieterse (10/17/2008 2:15:43 AM)

Hi Aaron,

I am not sure I understand what you mean. Does the code sample change the behaviour?

 


Comment by: ArtHudson (1/8/2009 1:15:39 PM)

Just as a formula in B21 can be cached as a cell comment in B20 (the headings row), and then can be extracted from that cell comment as a formula in B21 for filldown, so I suspect that sheet-resident validation can be cached somewhere (perhaps in a macro?) so that it can repopulate the validation in the sheet each time the sheet is activated.

I store my validation list in an external file named "List_"&<ColHdg>&".xls" so I have no need to recreate a destroyed validation list that resides in any sheet. I had to write an app to do that, but it was worth it.

 


Comment by: Steve (5/11/2009 3:06:12 AM)

Great piece of code! I got really excited for a moment as I have been looking for the foolproof dis-abler for some time however I tried double clicking a cell with contents, highlighting the text then Ctrl-c, followed by esc then double click a new cell then ctrl-v and voila - copy & paste.

great code though!

 


Comment by: Jan Karel Pieterse (5/11/2009 4:16:25 AM)

Hi Steve,

Of course, that can't be avoided since you can't run code when you're in cell edit mode.
Luckily this particular method of pasting does not zap validation or cell formatting, so no big harm is done.

 


Comment by: seiji (5/12/2009 3:01:48 AM)

Excellent code. The only problem I see here is if the user is pasting hundreds of lines of data, the code's going to be VERY slow.

 


Comment by: Jan Karel Pieterse (5/12/2009 9:55:44 PM)

Hi seiji,

Well, yes if you leave in the validation check it might be.
The situation where I used this code didn't allow for pasting huge amounts of data: the worksheet had restricted input areas which were limited in size.

 


Comment by: Elliot (6/18/2009 12:46:16 PM)

Great Code!

Per above - you indicated that you planned on adding code for excel 2007.

Do you have a timeframe in mind?

Thanks so much in advance.

Regards,
Elliot

 


Comment by: Jan Karel Pieterse (6/21/2009 3:08:44 AM)

Hi Elliot,

Ouch, that is the risk of writing that on plans to add something in future. I haven't had time to look into this yet.

 


Comment by: Peter (7/15/2009 6:05:36 PM)

Very thorough and well-written. But why ask a spreadsheet to do the job of a database? MS Access has always enforced validation regardless of how data is entered. No code required.

 


Comment by: Jan Karel Pieterse (7/16/2009 2:42:13 AM)

Hi Peter,

Because people don't always need all functionality of a database, but sometimes do need the calculation power and versatility of Excel.
WHether or not Excel is the right tool for the job is something I discuss with my customers on many occasions. Sometimes they agree Excel isn't right for the job and then I offer building their app in Access.

 


Comment by: Bill T. (9/28/2009 9:32:00 AM)

Love this.
You've saved me hours of development. I have a sheet I need to use it in but I need for them to be able to paste from an external source, that is to say Paste Special, Values only. I have tried several things but cant find the right change to make to the code to allow them to do this. I'm sure it's something right in front of me, that's why I don't see it right? We are still using an old COBOL database till SAP is up and running and my users need to be able to Copy and Paste into the worksheet and I want it to paste values only when ever and however they try to paste.
Thanks

 


Comment by: Jan Karel Pieterse (9/28/2009 10:47:57 AM)

Hi Bill,

The example already does a paste values.
It should suffice to copy the entire code into a normal module and add
CatchPaste
to the Workbook_Open event and
StopCatchPaste
to the Workbook_BeforeClose event.
See the sample file.

 


Comment by: Rico (10/28/2009 1:31:03 PM)

Hi,

Great article, quick question, the example works for copy and paste but when I try CUT and paste it doesn't allow pasting in the value

I think it might be a limitation when your using paste special.. is there a way around this to allow cut and paste to work?

Thanks

Rico

 


Comment by: Jan Karel Pieterse (10/30/2009 11:48:41 AM)

Hi Rico,

You are right, after cutting, paste special is unavailable and hence this method does not work.
You might consider adding this to the beginning of the MyPaste routine:

    If Application.CutCopyMode = xlCut Then
        MsgBox "Cutting is not allowed!"
        Exit Sub
    End If

 


Comment by: Barb Reinhardt (12/14/2009 7:07:02 AM)

Is there some way to disable a right click to Insert? How would that be done?

Thanks,
Barb

 


Comment by: Jan Karel Pieterse (12/15/2009 1:25:02 AM)

Hi Barb,

You would have to find out the Id of the appropriate menuitem and add that to the list of Id's the example code already handles.

Id 3191 is "Insert Cut Cells" and Insert Copied cells" on the cells toolbar:

    'Insert Cut cells button
    AddCatch "Dummy", 3185
    AddCatch "Dummy", 3191

 


Comment by: Barb Reinhardt (12/15/2009 5:56:45 AM)

Where would I find a complete list of the ID's?

Thanks again,
Barb

 


Comment by: Jan Karel Pieterse (12/15/2009 8:14:10 AM)

Go to my downloads page and locate a file called xlmenufundict. It contains them all.

 


Comment by: YIANNIS (1/6/2010 5:17:24 PM)

I am writing this to you since you must be veeery good with anything that has to do with excel, and i need some help.

I have a worksheet with formulas and what i want to do is to copy only the value of a formula to another cell.
I know that there is the PasteSpecial for it, but in my case i want to copy the value only when certain criterias are met.

For instance, lets say A1=5, A2=6 and A3=SUM(A1:A2).
I want to paste only the value of cell A3 to another cell (lets say D1). Thats the easy part, copy cell A3 and Paste Special --> Values to cell D1. in that case the result is 11 and will remail 11 nomatter how many times i will change the values to cells A1 and A2.
But, if i want to do the above when certain conditions are met (lets say only when SUM(A1:A2)>10) i need a function. A function that can be used with IF and ofcourse the result not to change nomatter what the values of A1 and A2 will be in the future

Since i am not as keen as you in excel, i would appreciate it if you could suggest where to look for this.

Thanx in advance

Yiannis

 


Comment by: Jan Karel Pieterse (1/6/2010 11:12:37 PM)

Hi Yiannis,

The best way to achieve this is by using a bit of macro code (VBA). Right-click the sheet's tab and select "View Code". Now paste this code in the window that opens:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    'Only process changes in column A or B
    If Intersect(Target, Range("A:B")) Is Nothing Then Exit Sub
    If Me.Cells(Target.Cells(1, 1).Row, 3).Value > 10 Then
        Me.Cells(Target.Cells(1, 1).Row, 4).Value = Me.Cells(Target.Cells(1, 1).Row, 3).Value
    End If
End Sub

 


Comment by: Yiannis (1/7/2010 3:43:22 AM)

Thanx for replying me so fast.
I tried what you suggested but i get an error message when debugging "Invalid use of Me keyword"

How can i use the above script inside an IF function? Supposing that a cell contains a formula and i want to copy the value of this formula to another cell and freeze it only if it is >0, can i use something like =IF(D#>0;Worksheet_change(D#);0)??? Where # can be any cell number.

And one more thing, why use Sub and not Function?

Once again thanx for your time and effort

Yiannis

 


Comment by: Jan Karel Pieterse (1/7/2010 4:18:14 AM)

Hi Yiannis,

You must have put the code in the wrong place. Please re-read my previous comment precisely.

 


Comment by: Jaafar Tribak (3/6/2010 10:32:12 PM)

I recently used a different approach to cath the paste event in excel. See link below.

It's not finshed yet ( Still needs a propper handling of "Paste Special" but seems to work fine otherwise.

The code uses a Windows hook namely the WH_CALLWNDPROC hook to catch all Cut/Copy operations and delay clipboard rendering . When a paste is requested the hook procedure can then control what/how/if the data can be pasted.

http://www.mrexcel.com/forum/showthread.php?t=451174

 


Comment by: Jan Karel Pieterse (3/7/2010 11:24:13 PM)

Hi Jaafar,

Thanks!

Looks very complex to me.
I'd prefer to keep it simpler and use the native Excel/VBA functionality here.
Keep in mind that you'll have to use compiler directives to have this work in Office 2010 64 bit:
www.jkp-ads.com/articles/apideclarations.asp

 


Comment by: Mohamed Singh (4/18/2010 3:55:58 PM)

Hi,
To be truthful i hav sought help with this on 2 other sites but no-body seems to solve this "Difficult Copy & paste Between 2 Workbooks". Plse could you assist.


I have 2 workbooks. I need to Copy FROM any cell in range A:A in Workbook1 Sheet1 by double-clicking the contents of which must be pasted into Workbook 2 Sheet 11 next available cell in column D. The adjacent cell C (in Workbook 2) has cell-validation & Cell E in row above (in Workbook 2) has INDEX formula. Both of these (cell-validation & INDEX) cannot be removed since they facilitate audit control & eliminate validation errors for an accounting software. Column E is locked for INDEX formula so once the copy & paste is complete , the INDEX will give the corresponding detail.

The vba to work in excel 2003 - 2007.

Thanking you in advance for your assistance.Plse respond even if this cannot be done.

 


Comment by: Jan Karel Pieterse (4/19/2010 12:53:54 AM)

Hi Mohamed,

Try this:
- Right-click the tab of the worksheet Sheet1 in Workbook1 and select "View code"
- paste this code:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Cancel = True
    Target.Cells(1, 1).Copy
    With Workbooks("Book2.xls").Worksheets("Sheet 11")
        .Range("D" & .Rows.Count).End(xlUp).Offset(1).PasteSpecial xlValues
    End With
    Application.CutCopyMode = False
End Sub


- Make sure you change the name of the workbook and worksheet in the code sample above.

 


Comment by: Mohamed Singh (4/19/2010 4:58:36 AM)

Thank you for such a speedy response-You have exceeded all expectations!

Did as you directed, but the error message is:
"RunTime error '1004':
The cell or chart you are trying to change is protected and therefore read only... "
Yes, the worksheet is protected but the range D14:D213 is unprotected since,up to now entries were typed into same range.
The "Protect Sheet" settings "Select Locked cells" & "Select Unlocked Cells" are both ticked.
Also, it is attempting to paste into the last cell in column D219 not in next available cell. Row 219 is last row in worksheet.
I forgot to mention that column D has cell validation- to test i removed cell validation but still came up with same RunTime Error '1004' & same attempt to paste in last cell.


 


Comment by: Jan Karel Pieterse (4/19/2010 5:40:35 AM)

The code tries to paste a value into the first non-empty cell in column D, because that is what I understood was needed. Please advise if this is worng.

 


Comment by: Mohamed Singh (4/19/2010 6:38:59 AM)

Hi Jan,
Thank you again for taking time & effort to reply.
Your understanding is 100% correct. I tested code on the actual source workbook pasting to another ordinary non-formatted, non-formulated workbook & it worked 100% as required. But it fails to work within the parameters of my workbook with the validations etc. as initially described.
Thanks
Mohamed

 


Comment by: Mohamed Singh (4/19/2010 6:50:27 AM)

Hi Jan,
Sorry this is a follow-up to my last response & your response dated 4/19/2010 5:40:35 AM.

Please refer to original spec.
"I need to Copy FROM any cell in range A:A in Workbook1 Sheet1 by double-clicking the contents of which must be pasted into Workbook 2 Sheet 11 next available cell in column D."

Hope i'm not being a bother.
Kind Regards

 


Comment by: Mohamed Singh (4/20/2010 12:29:33 PM)

Hi,

I tried to respond but not sure why my message did not get thru.

Code needs to paste in first empty cell in column D. See spec :"the contents of which must be pasted into Workbook 2 Sheet 11 next available cell in column D.

Thanking you in advance,
Mohamed

 


Comment by: Jan Karel Pieterse (4/20/2010 10:09:37 PM)

Hi Mohamed,

Sorry, I didn't have time to review your posts. They are not visible until I set them to public.

Modify my code to:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Cancel = True
    Target.Cells(1, 1).Copy
    With Workbooks("Book2.xls").Worksheets("Sheet 11")
        .Protect Password:="password", UserInterfaceOnly:=True
        .Range("D" & .Rows.Count).End(xlUp).Offset(1).PasteSpecial xlValues
    End With
    Application.CutCopyMode = False
End Sub

 


Comment by: Mohamed Singh (4/23/2010 2:18:16 AM)

Hi Jan,

Thank you for being so transparent.

I tried n tried..., since your code works perfectly in an ordinary worksheet it appears that because of the structure of the worksheet, it cannot identify the next vailable cell in column d.

Thank you.
Mohamed

 


Comment by: Mohamed Singh (4/25/2010 11:03:29 PM)

Hi Jan,

Please could you advise:

1. I need to set
1.1 print area & page breaks with vba
1.2 footer with vba so that these settings cannot be altered. As an example i wish to place company name in the left footer, page number in right footer.

2. I have read that if the macro is interrupted while running, macros could become disabled & would need enabling. Is there code that can be set in the workbook open event to always reset vba to be enabled?

 


Comment by: Jan Karel Pieterse (4/26/2010 12:26:58 AM)

Hi Mohamed,

To set the print settings by VBA I advise you to record a macro while doing so. You can then use the recorded code to do what you need.

If you want to prevent the user from changing these settings, protect the worksheet afterwards.

Start your macro with this line to achieve that:

Worksheets("Sheet1").Protect Password:="Password", UserInterfaceOnly:=True


A macro can be interrupted and restarted as many times as you like. Enabling/disabling of a macro is *only* done when the file is opened. If you want macros to be enabled all the time, consider digitally signing the macro or placing the workbook in a trusted folder.

 


Comment by: Marcel Schilling (4/27/2010 1:33:38 AM)

Hi Jan,

I'm new to VBA, but is there no easier algorithm to do what you do?
I'm thinking about something like:


if any paste should be done
if target cell has no validation rule
     let the pasting be done
else
     let the pasting be done
     if target cell still has no validation rule anymore
         undo last action (the pasting) and display message
     endif
endif
endif


Is there anything impossible in that pseudo code?
Or would that forbid pasting into validated cells because the validation rule is removed by pasting no matter wether rule was broken or not?

Please tell me your opinion on this and thank you very much for sharing your code.

Marcel

 


Comment by: Jan Karel Pieterse (4/27/2010 1:35:49 AM)

Hi Marcel,

Very valid question.
There are some potential problems:

- There is no way of knowing how many cells are involved after doing the paste without doing the actual paste
- There is no way of telling whether the source cells contain the same validation rules as the target cells, so any validation rule is always overwritten.

 


Comment by: Mohamed Singh (5/4/2010 2:55:21 AM)

Hi Jan,

Thank you for the suggestion re:recording the vba for my print settings, it has worked.

I have another question:
I wish to convert numbers to words. I have used the Function Wizard -->User defined-->SpellNumber. This works very well EXCEPT it results in "...Dollars and cents". As i am in South Africa, our currency is "Rands ", how do i change the Dollars" to "Rands"?

Kind Regards
Mohamed Singh

 


Comment by: Jan Karel Pieterse (5/5/2010 7:36:42 AM)

Hi Mohamed,

You would have to open the function in the VBA editor and edit the VBA code.

 


Comment by: Jaafar tribak (5/5/2010 1:59:59 PM)

Hi all.

A while ago I used a different approach which is more involved but yu may want to take a look at :

http://www.mrexcel.com/forum/showthread.php?t=451174&highlight=paste

 


Comment by: Asher Rodriguez (5/10/2010 6:21:48 AM)

Hi Jan Karel,

At the top of this page you said "NB: as it is now, the article focuses on Excel 2000 to 2003. I will add the needed steps for Excel 2007 later on."

I am using Excel 2007. Is there anything else I need to do to try this code for 2007?

 


Comment by: Jan Karel Pieterse (5/10/2010 11:23:27 PM)

Hi Asher,

For Excel 2007, you need to add ribbonX XML code to the workbook to repurpose the paste buttons of the ribbon.
Unfortunately I lack the time right now to add an explanation on how to do than.
I'd recommend buying the RibbonX book by Ken Puls et al. to find out how that ribbon customising stuff works.

 


Comment by: Mohamed Singh (5/28/2010 2:14:45 AM)

Hi Jan,

I have to set an internal audit control such that if the transaction total is greater than a preset value then a password is required to run an update macro:
If the Total in Column AF11 Sheet11 is greater than the Total in Column I214 Sheet11, then a password needs to be enterd for Sub Update() to run on sheet 5.
I have got as far as the user form:
Name: "frmPassword"

TextBox:-
Name: "txtPasswrod"
Properties, maxlength=5, PasswordChar=*

CommandButton:-
Name: cmdOK

Please could you help?
Many Thanks
Mohamed

 


Comment by: Jan Karel Pieterse (5/28/2010 6:39:26 AM)

Hi Mohamed,

So you would have a normal module which contains something like this:

Option Explicit

Sub OpenForm()
    Dim ufPasswrd As frmPassword
    If Worksheets("SHeet11").Range("AF11").Value > Worksheets("SHeet11").Range("I214").Value Then
        Set ufPasswrd = New frmPassword
        With ufPasswrd
            .Show
            If .tbxPassword.Value = "Password" Then
                'call your routine
            Else
                'Wrong password, maybe show a cancel message
            End If
        End With
    End If
End Sub


The userform's code module would only contain this:

Option Explicit

Private Sub cmbOK_Click()
    Me.Hide
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    Cancel = True
    Me.Hide
End Sub

 


Comment by: Mohamed Singh (6/2/2010 3:10:15 AM)

Dear Jan,

A very big thank you for the "internal audit password". This worked out very well & saved me loads of time.

Thanking you sincerely
Mohammed

 


Comment by: Jan Karel Pieterse (6/2/2010 3:22:16 AM)

Hi Mohammed,

You're welcome!

 


Comment by: Mohamed Singh (6/3/2010 3:17:26 AM)

Hi Jan,
I seek your assistance again:
I have 2 list boxes, List Box 1 & List Box 2.
Is it possible to have 10 named ranges in List Box 1:
Company1
Company2...through to
Company10
then an add command button which will take the selection from list Box 1, for example company1,& populate List Box2.

Thanking you sincerely,
Mohamed Singh


 


Comment by: Jan Karel Pieterse (6/3/2010 3:38:36 AM)

Hi Mohamed,

I would like to advise you to ask your question here:
http://www.eileenslounge.com
People over there are very friendly and helpful and chance you receive help is better than asking me (since I might be too busy).

 


Comment by: Mohamed Singh (6/3/2010 6:33:27 AM)

Dear Jan,

Many thanks for the suggestion, & for taking time to respond.

Best Regards
Mohamed

 


Comment by: Jan Karel Pieterse (6/3/2010 6:43:31 AM)

Hi Mohamed,

You're most welcome.

 


Comment by: Dathai (10/20/2010 9:52:55 AM)

Jan,

Absolutely superb piece of coding!
Thanks very much for posting this. I've just hit a requirement for exactly this type of paste interception, began writing the code then thought I'd have a quick check on Google. I don't think I've ever been lucky enough before to stumble onto something that met my requirements so perfectly - you've saved me hours of work.

Thanks again,
Dathai

 


Comment by: Jan Karel Pieterse (10/20/2010 10:57:10 AM)

Hi Dathai,

What can I say,
glad I could save you some time.

 


Comment by: S Griffin (10/29/2010 3:24:53 PM)

Wonderful post! Do you have plans to code for 2010 anytime soon?

 


Comment by: Jan Karel Pieterse (10/30/2010 11:09:15 AM)

Hi,

Not really, I'm a bit overwhelmed. Basically, most of the code is the same, all that needs to be added is RibbonX code to change the behaviour of clicking the ribbon past buttons.

 


Comment by: Vernon Wankerl (11/23/2010 1:05:26 PM)

I am pleased with this code. There is an application I am working on that will benefit from it immediately. Being a VBA developer (and programmer in general), I find reading code helpful in my future developments. I have one question about this code: Could you discuss the reason for doing the OnTime functions in the Workbook_BeforeClose and Workbook_Deactivate code? I don't understand what they are to accomplish.

Thanks!

 


Comment by: Jan Karel Pieterse (11/24/2010 12:20:39 AM)

Hi Vernon,

The OnTime in BeforeClose schedules to reinstate the catchpaste code in case the user decides to cancel shutting down Excel: the timed proc will run and re-instate the appropriate settings.

If however the user just closes the file, but not Excel, then first before_close runs. It sets that timer. But after that, DeActivate is fired, which unschedules the timed proc again, thus preventing the workbook from being opened as soon as the timer reaches its value.

 


Comment by: Tony Brazao (12/7/2010 3:05:02 AM)

Hi there

Thanks a million for this code. I am using it successfully.

There is just one issue that I can't seem to resolve. I am unable to trap the Cells option on the Insert menu. On my sheet, I have disallowed inserting Columns but want to allow Insert Rows. This works well mostly but when you have a row highlighted, you can use the Insert/Cells menu. This will insert a row in this case and I can't trap it. I have tried all the menu ID's but none seem to work. The ones I am using are (295, 296, 297, 3181, 3183, 3185, 3187, 30005). None of these seems to be Insert/Cells?

You help would be greatly appreciated.

Tony

 


Comment by: Jan Karel Pieterse (12/7/2010 3:52:47 AM)

Hi Tony,

I think the Id you need is 3182:

    MsgBox Application.CommandBars("Worksheet menu bar").Controls(4).Controls(1).ID

 


Comment by: Tony Brazao (12/7/2010 4:24:49 AM)

Hi Jan

Thanks for the speedy response. I tried 3182 and it never worked. In my AddCatch, the FindControl does not find any of these controls (3181, 3182, 3184, 3185, 3187), even looking directly at the Cell menu as you've done.

It's interesting that when I ran your MsgBox code (MsgBox Application.CommandBars("Worksheet menu bar").Controls(4).Controls(1).ID) the first time, I got 3182 but after that I got 295. This makes me think that that these controls depend on the context.

Should I be calling CatchPaste somewhere else as well as in the Open and Activate events of the workbook (e.g when a row is highlighted - though I don't know if there is an event that I can use for this)?

Many thanks,
Tony

 


Comment by: Jan Karel Pieterse (12/7/2010 6:38:12 AM)

Hi Tony,

The control on the Insert menu might change depending on the current situation (like whether or not an entire row is selected).

If you use the AddCatch routine as shown above, then it should work. By adding a custom commandbar (which AddCatch does) and adding the specific control ID to that bar, this problem is circumvented.

 


Comment by: Tony Brazao (12/10/2010 2:44:18 AM)

Thanks for looking at it Jan

After playing around with this for a bit, it turns out that if the worksheet is protected, these menu options (like Insert) are greyed out, as one would expect, but if you get a handle on the control (directly on the relevant menu) then use the OnAction property to run your code, it actually makes those items available for use (un-greys them). I think this is the route I am going to take (for Insert and Delete at least) - Protect the sheet and "allow" Insert/Delete using OnAction. You just have to remember to reset the control to the default behaviour at the end (otherwise Excel will try to run the OnAction routine for evermore, as I discovered 5 minutes before our Christmas lunch!).

Take care,
Tony

 


Comment by: Jan Karel Pieterse (12/10/2010 3:22:36 AM)

Hi Tony,

Very good point, thanks for letting us know!

 


Comment by: Susan Bennett (12/14/2010 8:04:28 AM)

Do you have comparable code for MS Excel 2007? I am a new VB programmer and would benefit seeing the udpated code.

Regards

 


Comment by: Jan Karel Pieterse (12/14/2010 9:29:06 AM)

Hi Susan,

I don't have that available right now, but let me suggest to visit the Microsoft MSDN site, there are examples on how to repurpose controls in Excel 2007 ribbon:

http://msdn.microsoft.com/en-us/library/bb462633(v=office.12).aspx

 


Comment by: Shiv Pati Tripathi (12/27/2010 5:23:24 AM)

It Seems does not work while er are trying tp paste something using Ribbon(Paste option)

 


Comment by: Jan Karel Pieterse (12/27/2010 1:51:25 PM)

Hi Shiv,

That is correct, the article currently is only valid for Excel 2003 and older, except for the short-cut key interseptions and the past eoptions on right-click menu's.

 


Comment by: Orjan Dill (1/3/2011 9:07:18 AM)

Hello,

An excellent solution you have provided for us Excel users.


I might have a suggestion to improve your solution.

In your answers abowe you have stated that we can't know how big area we are expect to paste but I have discovered a workaround for me where I paste "empty comments" in the range and then loop through the new selection to identify any locked cells and hence I know the new area.

Perhaps there are other solutions to paste something else than comments which is not so commonly used or visible, if you have any suggestions to improve my ideas I would be happy to learn.

In the module "MyPasteValues" i have the following code snippet.

[ Selection.PasteSpecial Paste:=xlComments

        For Each rngCell In Selection
'Search through the selection to identify any locked cells.
            If rngCell.Locked = True Then strLockedCells = strLockedCells & " " & rngCell.Address(False, False)
        
        Next
        
    End If

'If there is no locked cells in the area to be pasted then paste is allowed else "do nothing".
            If strLockedCells = "" Then
]

Best Regards,

Orjan Dill

 


Comment by: HIEN (3/15/2011 3:13:22 AM)

Hi JAN, what does "Dummy" mean in your code ? I don't understand this word. Is it Procedure or Event ???

 


Comment by: Jan Karel Pieterse (3/15/2011 4:18:12 AM)

Hi Hien,

It is just the name for a temporary toolbar the code creates. It could have been any unlikely name for a toolbar.

 


Comment by: HIEN (3/16/2011 8:44:02 PM)

Hi JAN,

Your code is perfect on Excel 2003, but on Excel2007, it's not work with ribbon bar (PASTER icon or COPT icon on ribbon bar). Could you please re-code it to apply on Excel 2007 ???

Thks so much.

 


Comment by: Jan Karel Pieterse (3/17/2011 9:11:46 AM)

Hi Hien,

You're right. I know this page needs an update for Excel 2007 and 2010, but I lack the time!

 


Comment by: Cris (4/3/2011 11:06:23 AM)

Thanks!

Its also a nice tutorial on how to catch any event. has many pieces to learn and obviously prevents from cutting the format.

Respect to Microsoft Im very dissapointed cause its impossible then to cut/paste only the values if not coding a lot. Im using Office XP.
What the hell was thinking the developers when designing Microsoft Excel? :D Why they forgot to include in the options some checkbox or anything related to this?

It reminds me the mine field of bugs you find when programming charts...

WEll, thanks for making this easy to us.

Best Regards

 


Comment by: Martin Gaub (9/22/2011 1:39:34 AM)

There is a little typo in the following code example:

Section: "Explanation Of Parts Of The Code"
Subsection: "Catching Clicks On Toolbar And Menu Controls"
After "And the code to create an instance of this class could look like this:"

Set oCtl = Application.CommandBars("Cells").FindControl(ID:=3185, recursive:=True)

Should read:        ...("Cell")...


 


Comment by: Jan Karel Pieterse (9/22/2011 7:44:55 AM)

Hi Martin,

Well spotted, thank you!

 


Comment by: Yingxi Chen (9/28/2011 9:31:32 AM)

I modified the code a little bit in MyPasteValues() sub to deal with hidden row/column and auto filtered range. Here is the code of the select case section:

........
Select Case Application.MoveAfterReturnDirection
    Case xlUp
        Do
            ActiveCell.Offset(-1).Select
        Loop While ActiveCell.EntireRow.Hidden = True
    Case xlDown
        Do
            ActiveCell.Offset(1).Select
        Loop While ActiveCell.EntireRow.Hidden = True
    Case xlToRight
        Do
            ActiveCell.Offset(, 1).Select
        Loop While ActiveCell.EntireColumn.Hidden = True
    Case xlToLeft
        Do
            ActiveCell.Offset(, -1).Select
        Loop While ActiveCell.EntireColumn.Hidden = True
End Select

 


Comment by: Jason (9/28/2011 3:18:08 PM)

you touched briefly on the drag and drop being disabled above. I'm trying to catch the "drop event" which I believe is the
.cut Destination:= somerange
in the Worksheet_Change(ByVal Target As Excel.Range) but the cutcopymode status does not have the 'xlcut' value.
I want to do something like this:
if cells are edited or changed do nothing
if cells are draged and dropped then
test for cells in the Target range having validation
if true then application.undo
if false do nothing

I can do this in a single cell, but have trouble in ranges
I've played around with the code below butit always fails in one way or the other:

Worksheet_Change(ByVal Target As Excel.Range)
If Application.CutCopyMode = True Then
Dim CVal As Integer
For Each Wscell In Selection.Cells
On Error Resume Next
CVal = Wscell.Validation.Type
If CVal > 0 and Cutcopymode=xlcut Then
Application.EnableEvents = False
Application.Undo
On Error GoTo 0
GoTo ExitSub
End If
Next
End If

 


Comment by: Jan Karel Pieterse (9/28/2011 11:26:53 PM)

HI Yingxi,

Thanks!

 


Comment by: Jan Karel Pieterse (9/28/2011 11:39:28 PM)

Hi Jason,

In order to detect whether the user has cut something, you have to first undo that operation. CUmbersome, because if you want to allow the operation afterwards, you'll somehow have to redo the operation if all is well. This appears to work, but not for dragging a range with the mouse:

Option Explicit

Dim mbNoEvent As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)
    If mbNoEvent Then Exit Sub
    mbNoEvent = True
    Application.Undo
    If Application.CutCopyMode = xlCopy Then
        Application.Undo
    ElseIf Application.CutCopyMode = False Then
        Application.Undo
    Else
        MsgBox "Cutting and pasting is not allowed!"
    End If
    mbNoEvent = False
End Sub

 


Comment by: Jason (10/4/2011 11:12:16 AM)

Jan, thanks' again for your quick response. you got me pointed in the right direction with the application.undo and then testing the ranges. I did find a solution to testing a DragandDrop event:
The cell fill color and pattern are reset to none (16777215 for colr and xlnone for pattern) simply filling the cells with White and a border (only to define each cell) allows to test for a change in the color/pattern values when the cells are dragged and dropped. I have cells that I want to test for validation (any test could be done),so I undo the move (your idea) and then test the source (Target) and destination (Selection) ranges for validation. if there is validation I keep the undo. if there's not validation I undo the undo and keep the DragandDrop. then I reformat the Target cells (the reformatting is not included here as I use another Procedure to do this as I have some custom formatting). I use the worksheet_change event to trap the Drag and Drop. I found it useful to set the ranges (source and destination) to string variables instead of Range variables as the Selection Range changes in certain cases. I also trap a copy/ paste event (your idea). this works on both single and multiple cell ranges. I self taught VBA so often times my code is not as elegant as it could be and I do not use hungarian notation for my variables in this case. Hopefully this will be useful to someone trying to trap a DragandDrop event. Thanks' again for getting me pointed in the right direction!
see next post for code

 


Comment by: Jason (10/4/2011 11:12:53 AM)


Public WsCHTarget As String
Public WsCHSelection As String
Public CMTSCopyMode As Boolean

Public Sub Worksheet_Change(ByVal Target As Excel.Range)
CMTSCopyMode = False
If Application.CutCopyMode = xlCopy Then CMTSCopyMode = True
WsCHTarget = Target.Address
WsCHSelection = Selection.Address

Application.EnableEvents = False:Application.ScreenUpdating = False

CheckValidation

Application.EnableEvents = True: Application.ScreenUpdating = True ': Application.CutCopyMode = False

End Sub


Public Sub CheckValidation()

If CMTSCopyMode = True Then GoTo BeginTest
If Range(WsCHTarget).Interior.Pattern = xlNone And Range(WsCHTarget).Interior.Color = 16777215 Then GoTo BeginTest
GoTo ExitSub
''''''''begin test for moved or overwritten validation cells''''''''
BeginTest:
Dim CVal As Integer, WsCell As Range
Application.EnableEvents = False: Application.ScreenUpdating = False: ActiveSheet.Unprotect
On Error Resume Next
Application.Undo
''''''''test target begin range''''''''
For Each WsCell In Range(WsCHTarget).Cells
On Error Resume Next
CVal = WsCell.Validation.Type
If CVal > 0 Then
GoTo ExitSub
End If
Next
''''''''test selection destination range''''''''
For Each WsCell In Range(WsCHSelection).Cells
On Error Resume Next
CVal = WsCell.Validation.Type
If CVal > 0 Then
GoTo ExitSub
End If
Next
''''''''no validation cells - found redo the undo
On Error Resume Next
Application.Undo
''''''''
ExitSub:
Application.CutCopyMode = False
ActiveSheet.Protect
End Sub

 


Comment by: Jan Karel Pieterse (10/5/2011 12:09:01 AM)

Hi Jason,

Thanks for the code.
There is no need to worry about not using Hungarian variable names notation. However, I'm no fan of using Goto's in code except for the On Error Goto statement.
I think your code can be rewritten to:

Public Sub CheckValidation()
    Dim CVal As Integer
    Dim WsCell As Range
    Dim bStop As Boolean
    If CMTSCopyMode = True Or _
     (Range(WsCHTarget).Interior.Pattern = xlNone _
        And Range(WsCHTarget).Interior.Color = 16777215) Then
        Application.EnableEvents = False
        Application.ScreenUpdating = False
        ActiveSheet.Unprotect
        On Error Resume Next
        Application.Undo
        ''''''''test target begin range''''''''
        For Each WsCell In Range(WsCHTarget).Cells
            On Error Resume Next
            CVal = WsCell.Validation.Type
            If CVal > 0 Then
                bStop = True
            End If
        Next
        If bStop = False Then
            ''''''''test selection destination range''''''''
            For Each WsCell In Range(WsCHSelection).Cells
                On Error Resume Next
                CVal = WsCell.Validation.Type
                If CVal > 0 Then
                    bStop = True
                End If
            Next
            ''''''''no validation cells - found redo the undo
            If bStop = False Then
                On Error Resume Next
                Application.Undo
            End If
        End If
    End If     ''''''''
    Application.CutCopyMode = False
    ActiveSheet.Protect
End Sub

 


Comment by: xoan.ninguen (1/10/2012 1:05:47 PM)

Hy Jan,

I experienced the following when playing this macro (using XL2003 on WinXP SP3):
- It seems to be very busy on opening Excel File (even with the original downloaded file).
- After a crash I could not recover all functions.

I must confess (for what is the last point mentioned) that I've playing a lot with this and that, and I made a few changes to your macro, more specifically on CatchPaste procedure, adding dummy dissabling controls for ID:19 (copy), 21 (Cut), 478 (Delete), 847 (DeleteSheet), 296 (insertRow), 297 (insertRow),...

Ok that is not the point. I could near restore all these but for 847, 296 and 297 (don't know why these can't), I would rather reinstall XL that continue testing (although fun).

What I came here for is to explain that I could achieve dissabling all these without the class. I can get to open that file with macro with no delay (as usual in XL).
I tried to append the code used but its 10105 characters long... :(

Will Email you...




 


Comment by: Jan Karel Pieterse (1/11/2012 7:38:21 AM)

Hi Xaon,

Thanks for your comments. If your current code does what you need, then of course my suggested method is overkill.

What the method shown here does do however is instead of disabling all those controls, redirecting them to my macro. Your solution seems to only disable most controls and redirect just the shortcut keys.

 


Comment by: Rashid Khan (2/16/2012 2:48:45 AM)

Hi,
Is there any event to capture CTRL+V in Word VBA?
As I have already running WindowSelectionChange.

-
Thanks,
Rashid

 


Comment by: Jan Karel Pieterse (2/17/2012 7:29:47 AM)

Hi Rashid,

Please visit www.eileenslounge.com to ask your question; I haven't got enough Word knowledge to answer this question.

 


Comment by: Garland Pope (5/7/2012 10:59:10 PM)

Has anyone found that in Excel 2010 you cannot repurpose the special paste buttons in the Paste gallery on the Home tab of the ribbon? The customUI XML for Paste successfully handles the main Paste button, but the others (PasteSpecial, PasteFormulas, PasteFormatting, etc.) seem to have no effect whatsoever. It seems that this post would indicate that these controls cannot be accessed because they are part of a gallery: http://www.add-in-express.com/forum/read.php?PAGEN_1=1&FID=5&TID=9679

Any ideas?

 


Comment by: Jan Karel Pieterse (5/8/2012 12:35:20 AM)

Hi Garland,

Indeed you can repurpose them, I could only disable the paste gallery like so:

<command idMso="PasteGallery" enabled="false"/>

 


Comment by: Garland Pope (5/8/2012 12:34:58 PM)

Thanks for the response, Jan. Can you clarify whether you were agreeing with me that you _CANNOT_ repurpose these buttons or confirming that you _CAN_ do so? If you think you can do it, can you check your example file to confirm that it works? I can paste from all of the buttons in the paste gallery (but not the main Paste button on the ribbon).

Assuming that you can't, it seems that this leaves a considerable hole in this solution (not your fault, but Microsoft's). The solution rightly encourages the user to cancel and use the Paste Values option instead of doing a regular paste (otherwise the undo history is lost, and that's unacceptable to me). It seems that the two places from which you can do a Paste Values are the gallery on the ribbon and the mini gallery in the context menu. If you have to disable the whole paste gallery to prevent the user from using the other paste functions, then how can the user do a Paste Values?

 


Comment by: Jan Karel Pieterse (5/8/2012 10:54:37 PM)

Hi Garland,

Seems you cannot. I agree this is not very conveinient.
But at least disabling the gallery does leave the paste button itself available.

 


Comment by: Rita (7/20/2012 7:42:38 AM)

Is there any way to capture a Copy or Paste event if the user right-clicks and selects a Copy or Paste option from the pop up menu ?

 


Comment by: Jan Karel Pieterse (8/7/2012 11:33:53 AM)

Hi Rita,

The class clsCommandBarCatcher and its associated demo code in the routine called "CatchPaste" should handle that indeed.

 


Comment by: Kyp (8/23/2012 4:53:36 PM)

As with the comment by: Brent Leslie (4/1/2008 7:11:30 PM)
I also use merged cells (alot).
I tried to use the line that Brent used, however I am getting the "Run-Time Error 9, Subscript out of range".
Is there a way to fix this?
Thanks in advance,
Kyp

 


Comment by: Jan Karel Pieterse (8/24/2012 11:33:52 AM)

Hi Kyp,

In order for that code to work, you must have a worksheet with the name as stated in that code (or adjust the name in the code) and a named cell with the name as stated in the Range() object.

 


Comment by: Rafael Stern (5/2/2013 10:53:56 PM)

Once again thanks for this code.

The only problem I have is that I need to be able to copy and paste from another excel file, respecting the paste values.

This is because we are migrating from one template to another one, and people will need to transfer large information, but I cant afford to turn this macro off because the paste necessarily needs to be values as well.

How can I do that? Is it possible for you to send me an e-mail to let me know you have responded? Thanks in advanced.

Thanks in advanced.

 


Comment by: Jan Karel Pieterse (5/3/2013 11:28:16 AM)

Hi Rafael,

The code should do its work accross workbooks, so if you try to paste data from another workbook, into the workbook with the code, it should "fire off" the alternative paste operation.

 


Comment by: Rafael Stern (5/3/2013 4:20:32 PM)

Hi Jan,

Thanks for your response. Ive tried all sort of methods to copy from another workbook and it seems when I switch the workbooks, it looses the content in the clipboard.

When I copy the content of the cell by selecting the text within the cell and then paste also within the cell content, then it works.

The problem is that is not something easy to teach people to do, since they usually select copy the cell and we are talking about a lot of people using, remotely, with no resources for training.

I saw some comments on your code explaining how this is not possible but I didnt understand what I have to do to make it happen.

 


Comment by: Jan Karel Pieterse (5/3/2013 4:25:50 PM)

Hi Rafael,

I tested this using the example workbook provided with the article, in which it does allow you to simply copy a range of cells from another workbook into the workbook with the code.

This means there is some event code in your file (or in an add-in) which causes the Excel clipboard to be zapped.

 


Comment by: Phil Whitehurst (5/9/2013 9:27:02 PM)

Is there not a simpler way to do this?

In order to paste, they must change the cell selection. So why not use the worksheet change selection event to set

Application.cutcopymode = false


Then the cut / copy operation should be cancelled, so you can't paste.

Phil

 


Comment by: Jan Karel Pieterse (5/9/2013 9:55:34 PM)

Hi Phil,

Sure, that will work to prevent pasting. The point here is to allow paste, but prevent that your formatting and validation goes haywire.

 


Comment by: Mat (5/20/2013 10:16:45 AM)

Hi Jan,

Thanks for your information and code for CatchPaste - its almost working perfectly for me...

The issue that I have is that my users are able to paste over locked cells with this method - this had me scratching my head for hours before I worked out that its some other code in my workbook that is facilitating this. I use the WorkbookOpen event to set the UserInterfaceOnly:=True for several protected worksheets in order for other code to run on these protected sheets. This means that the Selection.PasteSpecial Paste:=xlValues line in MyPasteValues procedure happily overwrites protected cells when pasting.

Do you think my only option is to set UserInterfaceOnly:=False for the current sheet before pasting or is there a better way? (This option may seem easy but several sheets have different passwords so just trying to avoid lengthy code additions to work out what sheet it is then what password to use etc.)

Appreciate any guidance you can provide.

Regards
Mat

 


Comment by: Jan Karel Pieterse (5/20/2013 12:19:54 PM)

Hi Mat,

I guess re-protecting the sheet is the only thing you can do.

 


Comment by: Mat (5/21/2013 12:30:57 AM)

Hi Jan,

Thanks for your fast response. Unfortunately the approach of re-protecting the worksheet looks like its not going to work. It seems that the worksheet.protect method is clearing the clipboard.

Back to the drawing board I guess.

Regards
Mat

 


Comment by: Jan Karel Pieterse (5/21/2013 8:59:15 AM)

Hi Mat,

One way you could do this is by adapting other code that is currently depending on the UserInterfaceOnly bein set to True, so that is unprotects and reprotects before and after the code without setting UI to True. That way, no re-protecting is needed in the paste values part.

 


Comment by: Mat (5/22/2013 4:26:47 AM)

Hi Jan,

Thanks for your comments. In my case there is just too many worksheets and too much code in my workbook which relies on the UserInterfaceOnly property to switch to the unprotect and re-protect approach now, plus this method always leaves the possibility of an unprotected worksheet if the code crashes before re-protecting.

It looks my best option is to just block the paste process completely using your code (i.e. remove the Selection.PasteSpecial line) and advise the users via the msgbox to only use the Paste Values button I have added to the custom ribbon as this doesn't call any custom code so it behaves as I want in relation to pasting onto locked cells.

Regards
Mat

 


Comment by: Jan Karel Pieterse (5/22/2013 7:45:53 AM)

Hi Mat,

There is one (ugly) alternative:

Use sendkeys to mimick pressing control+alt+v (paste special), v (Values), ~ (enter):

SendKeys "^%vv~"

 


Comment by: Vats (8/1/2013 12:05:11 AM)

Hi,

Problem: We have an excel work sheet that accepts data in text and numeric format. Typically the data runs into 1000's of rows, hence copy/paste is the only option.

This excel file is further used to create a csv. Since csv requires set formats, we want to force the applicant to follow the format specified by the validation in the excel document.

I have put in the validation rules, chosen the above consolidated code, protected the sheet and workbook and then tried pasting incorrect formats. The code doesn't seem to prevent the pasting of formats nor does the validation rule create a block. I am using excel 2010.

 


Comment by: Jan Karel Pieterse (8/8/2013 9:06:36 PM)

Hi Vats,

I think the best way to handle this is to have a scrap sheet in which you paste the data and a subsequent macro that massages the data so that it adheres to the rules and then "copies" it over to the "real" export sheet.

 


Comment by: AY (8/28/2013 2:08:36 PM)

Hi Jan,

Just tried to use your (very helpful) solution.

In Excel 2010, it is unfortunately still possible to use the Ribbon paste commands. These are not intercepted.

If I look at the custom RibbonUI code, you set the callback to "MyPasteValues2007"; however, I cannot find this Sub anywhere in the modules.
Is there an oversight from your side or do I need to examine your example file more carefully?

I tried to modify the callbacks to "MyPasteValues", eg. the same sub that is used for the Ctrl+V command. It is unfortunately not working.

Could you give me a hint?

Thanks & regards
AY

 


Comment by: Jan Karel Pieterse (8/28/2013 2:25:56 PM)

Hi AY,

The missing sub is:

Public Sub MyPasteValues2007(control As IRibbonControl, ByRef cancelDefault)
    MyPasteValues
End Sub

(Available in the downloadable demo file)

 


Comment by: AY (8/28/2013 2:52:18 PM)

Hi Jan

Thank you for the reply - I was blind, I'm sorry :-)

It seems to be working now as expected. Nice work!

 


Comment by: Ravi (1/16/2014 9:43:53 PM)

Any chance you'd have any idea how to port this over to Excel for Mac? I'm not even sure where to begin...and can't find very good documentation on the differences except for here: http://www.rondebruin.nl/mac.htm

I've successfully implemented this into my workbook and very happy with the results so far. I just hadn't thought about some of our users have Macs!

 


Comment by: Jan Karel Pieterse (1/17/2014 11:07:08 AM)

Hi Ravi,

I guess the only way is to try?

 


Comment by: Ravi (1/17/2014 1:54:36 PM)

Hi Jan...I think it's futile. I get the impression MS just really doesn't want us using VBA with the Mac Office suite. I spent a fair bit of time yesterday messing around with it on a friend's Mac and just about every line gives a compile error. I can't seem to find an object reference on MS's site. So I give up for now. Thanks for the code though...works brilliantly on Windows machines.

 


Comment by: Jan Karel Pieterse (1/17/2014 4:25:37 PM)

Hi Ravi,

Well, the previous Office for MAC didn't have any VBA, so I expect things will improve.

 


Comment by: Rab (2/7/2014 9:25:21 PM)

Hi Jan,

Many thanks for your sample file for Excel 2010. I have tested this and believe it is still possible to paste things other than values, e.g cell B1 has a validation rule "Allow a whole number between 100000 and 999999".
When copying a formula from cell A1, then selecting cell B1 and right clicking, it allows all the paste special options, and also allows other paste options when clicking on the little drop down arrow under the "Paste" in the Ribbon - is it possible to prevent these please, or even better in my case prevent all types of pasting and dragging and dropping altogether?

Kind regards,

Rab


 


Comment by: Jan Karel Pieterse (2/10/2014 6:54:42 AM)

Hi Rab,

You can turn off drag and drop easily:

Application.CellDragAndDrop = False

I suspect it will take some RibbonX customising to get at the right-click special paste buttons.
There is some exaxmple RibbonX above, but obviously it isn't complete.

These are all the idmso's related to paste:
PasteMenu
Paste
PasteGallery
PasteUsingTextImportWizard
PasteRefreshableWebQuery
PasteSpecialDialog
NamePasteName
PasteInk
PasteFormatting
PasteFormulasAndNumberFormatting
PasteValuesAndNumberFormatting
PasteTextOnly
PasteDestinationFormatting
PasteSourceTheme
PasteDestinationTheme
PasteValuesAndSourceFormatting
PasteWithColumnWidths
PasteSourceFormatting
PasteFormulas
PasteValues
PasteNoBorders
PasteTranspose
PasteLink
PasteAsHyperlink
PasteMergeConditionalFormatting
PasteAsPictureMenu
PasteAsPicture
PastePictureLink
PasteMenu
PasteGalleryMini

 


Comment by: Dave (3/7/2014 3:00:19 PM)

Hi Jan,

Great piece of code thanks so much.

 


Comment by: Christof DB (4/7/2014 4:13:12 PM)

Great work!
I was looking for this kind of functions for some time already to protect some rather complicated (& formatted) excel tool.

I tested the download and it all works like a charm, except that the ribbon commands do not seem to get trapped: there is no call to MyPasteValues2007 when issuing the paste commands from the ribbon.

Any clue? (tested on Excel 2010)

Thanks,
Christof

 


Comment by: Bill Bateman (6/3/2014 5:04:08 PM)

I have a worksheet that is expected to be populated by users copy/pasting rows from a table in a webpage. When they paste this data into a vanilla (no macros) Excel sheet, it nicely populates the cells but brings the font & spacing formatting.

So I added CatchPaste to keep the formatting out. Now nothing gets pasted into the document. I can see that the Application.CutCopyMode = FALSE when this occurs, but I don't understand why.

Any ideas how to fix this?

 


Comment by: Dave Rose (6/26/2014 10:13:38 PM)

Hi,
Thanks for this page - extremely helpful.
I am trying to do something much more limited, but it is proving much harder! I just want to disable cut and paste on the ribbon.
(I'm happy for users to paste-special or other functions, as it's only cut and paste that will mess up my linked formulas, and I've already managed to disable right-click cut and ctrl+x,v.)

I installed the Custom UI, and abbreviated your code to the below - which the validation says is fine.

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
<commands>
<command idMso="Paste" onAction="CPstop"/>
<command idMso="Cut" onAction="CPstop"/>
</commands>
</customUI>

My macro has had various iterations, currently,


Public Sub CPstop()

Dim strInf As String

If Application.CutCopyMode <> False Then
strInf = MsgBox("Normal cut and paste operation has been disabled.")
End If
End Sub


The macro works fine on its own, but when I hit the cut or paste button, I get a 450 error: "Wrong number of arguments or invalid property assignment."
I have no idea how that macro produces that error!
Please help.
Many Thanks!
Dave

 


Comment by: Jan Karel Pieterse (6/27/2014 6:14:09 AM)

Hi Dave,

A callback from the ribbon needs a specific "signature" (amongst others, a set of arguments). You can get them in the custom ui editor by clicking the associated button to view the callbacks. Copy those and paste them into your VBA and call your custom paste code from there.

 


Comment by: Meena (7/14/2014 11:34:18 PM)

Thank you for this solution. It worked great. I searched all over for it!

 


Comment by: Taxis (7/30/2014 3:21:47 PM)

Hi,

It's an awesome piece of code helped me a lot.
But I wonder if its possible to somehow "IsCellValidationOK" could trigger also when I use command bar - paste drop down buttons.
For example when I copy something with ctrC inside the demo workbook the validation warning message triggered, but when I copy something with pasete drop down buttons and its copied to cells with validation then the message not triggered. So is it possible to trigger this message when you use paste drop down buttons?

Thank You for your anwser in advance!

 


Comment by: Jan Karel Pieterse (8/12/2014 10:44:04 AM)

Hi Taxis,

I do think this is possible, but I have no time to find out how to redirect the paste-special dropdown items.

 


Comment by: Donald Domek (4/17/2015 9:56:13 PM)

I am using Excel 2010. I tried both CatchPasteDemo files. It captures ctrl V just fine. But if I right click and paste or paste from the Ribbon, it pastes without capturing. Am I doing something wrong?

 


Comment by: Jan Karel Pieterse (4/21/2015 8:02:05 AM)

Hi Donald,

As far as I know t should work as the article states.

 


Comment by: kadeo (8/14/2015 1:25:43 PM)

I'm trying to adapt this to capture a copy command, the idea being that when something is copied, it forces a custom Ribbon Control to refresh. But I'm not having much luck.

The Ribbon Control needs to be enabled in all the same situations that Paste Values is enabled. (Another custom button but uses IdMSO "PasteValues" so it's enabled state is controlled by excel). So effectively only needs to be active after ranges have been copied.

I'll need to catch the Copy button in the toolbar, right-click context menu and keyboard shortcuts. No validation needs be done after the copy event has been captured except for checking that the Paste Values button has been enabled by excel which is already coded.

I just can't get the 'catch copy event' to work.

 


Comment by: Jan Karel Pieterse (8/17/2015 10:26:13 AM)

Hi kadeo,

You haven't included any code in your comment, which makes it rather difficult to help out?

 


Comment by: Markiewicz (10/13/2015 3:08:42 PM)

Jan,

I am testing your CatchPasteDemo.xlsm file using Excel 2010. I find that it captures as expected when I click on the main Paste button in the Clipboard section of the ribbon. It does not capture when I first click on the arrow attached to the Paste button and then click on one of the other buttons that appear ("Paste(P)","Formulas(F)", etc.). Are other values required for the 'idMso=' statements in the custom UI ribbon xml code?

 


Comment by: Jan Karel Pieterse (10/14/2015 6:25:26 PM)

Hi Markiewicz,

I think you're right, however I didn't have time to find out which those Ids are!

 


Comment by: Narendra (11/9/2016 12:50:20 PM)

If anyone is looking for force paste special, you can try the below code, it captures paste operations and does paste special. Not sure if there are instances where it won't work.

Sub PasteValues()
Application.EnableEvents = False
If Application.CutCopyMode = True Then
Application.SendKeys "%e", True 'Not using CTRL+SHIFT+V (some macros use it for customized operations)
Application.SendKeys "s", True 'Loads pastespecial dialog box
Application.SendKeys "u", True 'Read Trick1 below
Application.SendKeys "v", True 'Read Trick2 below
Application.SendKeys "{ENTER}", True 'Apply paste special
'Trick1: if copy source is external, select "Unicode Text" format (use t for "Text" format)
'Trick2: Changes selection to values, if copy source is excel. If not this keypress changes nothing
End If
Application.EnableEvents = True
End Sub

 


Comment by: Jan Karel Pieterse (11/14/2016 9:16:04 AM)

Hi Narendra,

Thanks. Please note that your code only works on English Excel!

 


Comment by: Mike Knerr (9/28/2017 6:36:51 PM)

First of all, this code is very impressive! However, I am struggling to make it work with my particular use case.

I have a sheet that contains dynamic data validation. When I click on a cell it triggers the Worksheet_SelectionChange event, which kicks off a series of actions that adds validation to the cell based on another list.

This works when using the drop down or typing, but not when copy/pasting. I was directed to this page from this post on StackOverflow: https://stackoverflow.com/questions/46458188/vba-data-validation-does-not-work-when-copy-paste-from-another-cell

For starters, when I open the test file I got an immediate error "Compile error: object does not source automation events". I got rid of all the code relating to the command bar, which seemed to help.

However, once I copied into my workbook, my copy/paste still is not caught by data validation. I copy a value that is not in the validation list, click on the cell with validation (the code runs, validation is enabled) then command-V. The value is added and the data validation list appears to be cleared. When I hit Enter, nothing happens. When I hit Enter again, it takes me to the next cell below but I get no validation alert.

Curious how I can integrate with this effectively.

 


Comment by: Jan Karel Pieterse (9/29/2017 11:08:00 AM)

Hi Mike,

This article was written (and the code tested) with WIndows Excel in mind. Unfortunately I do not own a Mac so I can't possibly say how to do this in Mac Office. I expect you would need to update the OnKey method to intercept the "Command-v" keystroke:

https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_mac/applicationonkey-in-excel-2016-for-mac/c1478637-9ee9-42d9-97f1-341fccec2811?auth=1

 


Comment by: Mike Knerr (9/29/2017 4:18:36 PM)

Thanks Jan, that seems to have captured the event well -- now when I paste into the cell, the validation is still there. But I can still "Enter" away from the cell without triggering the validation. Would I need to add a Worksheet_Change event somewhere to capture this occurrence, and if so would I need to move the any of the paste code above into that event?

 


Comment by: Jan Karel Pieterse (9/29/2017 5:06:55 PM)

Hi Mike,

Yes, you can use the example code "IsCellValidationOK" which I posted above that checks for validation violations immediately after the paste.

 


Comment by: Mike Knerr (9/29/2017 6:46:40 PM)

Thanks, reading through that, that looks like it'll work once it hits that point. The problem is I keep getting assorted errors leading up to it. The one that keeps stopping me is on this line:
Public WithEvents oComBarCtl As Office.CommandBarButton


I get a compile error: "Object does not source automation events"

 


Comment by: Daniel Lockhart (11/24/2017 9:57:26 PM)

Thank you soooo much for this!!! I wish I had found it two weeks ago!!

 


Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.

Please enter your name (required):

Your e-mail address (optional but if you want me to respond it helps!; will not be shown, nor be used to send you unsolicited information):

Your request or comment:

To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].