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
