Event when Excel window is activated

[hojkoff]

I'm trying to copy data from one program to excel. To save time I want excel to do all the pasting for me.

So, I have one excel workbook open and MS Dynamic NAV open.

I copy the data from NAV and then when I switch to the excel workbook I want it to paste the data in itself.

I've written the code to paste the data in. That works fine, however I can't get this code to fire when I activate or switch to the excel window. I've tried the following but it won't work

Private Sub Workbook_Activate()
    
    Dim pasteBoard As New MSForms.DataObject
    Dim pData As String
    Dim startCell As Range
    Set startCell = Range("D1")
    
    pasteBoard.GetFromClipboard
    pData = pasteBoard.GetText
    
    Do
        Set startCell = startCell.Offset(1, 0)
    Loop Until IsEmpty(startCell)

    startCell.Value = pData

End Sub

And the following as well,

Private Sub Workbook_WindowActivate(ByVal Wn As Window)

    ' 'SAME CODE' '

End Sub

Any thoughts?

[romperstomper]

There is no event for that, I'm afraid. Excel's events only relate to things that happen within the application.

[royUK]

maybe the rezize event

[hojkoff]

But am I not activating the application or workbook or something like that....

Private Sub App_WorkbookActivate(ByVal Wb As Workbook)

Would this work? Don't really know where to put it....

[romperstomper]

No - that will only fire when you switch between workbooks in Excel.