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

Nogslaw

Well-known Member
Joined
Jul 9, 2004
Messages
904
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.
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
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

Code:
Private Sub Workbook_Activate()

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

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

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MsgBox "Run this macro"
End Sub
 
Upvote 0
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!
 
Upvote 0
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 :

Code:
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 :

Code:
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 !

Code:
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.
 
Upvote 0
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 !

Code:
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?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,224,826
Messages
6,181,192
Members
453,021
Latest member
pingpong7117

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top