I am using the code described in this post to try to capture paste events and force them to Paste Special to preserve my validation. As part of that code, there is a class module:
'-------------------------------------------------------------------------
' Module : clsCommandBarCatch
' 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 '<- Error highlights this line
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
However, I can't get the code to run because I keep getting the compile error
Object does not source automation events
highlighting the line indicated above.
I have checked my references, in the "Add References" window of my VBA editor (I am on Excel for Mac 2016 btw) and found the following libraries checked:
- Visual Basic for Applications
- Microsoft Excel 14.0 Objects
- Microsoft Office 14.0 Objects
- Microsoft Forms 2.0 Objects
I also checked "OLE Automation" just in case. There are some other libraries not checked ("VBAProject", "EBApp Object Library", libraries for other Office apps) that don't seem to apply here so I left them unchecked.
I also tested this on Excel for Mac 2011 and got the same error. Is it possible newer versions of Excel don't use the CommandBar object b/c of the Ribbon?
Any thoughts on how to clear this error are appreciated.