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
