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