dear sam/paul,
Thanks for your guidance
regards
aju
On 9/27/11, Sam Mathai Chacko <samde...@gmail.com> wrote:
> 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
>
--
----------------------------------------------------------------------------------
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
