Solved

Excel 2007: Run a code when workbook activated.

Posted on 2011-03-24
15
309 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
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.

 
LVL 19

Expert Comment

by:akoster
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:akoster
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:
akoster earned 500 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:akoster
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:akoster
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: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

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

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

860 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