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
