Solved

Excel 2007: Run a code when workbook activated.

Posted on 2011-03-24
15
298 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 19

Expert Comment

by:akoster
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 19

Accepted Solution

by:
akoster earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks for your persistence so far on this.
0
 
LVL 19

Expert Comment

by:akoster
Comment Utility
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
Comment Utility
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
Comment Utility
Used other alternative
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

763 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now