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
