• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 394
  • Last Modified:

Excel 2007: Run a code when workbook activated.

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
Jignesh Thar
Asked:
Jignesh Thar
  • 9
  • 5
1 Solution
 
jppintoCommented:
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
 
Jignesh TharSenior ManagerAuthor Commented:
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
 
Jignesh TharSenior ManagerAuthor Commented:
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Arno KosterCommented:
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
 
Jignesh TharSenior ManagerAuthor Commented:
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
 
Arno KosterCommented:
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
 
Jignesh TharSenior ManagerAuthor Commented:
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
 
Arno KosterCommented:
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
 
Jignesh TharSenior ManagerAuthor Commented:
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
 
Arno KosterCommented:
Small wonder how that's happening ;-)

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

0
 
Jignesh TharSenior ManagerAuthor Commented:
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
 
Jignesh TharSenior ManagerAuthor Commented:
Thanks for your persistence so far on this.
0
 
Arno KosterCommented:
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
 
Jignesh TharSenior ManagerAuthor Commented:
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
 
Jignesh TharSenior ManagerAuthor Commented:
Used other alternative
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 9
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now