[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Excel 2007: Run a code when workbook activated.

Posted on 2011-03-24
15
Medium Priority
?
358 Views
Last Modified: 2012-05-11
I have some code which should run when I manually focus on excel sheet. This code reads windows clipboard and processes it.

Strangely, Workbook_Activate and Workbook_WindowActivate fire for me only once when workbook is opened. The code in it doesnt involve irrespective of how I focus on it!!

Can someone tell me the workaround and why above events dont work?
0
Comment
Question by:Jignesh Thar
  • 9
  • 5
15 Comments
 
LVL 33

Expert Comment

by:jppinto
ID: 35206534
This two events (Workbook_Activate and Workbook_WindowActivate) should only fire when you open your Workbook. When you want your code to run when you focus on a worksheet, you should use Worksheet_Activate event and place your code there.

jppinto
0
 
LVL 7

Author Comment

by:Jignesh Thar
ID: 35212835
jppinto - Thanks for your response. I want the code to run when workbook is focused (selected from task bar) or selected through "AppActivate" command from outlook VBA.

I'm able to activate workbook using AppActivate but how do I run a macro as soon as workbook is focused?
0
 
LVL 7

Author Comment

by:Jignesh Thar
ID: 35213015
I noticed that Workbook_Activate and Workbook_WindowActivate fire again when I focus on other workbook and return back to workbook that has this code!! Really strange behaviour as this code doesnt fire when I move to other application and activate the workbook again!!
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 19

Expert Comment

by:Arno Koster
ID: 35221941
That's how the events are defined.

when clicking on another workbook, the workbook_activate event of that workbook is fired. vice versa when returning to the original workbook.

when you clidk on another application however, the workbook in itself does not loose focus but only the excel application.
So when you return, excel fires
0
 
LVL 7

Author Comment

by:Jignesh Thar
ID: 35226897
All right. I understand that I cannot use Workbook_Activate and Workbook_WindowActivate events to fire code when excel workbook is focused again.

Is there any other alternative way to triggeer VBA macro when excel workbook is focused every time?
0
 
LVL 19

Expert Comment

by:Arno Koster
ID: 35229942
standard excel application events such as below only handle focus events within the excel application scope

post this code in a class module named "ThisApplication"
Public WithEvents app As Excel.Application
Private Sub app_SheetActivate(ByVal Sh As Object)
    MsgBox "app sheet " & Sh.Name & " activated"
End Sub
Private Sub app_SheetDeactivate(ByVal Sh As Object)
    MsgBox "sheet deactivated"
End Sub
Private Sub app_WindowActivate(ByVal Wb As Workbook, ByVal Wn As Window)
    MsgBox "app window " & Wn.WindowNumber & " activated"
End Sub
Private Sub app_WindowDeactivate(ByVal Wb As Workbook, ByVal Wn As Window)
    MsgBox "app window deactivated"
End Sub
Private Sub app_WorkbookActivate(ByVal Wb As Workbook)
    MsgBox "app workbook " & Wb.Name & " activated"
End Sub
Private Sub app_WorkbookDeactivate(ByVal Wb As Workbook)
    MsgBox "app workbook " & Wb.Name & " deactivated"
End Sub

Open in new window


and execute this code in a regular module
Dim xl As New ThisApplication

Sub test()
    Set xl.app = Excel.Application
End Sub

Open in new window


you could try the approach from

http://www.bigresource.com/VB-Excel-application-focus-event-SOLVED--ScR1UJjrRY.html

it uses the windows API to receive event messages sent from excel. You can then find the appropriate event and act accordingly
0
 
LVL 7

Author Comment

by:Jignesh Thar
ID: 35230864
akoster - Thanks for above code. It appears neat but sheet and app window activate doesnt fire when I move from noteback to excel. This works when I move between workbooks (precisely the behaviour of Workbook_Activate and Workbook_WindowActivate events).

Am I missing something?

In the link you provided, I could locate the code / section which is for excel. Can you please point it out for me?

Appreciate your help so far.
0
 
LVL 19

Accepted Solution

by:
Arno Koster earned 2000 total points
ID: 35233273
insert this code in a regular code module
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
Declare Function SetWindowLong Lib "user32" Alias "SetWindowLongA" (ByVal hwnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long

Dim IsHooked As Boolean
Dim lpPrevWndProc As Long
Dim gHW As Long
Const GWL_WNDPROC = -4

Sub initialise()

gHW = Application.hwnd
Unhook
Hook

End Sub

Public Sub Hook()
    If IsHooked Then
        MsgBox "Don't hook it twice without unhooking, or you will be unable to unhook it."
    Else
        lpPrevWndProc = SetWindowLong(gHW, GWL_WNDPROC, AddressOf WindowProc)
        IsHooked = True
    End If
End Sub

Public Sub Unhook()
    Dim temp As Long
    temp = SetWindowLong(gHW, GWL_WNDPROC, lpPrevWndProc)
    IsHooked = False
End Sub

Function WindowProc(ByVal hw As Long, ByVal uMsg As Long, ByVal wParam As Long, ByVal lParam As Long) As Long
    
    Select Case uMsg
    'Case 32, 132, 533
        '-- mouse movements
    'Case 124, 125, 528
        '-- mouse click
    'Case 15, 20, 133
        '-- drag & drop
    'Case 12, 13
        '-- change worksheet
    Case 134
        '-- loose focus
        If wParam = 0 Then Debug.Print "Excel lost focus"
        If wParam = 1 Then Debug.Print "Excel got focus"
    Case Else
        Debug.Print "Message: "; hw, uMsg, wParam, lParam
    End Select

    WindowProc = CallWindowProc(lpPrevWndProc, hw, uMsg, wParam, lParam)
End Function

Open in new window


feel free to experiment with additional messages !

please note though that the code may mess up the name of the application and window placement, so I advise you to populate your screen with excel in the top left quarter, the vba editor in the top right quarter and another application on the lower left quarter of your screen. If you make sure that the application windows do not overlap each other, you will be able to see the messages coming in.

When you de-comment the case [xxx] lines, the number of messages will dramatically decrease to a level that you can actually see what's going on.
0
 
LVL 7

Author Comment

by:Jignesh Thar
ID: 35251722
Thanks akoster. Seems like this is certainly in direction I want the solution.

I tried below.
1. I replaced Debug.Print with MsgBox for lines having "Excel lost focus"  / "Excel got focus" and commented "Debug.Print "Message: "; hw, uMsg, wParam, lParam" in "Case Else".
2. Ran "initialise" once

I get "Excel lost focus"  continuously and the code goes in infinite loop.

0
 
LVL 19

Expert Comment

by:Arno Koster
ID: 35255489
Small wonder how that's happening ;-)

have you considered that opening a messagebox makes excel loose focus ?

0
 
LVL 7

Author Comment

by:Jignesh Thar
ID: 35260478
Hmm. I understand :-) So opening msgbox makes lose focus and there is another messagebox and so one when it loses focus.

Do you see any other alternative for me need? What I needed is for the code to fire once when excel is focused from windows task bar?
0
 
LVL 7

Author Comment

by:Jignesh Thar
ID: 35260483
Thanks for your persistence so far on this.
0
 
LVL 19

Expert Comment

by:Arno Koster
ID: 35261613
the options you have to notify depend on precisely what you want to achieve.

for testing purposes you might benefit to

- take a cell inside the excel sheet and increase its value each time of losing focus
- take a cell inside the excel sheet and color it red for lost focus and green for has focus
- use the vba editor with the debug.print statement to output the time and getting or losing focus
- output to a notepad window / msword / etc instead of the vba editor
- write the output to a text file
- use a sound (as in wave file / mp3) or beep to notify

this is all done pretty easily

when you want to fire an event when the focus is transferred from the windows task bar, and only from the taskbar (not from other programs) you might need to process taskbar messages as well. This means intensive use of API functions, if it can be done at all.
0
 
LVL 7

Author Comment

by:Jignesh Thar
ID: 35273185
Thanks akoster.

I need to run code when excel is either manually focused from task bar or from other program. So I need to run code when excel is refocused irrespective of how it gets focused

I have finally resorted to use of Application.OnKey "`", "Personal.xlsb!SearchByKeywordAcrossColumnsAndHideRows" to invoke macro with single click (as there doesnt seem to be easy way to involve as soon as excel is focused from other program or windows task bar)

Appreciate all your help so far
0
 
LVL 7

Author Closing Comment

by:Jignesh Thar
ID: 35273222
Used other alternative
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question