Class Event CutCopyMode help

[winxpdows]

Hi All,

Looking for help from the experts. I am unable to copy/cut/paste between workbooks. I'm fairly certain it is because of a Class event I have to check if the workbook has been changed, but can't figure out a workaround.

I am building an addin that makes a custom toolbar of icons for running different procedures. I only want certain buttons available on certain types of worksheets (that contain specific data in the header) that the users have as templates. To do so, I found online postings leading me to create a Class Event

Public WithEvents App as Application

Private Sub App_WorkbookActivate(ByVal Wb as Workbook)
   call statuscheck
End Sub

where statuscheck is my procedure that looks in the header for the correct info and enables/disables the correct buttons

in my workbook_open I call a routine named InitiateWorkbookChangeEvent..

dim myobject as New CExcelEvents

Sub InitiateWorkbookChangeEvent()
   Set myobject.App = Application
End Sub

It works great for enabling/disabling the buttons, but if I need the users to still be able to copy and paste between different workbooks. Copy and pasting within the same workbook works fine. But if you copy something, as soon as you click on another workbook, the flashing outline of the selection you want to copy in the other workbook disappears.

If there is a different way for me to catch when a user changes workbooks so that I enable the correct buttons, I'd appreciate that help as well.

Thank you in advance!

 

[winxpdows]

Oh.. And I have tried grabbing the cutcopymode before the call statuscheck to try and enable it again after the statuscheck. But by then it already says it's 0.

Any help is greatly appreciated!

 

[Jaafar Tribak]

This nasty clearing of the clipboard was discussed HERE
Take a look at Post #9

 

[winxpdows]

Thanks Jaafar for directing me there.. Unfortunately, I still can't get it working for my add-in...

I've tried adding the code you posted there into my Class Module and I get a compile error...

Public WithEvents App as Application

Private Sub App_WorkbookActivate(ByVal Wb as Workbook)
    call xlCalcMan  
    call statuscheck
End Sub

I tried adding it to my ThisWorkbook module but it's not working that way either... and I've tried putting the xlCalcMan/Auto procedures into regular modules as public routines... No luck...

any other ideas would be GREATLY appreciated. Thanks in advance!

 

[Jaafar Tribak]

Try amending your Class module code as follows and see if it works:

[B][COLOR=Magenta]Private Declare Function OpenClipboard Lib "User32" _
(ByVal hwnd As Long) As Long[/COLOR][/B]
 
[B][COLOR=Magenta]Private Declare Function CloseClipboard Lib "User32" () As Long
[/COLOR][/B]
Public WithEvents App as Application

Private Sub App_WorkbookActivate(ByVal Wb As Workbook)

   [B][COLOR=Magenta]OpenClipboard 0[/COLOR][/B]
   Call statuscheck
   [B][COLOR=Magenta]CloseClipboard[/COLOR][/B]
   
End Sub