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
sap vs phy.xls
Description: MS-Excel spreadsheet
PERSONAL.XLS
Description: MS-Excel spreadsheet