How to trigger a macro as soon as Excel Window is Selected

[Nogslaw]

I want to trigger a macro to run as soon as the excel window is selected.

Say for example that the user had an Excel file, a Word document and an Outlook session open. The user is currently working on the Word document. The user then clicks on the Excel file - I want the macro to run immediately. How can I do that?

Ultimately, I am copying data from an AS400 program to an Excel sheet by I cannot access the actual database files, so I have to use highlight/copy/paste. I am trying to automate the paste so that when I click on the Excel program, it will find the bottom row and paste the data without me having to click on a cell and hitting a paste function.

 

[dave3009]

Most of the code to do that is easily obtainable but to fire a macro when the workbook is activated take a look at the Workbook_Activate event and see if it help

Private Sub Workbook_Activate()

'.....Some Code to do paste n' that

End Sub

 

[Jeffrey Smith]

Try this. Paste the code into the sheet you want to work (Sheet1). When you click on a new cell, it will run.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MsgBox "Run this macro"
End Sub

 

[Nogslaw]

Thanks guys. Those were the first events I looked at too, but they don't quite do the trick exactly as I had hoped.

The closest one to what I was hoping for is the Sheet Selection Change that Jeffrey suggested. It only requires a click of the mouse to activate it. Not a perfect fit to what I wanted, but it may be the best that can happen.

If anyone else has any ideas, please respond!

 

[lenze]

You can do this with an Application Event
See Chip Pearson's article Application Events for some examples.

lenze

 

[Jaafar Tribak]

The application level events in the Chip Pearson's article only work for the workbook windows. If you need to trigger a macro when the main excel window is activated then i am afraid you will need to resort to subclassing excel or setting a global Windows hook both of which are rather involved techniques.

Here is a WORKBOOK EXAMPLE that i posted only yesterday and that i have amended to meet your special requirements.

Functionality acquired : Whatever text is currently in the clipboard will be automatically pasted to the bottom of your list in Column A upon activating the excel application.

Main code in a Standard Module :

Option Explicit
 
Private Declare Function SetWindowLong Lib "user32.dll" _
Alias "SetWindowLongA" _
(ByVal hwnd As Long, _
ByVal nIndex As Long, _
ByVal dwNewLong As Long) As Long
 
Public Declare Function CallWindowProc Lib "user32" _
Alias "CallWindowProcA" _
(ByVal lpPrevWndFunc As Long, _
ByVal hwnd As Long, _
ByVal MSG As Long, _
ByVal wParam As Long, _
ByVal lParam As Long) As Long
 
Private Declare Function FindWindow Lib "user32.dll" _
Alias "FindWindowA" _
(ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long
 
Private Declare Function GetDesktopWindow Lib _
"user32.dll" () As Long
 
Private Declare Function ShowWindow Lib "user32.dll" _
(ByVal hwnd As Long, _
ByVal nCmdShow As Long) As Long
 
Private Declare Function SendMessage Lib "user32.dll" _
Alias "SendMessageA" _
(ByVal hwnd As Long, _
ByVal wMsg As Long, _
ByVal wParam As Long, _
ByRef lParam As Any) As Long
 
Private Declare Function PostMessage Lib "user32.dll" _
Alias "PostMessageA" _
(ByVal hwnd As Long, _
ByVal wMsg As Long, _
ByVal wParam As Long, ByVal lParam As Long) As Long
 
Private Declare Function SetTimer Lib "user32.dll" _
(ByVal hwnd As Long, _
ByVal nIDEvent As Long, _
ByVal uElapse As Long, _
ByVal lpTimerFunc As Long) As Long
 
Private Declare Function KillTimer Lib "user32.dll" _
(ByVal hwnd As Long, _
ByVal nIDEvent As Long) As Long
 
Private Declare Function LockWindowUpdate Lib "user32.dll" _
(ByVal hwndLock As Long) As Long
 
Private Declare Function GetProp Lib "user32" _
Alias "GetPropA" _
(ByVal hwnd As Long, _
ByVal lpString As String) As Long
 
Private Declare Function SetProp Lib "user32" _
Alias "SetPropA" _
(ByVal hwnd As Long, _
ByVal lpString As String, _
ByVal hData As Long) As Long
 
Private Declare Function RemoveProp Lib "user32" _
Alias "RemovePropA" _
(ByVal hwnd As Long, _
ByVal lpString As String) As Long
 
Private Const GWL_WNDPROC   As Long = -4
Private Const WM_USER As Long = &H400

Public Const WM_ACTIVATEAPP As Long = &H1C
Private Const WM_SETREDRAW As Long = &HB
 
Private Const VBE_CLASS_NAME As String _
= "wndclass_desked_gsk"
Private Const EXCEL_CLASS_NAME As String _
= "XLMAIN"
 
Public lOldWinProc As Long
Private lVBEhwnd As Long

Sub Safe_Subclass(hwnd As Long)

    'don't subclass the window twice !
    If GetProp(GetDesktopWindow, "HWND") <> 0 Then
        MsgBox "The Window is already Subclassed.", _
        vbInformation
        Exit Sub
    End If
    
    'store the target window hwnd as a desktop
    'window for later use property.
    
     SetProp GetDesktopWindow, "HWND", hwnd
    
    'retrieve the VBE hwnd.
    
     lVBEhwnd = FindWindow(VBE_CLASS_NAME, vbNullString)
    
    'prevent flickering of the screen
    'before posting messages to reset
    'the VBE window.
    
    LockWindowUpdate lVBEhwnd
    
    'do the same with the desktop in the background.
    SendMessage _
    GetDesktopWindow, ByVal WM_SETREDRAW, ByVal 0&, 0&
    
    'stop and reset the VBE first to safely
    'proceed with our subclassing of xl.
    
    PostMessage _
    lVBEhwnd, ByVal WM_USER + &HC44, ByVal &H30, ByVal 0&
    
    PostMessage _
    lVBEhwnd, ByVal WM_USER + &HC44, ByVal &H33, ByVal 0&
    
    PostMessage _
    lVBEhwnd, ByVal WM_USER + &HC44, ByVal &H83, ByVal 0&
    
    'run a one time timer and subclass xl
    'from the timer callback function.
    'if subclassing is not installed within
    'the timer callback,xl will crash !
    
    SetTimer GetProp(GetDesktopWindow, "HWND") _
    , 0&, 1, AddressOf TimerProc
 
End Sub
 
Sub UnSubClassWindow(hwnd As Long)
 
    'remove the subclass and cleanup.
    SetWindowLong hwnd, GWL_WNDPROC, lOldWinProc
    RemoveProp GetDesktopWindow, "HWND"
    lOldWinProc = 0
    
End Sub
 
Sub TimerProc(ByVal hwnd As Long, ByVal nIDEvent As Long, _
ByVal uElapse As Long, ByVal lpTimerFunc As Long)
 
    'we lost the hwnd stored in the lVBEhwnd var
    'after reseting the VBE so let's retrieve it again.
    
    lVBEhwnd = FindWindow(VBE_CLASS_NAME, vbNullString)
    
    'we no longer need the timer.
    KillTimer GetProp(GetDesktopWindow, "HWND"), 0&
    
    'allow back drawing on the desktop.
    SendMessage GetDesktopWindow, WM_SETREDRAW, ByVal 1, 0&
    
    'hide the VBE.
    
    ShowWindow lVBEhwnd, 0&
    
    'unlock the window update.
    
    LockWindowUpdate 0&
    
    'and at last we can now safely
    'subclass our target window.
    
    lOldWinProc = SetWindowLong _
    (GetProp(GetDesktopWindow, "HWND"), _
    GWL_WNDPROC, AddressOf WindowProc)
End Sub

Caller code in a another Standard module :

Option Explicit
    
Sub SetSubClass()
 
    'let's subclass the main
    'excel application window.
    
    Call Safe_Subclass(Application.hwnd)
 
End Sub
 
Sub RemoveSubClass()
 
    'unsubclass the XL window.
    
    Call UnSubClassWindow(Application.hwnd)
 
End Sub
 
Public Function WindowProc _
(ByVal hwnd As Long, ByVal uMsg As Long, _
ByVal wParam As Long, ByVal lParam As Long) As Long
    
    On Error Resume Next
    
    Select Case uMsg
    
        'Run your Paste Macro if
        'the app window is being
        'activated.
        Case WM_ACTIVATEAPP
        
            If wParam Then
                Call MyPasteMacro
            End If
    
    End Select
    
    'allow other msgs default processing.
    WindowProc = CallWindowProc _
    (lOldWinProc, hwnd, uMsg, wParam, lParam)
 
End Function
 
Private Sub MyPasteMacro()
 
    On Error Resume Next
    
    With ActiveSheet
        .Cells(Rows.Count, 1).End(xlUp).Offset(1).Select
        .Paste
    End With
 
End Sub

and finally, place this in the ThisWorkbook module. -- this is very important in case the user attempts to close the workbook/application before unhooking excel !

Option Explicit
 
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    
    'IMPORTANT !!!
    '============
    
    'if the subclassing is not removed
    'before closing the workbook
    'the system can crash !!!!
    
    Call UnSubClassWindow(Application.hwnd)
 
End Sub

Regards.

 

[Nogslaw]

Lenze and Jaafar - wow. Thanks for the suggestions. Both of these techniques are well beyond my knowledge of excel and vb, but good to see that these types of things can be accomplished. I'll work with them and see if I can get them to work.

Jaafar - one question:

and finally, place this in the ThisWorkbook module. -- this is very important in case the user attempts to close the workbook/application before unhooking excel !

Option Explicit
 
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    
    'IMPORTANT !!!
    '============
    
    'if the subclassing is not removed
    'before closing the workbook
    'the system can crash !!!!
    
    Call UnSubClassWindow(Application.hwnd)
 
End Sub

Regards.

What would happen if Excel was closed by non-traditional means? Say if Excel crashed, or the user used the Task Manager to End Task or something, and the BeforeClose Event could not run? Would this have the same potential to crash the system, or cause problems on re-open?

 

[Jaafar Tribak]

Jaafar - one question:



What would happen if Excel was closed by non-traditional means? Say if Excel crashed, or the user used the Task Manager to End Task or something, and the BeforeClose Event could not run? Would this have the same potential to crash the system, or cause problems on re-open?

Hi Nogslaw,

Well i guess the only problem is loosing unsaved data including that of other workbooks that you may have open in the current excel session which is bad enough. So this is to be used with caution.

Regards.