Ah! now you are talking. Aju, unfortunately there is no application level even that triggers when the application is active. The application class events are associated to the workbook events and below. And I am not sure if an API can be used to do this. the other suggestion I have is to use a scheduled event for every second that will run in the background and run when the app is active, but that could be flawed.
Regards, Sam On 9/26/11, Paul Schreiner <schreiner_p...@att.net> wrote: > It looks like you are correct! > the Workbook_Activate event only triggers when switching between Excel > workbooks. > > It looks like there should be an Application.WindowActivate event, but I > haven't > been able to get it to work in the small test case I tried. > > I also found an event that requires a class module, but have not looked at > it > further. > > You COULD create a SelectionChange event, that would trigger if a cell is > selected, but that would > require you to select the Excel application, AND select a cell. (any cell > would > do) > > I'll try to look further. > > Paul > ----------------------------------------- > “Do all the good you can, > By all the means you can, > In all the ways you can, > In all the places you can, > At all the times you can, > To all the people you can, > As long as ever you can.” - John Wesley > ----------------------------------------- > > > > > ________________________________ > From: aju chacko <ajuvcha...@gmail.com> > To: excel-macros@googlegroups.com > Sent: Mon, September 26, 2011 1:10:44 PM > Subject: Re: $$Excel-Macros$$ Trap window activate event from personal macro > workbook > > Dear sam/paul > Isempty(activecell.value) is OK now.But the macro > doesn't > work as i expected,It triggers only once.Actually what i want is whenever I > switch from SAP window to EXCEL window(Focus is set to EXCEL),the macro > should > execute. As i have understood,the workbook_activate event triggers only when > we > are switching between excel windows.Kindly help > > Aju v chacko > > > On Mon, Sep 26, 2011 at 9:24 PM, Sam Mathai Chacko <samde...@gmail.com> > wrote: > > It shouldn't be throwing an error, unless there is no workbook at all. There > are > lots of ways to check whether a cell is empty. Try this. >> >>If Len(ActiveCell.Value)=0 Then >> >>OR >> >>If ActiveCell.Value="" Then >> >>Paul, the event that Aju is after, is for an add-in to identify whenever a >>workbook with the mentioned sheet name is activated at an application >> level. The >>event which you mentioned is at the Workbook level (associated with the >> file >>that it is written in). Aju requires the event at an application level. Let >> me >>know if you need further clarification. >> >>Regards >> >>Sam Mathai Chacko >> >> >> >>On Mon, Sep 26, 2011 at 4:42 PM, Paul Schreiner <schreiner_p...@att.net> >> wrote: >> >>Forgive me if this is totally off-base. >>>But I have never used the Activate event in a Class module. >>> >>>In the ThisWorkbook module, I would use: >>> >>>Private Sub Workbook_Activate() >>> startappmon >>>End Sub >>> >>>I use this technique in several applications where I activate and >>> deactivate >>>toolbars depending on which file is open. >>> >>>hope this is helpful. >>> >>>Paul >>>----------------------------------------- >>>“Do all the good you can, >>>By all the means you can, >>>In all the ways you can, >>>In all the places you can, >>>At all the times you can, >>>To all the people you can, >>>As long as ever you can.” - John Wesley >>>----------------------------------------- >>> >>> >>> >>> >>> > ________________________________ > From: aju chacko <ajuvcha...@gmail.com> >>>To: excel-macros@googlegroups.com >>>Sent: Mon, September 26, 2011 5:53:07 AM >>>Subject: Re: $$Excel-Macros$$ Trap window activate event from personal >>> macro >>>workbook >>> >>> >>>dear sam, >>> Thanks for your kindness.I have imported all the >>>module as u mentioned,But the following line in class module is >>>throwing an error(syntax error) >>> ' If Not IsEmpty(ActiveCell) Then' >>>But i didn't see anything wrong with this line.By the way i am using >>>excel 2007 and my purpose is to shuttle data between 'Excel' and 'SAP' >>>applications.My purpose is to copy data from the cell once I select >>>Excel window , Now i am running this macro by selecting excel window & >>>then pressss the short cut key assigned to the macro for copying the >>>data every time.Kindly help me.Thanking you in advance >>> >>> aju v chacko >>> >>> >>>On 9/26/11, Sam Mathai Chacko <samde...@gmail.com> wrote: >>>> This is how you do it. >>>> >>>> Code below >>>> 'In Module >>>> Option Explicit >>>> >>>> Dim objApp As New Class1 >>>> Sub Auto_Open() >>>> >>>> Set objApp.app = Application >>>> >>>> End Sub >>>> >>>> Sub Auto_Close() >>>> >>>> Set objApp = Nothing >>>> >>>> End Sub >>>> Sub startappmon() >>>> >>>> If ActiveSheet.Name = "sap vs phy" Then >>>> If ActiveCell.Column = 1 Then >>>> Selection.End(xlToRight).Select >>>> Selection.Copy >>>> Application.WindowState = xlMinimized >>>> 'doappmon >>>> Else >>>> Range(Selection, Selection.End(xlToLeft)).Select >>>> With Selection.Interior >>>> .Color = 5287936 >>>> End With >>>> ActiveCell.End(xlToLeft).Offset(1, 0).Select >>>> If ActiveCell.Value Is Not Empty Then >>>> Selection.Copy >>>> Application.WindowState = xlMinimized >>>> 'doappmon >>>> Else >>>> Exit Sub >>>> End If >>>> End If >>>> End If >>>> >>>> End Sub >>>> >>>> 'In Class (name of class here is Class1) >>>> Option Explicit >>>> >>>> Public WithEvents app As Application >>>> Private Sub app_WindowActivate(ByVal Wb As Workbook, ByVal Wn As Window) >>>> If ActiveSheet.Name = "sap vs phy" Then >>>> If ActiveCell.Column = 1 Then >>>> Selection.End(xlToRight).Select >>>> Selection.Copy >>>> Application.WindowState = xlMinimized >>>> 'doappmon >>>> Else >>>> Range(Selection, Selection.End(xlToLeft)).Select >>>> With Selection.Interior >>>> .Color = 5287936 >>>> End With >>>> ActiveCell.End(xlToLeft).Offset(1, 0).Select >>>> If Not IsEmpty(ActiveCell) Then >>>> Selection.Copy >>>> Application.WindowState = xlMinimized >>>> 'doappmon >>>> Else >>>> Exit Sub >>>> End If >>>> End If >>>> End If >>>> End Sub >>>> >>>> >>>> Regards, >>>> >>>> Sam >>>> >>>> On Mon, Sep 26, 2011 at 10:33 AM, aju chacko <ajuvcha...@gmail.com> >>>> wrote: >>>> >>>>> Dear friends, >>>>> I have created the following code to to trap >>>>> "windowactivate" event and execute a code from personal macro >>>>> workbook when the activate sheet name is "sap vs phy".But it works >>>>> only when the excel is opened for first time.Kindly check the code & >>>>> give necessary correction so that once maco is executed & whenever >>>>> excel window is activated& w.shhet name is "sap vs phy" then the >>>>> following code is executed >>>>> >>>>> If ActiveSheet.Name = "sap vs phy" Then >>>>> If ActiveCell.Column = 1 Then >>>>> Selection.End(xlToRight).Select >>>>> Selection.Copy >>>>> Application.WindowState = xlMinimized >>>>> 'doappmon >>>>> Else >>>>> Range(Selection, Selection.End(xlToLeft)).Select >>>>> With Selection.Interior >>>>> .Color = 5287936 >>>>> End With >>>>> ActiveCell.End(xlToLeft).Offset(1, 0).Select >>>>> If ActiveCell.Value Is Not Empty Then >>>>> Selection.Copy >>>>> Application.WindowState = xlMinimized >>>>> 'doappmon >>>>> Else >>>>> Exit Sub >>>>> End If >>>>> End If >>>>> End If >>>>> >>>>> The sequence in which i made the modules are as follows >>>>> >>>>>................................................................................. >>>>>. >>>>> '***macro in personal macro workbook >>>>> Sub exceltosap() >>>>> ' >>>>> ' Macro1 Macro >>>>> ' >>>>> startappmon >>>>> >>>>> End Sub >>>>> >>>>>................................................................................... >>>>>. >>>>> '***macro in pesonal macro workbook >>>>> Dim x As New Class11 >>>>> Sub startappmon() >>>>> Set x.app = Application >>>>> End Sub >>>>> >>>>> >>>.............................................................................. >>>>> '***class module in personal macro workbook >>>>> Public WithEvents app As Application >>>>> Private Sub app_WindowActivate(ByVal Wb As Workbook, ByVal Wn As >>>>> Window) >>>>> If ActiveSheet.Name = "sap vs phy" Then >>>>> If ActiveCell.Column = 1 Then >>>>> Selection.End(xlToRight).Select >>>>> Selection.Copy >>>>> Application.WindowState = xlMinimized >>>>> 'doappmon >>>>> Else >>>>> Range(Selection, Selection.End(xlToLeft)).Select >>>>> With Selection.Interior >>>>> .Color = 5287936 >>>>> End With >>>>> ActiveCell.End(xlToLeft).Offset(1, 0).Select >>>>> If ActiveCell.Value Is Not Empty Then >>>>> Selection.Copy >>>>> Application.WindowState = xlMinimized >>>>> 'doappmon >>>>> Else >>>>> Exit Sub >>>>> End If >>>>> End If >>>>> End If >>>>> End Sub >>>>> >>>>> -- >>>>> >>>>>---------------------------------------------------------------------------------- >>>>>- >>>>> Some important links for excel users: >>>>> 1. Follow us on TWITTER for tips tricks and links : >>>>> http://twitter.com/exceldailytip >>>>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 >>>>> 3. Excel tutorials at http://www.excel-macros.blogspot.com >>>>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com >>>>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com >>>>> >>>>> To post to this group, send email to excel-macros@googlegroups.com >>>>> >>>>> <><><><><><><><><><><><><><><><><><><><><><> >>>>> Like our page on facebook , Just follow below link >>>>> http://www.facebook.com/discussexcel >>>>> >>>> >>>> >>>> >>>> -- >>>> Sam Mathai Chacko >>>> >>>> -- >>>>---------------------------------------------------------------------------------- >>>>- >>>> Some important links for excel users: >>>> 1. Follow us on TWITTER for tips tricks and links : >>>> http://twitter.com/exceldailytip >>>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 >>>> 3. Excel tutorials at http://www.excel-macros.blogspot.com >>>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com >>>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com >>>> >>>> To post to this group, send email to excel-macros@googlegroups.com >>>> >>>> <><><><><><><><><><><><><><><><><><><><><><> >>>> Like our page on facebook , Just follow below link >>>> http://www.facebook.com/discussexcel >>>> >>> >>>-- >>>---------------------------------------------------------------------------------- >>> >>>Some important links for excel users: >>>1. Follow us on TWITTER for tips tricks and links : >>>http://twitter.com/exceldailytip >>>2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 >>>3. Excel tutorials at http://www.excel-macros.blogspot.com >>>4. Learn VBA Macros at http://www.quickvba.blogspot.com >>>5. Excel Tips and Tricks at http://exceldailytip.blogspot.com >>> >>>To post to this group, send email to excel-macros@googlegroups.com >>> >>><><><><><><><><><><><><><><><><><><><><><><> >>>Like our page on facebook , Just follow below link >>>http://www.facebook.com/discussexcel >>>-- >>> >>>---------------------------------------------------------------------------------- >>> >>>Some important links for excel users: >>>1. Follow us on TWITTER for tips tricks and links : >>>http://twitter.com/exceldailytip >>>2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 >>>3. Excel tutorials at http://www.excel-macros.blogspot.com >>>4. Learn VBA Macros at http://www.quickvba.blogspot.com >>>5. Excel Tips and Tricks at http://exceldailytip.blogspot.com >>> >>>To post to this group, send email to excel-macros@googlegroups.com >>> >>><><><><><><><><><><><><><><><><><><><><><><> >>>Like our page on facebook , Just follow below link >>>http://www.facebook.com/discussexcel >>> >> >> >>-- >> >>Sam Mathai Chacko >>-- >>---------------------------------------------------------------------------------- >> >>Some important links for excel users: >>1. Follow us on TWITTER for tips tricks and links : >>http://twitter.com/exceldailytip >>2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 >>3. Excel tutorials at http://www.excel-macros.blogspot.com >>4. Learn VBA Macros at http://www.quickvba.blogspot.com >>5. Excel Tips and Tricks at http://exceldailytip.blogspot.com >> >>To post to this group, send email to excel-macros@googlegroups.com >> >><><><><><><><><><><><><><><><><><><><><><><> >>Like our page on facebook , Just follow below link >>http://www.facebook.com/discussexcel >> > -- > ---------------------------------------------------------------------------------- > > Some important links for excel users: > 1. Follow us on TWITTER for tips tricks and links : > http://twitter.com/exceldailytip > 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 > 3. Excel tutorials at http://www.excel-macros.blogspot.com > 4. Learn VBA Macros at http://www.quickvba.blogspot.com > 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com > > To post to this group, send email to excel-macros@googlegroups.com > > <><><><><><><><><><><><><><><><><><><><><><> > Like our page on facebook , Just follow below link > http://www.facebook.com/discussexcel > > -- > ---------------------------------------------------------------------------------- > Some important links for excel users: > 1. Follow us on TWITTER for tips tricks and links : > http://twitter.com/exceldailytip > 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 > 3. Excel tutorials at http://www.excel-macros.blogspot.com > 4. Learn VBA Macros at http://www.quickvba.blogspot.com > 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com > > To post to this group, send email to excel-macros@googlegroups.com > > <><><><><><><><><><><><><><><><><><><><><><> > Like our page on facebook , Just follow below link > http://www.facebook.com/discussexcel > -- Sam Mathai Chacko -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
