[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Excel 2007: Run a code when workbook activated.

Posted on 2011-03-24
15
Medium Priority
?
346 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
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‚Ķ

656 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