Re: $$Excel-Macros$$ Retrieving data in horizontal form
And take out the Grand Totals for rows and columns :) On Mon, Sep 26, 2011 at 10:37 AM, Amit Gandhi silkyro...@gmail.com wrote: thanks dear On Sat, Sep 24, 2011 at 11:22 PM, ChilExcel chilexcel...@gmail.comwrote: Please see attachment 2011/9/24 ashish koul koul.ash...@gmail.com try this cell f5 = SUMPRODUCT(($A$4:$A$150=F$3)*($B$4:$B$150=$E5)*($C$4:$C$150)) and drag the formula right On Sat, Sep 24, 2011 at 5:31 PM, Amit Gandhi silkyro...@gmail.comwrote: Hi Folks I am finding some problem in excel data. I have a excel data (attached file) in vertical form. I want to convert it into Horizontal form using Excel Formulas. Pls help me here. Regards Amit -- -- 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 -- *Regards* * * *Ashish Koul* *http://www.excelvbamacros.com/* P Before printing, think about the environment. -- -- 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 -- Visita ; http://sites.google.com/site/chilexcel/Home Visita ; http://www.youtube.com/user/timextag41 -- -- 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 -- 'Expecting the world to treat u fairly coz u r a good person is like expecting the lion not to attack u coz u r a vegetarian. Think about it.' Take care Amit -- -- 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
Re: $$Excel-Macros$$ vlookup for two same value in acceding order with two Criteria
Dear Vicky, See attached sheet if it help to u.. Thanks regards, Noorain Ansari *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ *http://noorain-ansari.blogspot.com/* http://noorain-ansari.blogspot.com/ On Sun, Sep 25, 2011 at 4:03 PM, vickey vikasda...@gmail.com wrote: can I get values (time) of two same values in accedning order with two Criteria. for example I have employee code / date time now employee code repeates two or more times in a day with diffrent times. now what I want if I select parcticular date with particular empoyee ID then time of that particuar date or day should reflect in acceding order in single cell. EMPCodeDateTime 525/09/11 09:15:30 525/09/11 12:30:00 525/09/11 18:30:45 taking above table as an example I want result in following format. EmpCode - 5 Date - 25/09/11 25 (09:15:30 | 18:30:45) means start time and end time for the day or date should reflect. the idea behind is to creat a caleder for attendence with time for employee, on the basis of data which is imported from biomatix system. kindly help me out vikas daiya -- -- 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 Solution-Vicky.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Re: $$Excel-Macros$$ Re: Retrieving data in horizontal form
Nicely done by Noorain. Shouldn't the solution provided by ChilExcel suffice? Pivot tables as it is dynamic in nature, you just need to pass the source range. Regards, Sam Mathai Chacko (GL) On Mon, Sep 26, 2011 at 11:40 AM, NOORAIN ANSARI noorain.ans...@gmail.comwrote: Dear Amit, Please see attached sheet.. and use * =INDEX($B$4:$B$150,SMALL(IF($D$4:$D$150=1,ROW($B$4:$B$150),),ROW(A3))-3) =Offset($B$4,SMALL(IF($D$4:$D$150=1,ROW($B$4:$B$150),),ROW(A3))-3,0)* -- Thanks regards, Noorain Ansari *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ *http://noorain-ansari.blogspot.com/*http://noorain-ansari.blogspot.com/ On Mon, Sep 26, 2011 at 10:33 AM, Amit Gandhi silkyro...@gmail.comwrote: Thanks alot Noorain It works fine. But I also want to automate Column E i.e. it automatically retrieve Co Name as well. If you can help me here as well. Regards Amit On Sat, Sep 24, 2011 at 8:54 PM, NOORAIN ANSARI noorain.ans...@gmail.com wrote: Dear Amit, Please see attached sheet, hope it will help to u. -- Thanks regards, Noorain Ansari *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ *http://noorain-ansari.blogspot.com/*http://noorain-ansari.blogspot.com/ On Sat, Sep 24, 2011 at 5:31 PM, Amit Gandhi silkyro...@gmail.comwrote: Hi Folks I am finding some problem in excel data. I have a excel data (attached file) in vertical form. I want to convert it into Horizontal form using Excel Formulas. Pls help me here. Regards Amit -- 'Expecting the world to treat u fairly coz u r a good person is like expecting the lion not to attack u coz u r a vegetarian. Think about it.' Take care Amit -- -- 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
Re: $$Excel-Macros$$ Re: Retrieving data in horizontal form
thanks Noorain. On Mon, Sep 26, 2011 at 11:40 AM, NOORAIN ANSARI noorain.ans...@gmail.comwrote: Dear Amit, Please see attached sheet.. and use * =INDEX($B$4:$B$150,SMALL(IF($D$4:$D$150=1,ROW($B$4:$B$150),),ROW(A3))-3) =Offset($B$4,SMALL(IF($D$4:$D$150=1,ROW($B$4:$B$150),),ROW(A3))-3,0)* -- Thanks regards, Noorain Ansari *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ *http://noorain-ansari.blogspot.com/*http://noorain-ansari.blogspot.com/ On Mon, Sep 26, 2011 at 10:33 AM, Amit Gandhi silkyro...@gmail.comwrote: Thanks alot Noorain It works fine. But I also want to automate Column E i.e. it automatically retrieve Co Name as well. If you can help me here as well. Regards Amit On Sat, Sep 24, 2011 at 8:54 PM, NOORAIN ANSARI noorain.ans...@gmail.com wrote: Dear Amit, Please see attached sheet, hope it will help to u. -- Thanks regards, Noorain Ansari *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ *http://noorain-ansari.blogspot.com/*http://noorain-ansari.blogspot.com/ On Sat, Sep 24, 2011 at 5:31 PM, Amit Gandhi silkyro...@gmail.comwrote: Hi Folks I am finding some problem in excel data. I have a excel data (attached file) in vertical form. I want to convert it into Horizontal form using Excel Formulas. Pls help me here. Regards Amit -- 'Expecting the world to treat u fairly coz u r a good person is like expecting the lion not to attack u coz u r a vegetarian. Think about it.' Take care Amit -- -- 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 -- 'Expecting the world to treat u fairly coz u r a good person is like expecting the lion not to attack u coz u r a vegetarian. Think about it.' Take care Amit -- -- 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
Re: $$Excel-Macros$$ Re: Retrieving data in horizontal form
Hi Sam solution provided by chilexcel is OK, but i want to go further in full automation using excel formulas. Thanks Amit On Mon, Sep 26, 2011 at 11:49 AM, Sam Mathai Chacko samde...@gmail.comwrote: Nicely done by Noorain. Shouldn't the solution provided by ChilExcel suffice? Pivot tables as it is dynamic in nature, you just need to pass the source range. Regards, Sam Mathai Chacko (GL) On Mon, Sep 26, 2011 at 11:40 AM, NOORAIN ANSARI noorain.ans...@gmail.com wrote: Dear Amit, Please see attached sheet.. and use * =INDEX($B$4:$B$150,SMALL(IF($D$4:$D$150=1,ROW($B$4:$B$150),),ROW(A3))-3) =Offset($B$4,SMALL(IF($D$4:$D$150=1,ROW($B$4:$B$150),),ROW(A3))-3,0)* -- Thanks regards, Noorain Ansari *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ *http://noorain-ansari.blogspot.com/*http://noorain-ansari.blogspot.com/ On Mon, Sep 26, 2011 at 10:33 AM, Amit Gandhi silkyro...@gmail.comwrote: Thanks alot Noorain It works fine. But I also want to automate Column E i.e. it automatically retrieve Co Name as well. If you can help me here as well. Regards Amit On Sat, Sep 24, 2011 at 8:54 PM, NOORAIN ANSARI noorain.ans...@gmail.com wrote: Dear Amit, Please see attached sheet, hope it will help to u. -- Thanks regards, Noorain Ansari *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ *http://noorain-ansari.blogspot.com/*http://noorain-ansari.blogspot.com/ On Sat, Sep 24, 2011 at 5:31 PM, Amit Gandhi silkyro...@gmail.comwrote: Hi Folks I am finding some problem in excel data. I have a excel data (attached file) in vertical form. I want to convert it into Horizontal form using Excel Formulas. Pls help me here. Regards Amit -- 'Expecting the world to treat u fairly coz u r a good person is like expecting the lion not to attack u coz u r a vegetarian. Think about it.' Take care Amit -- -- 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 -- 'Expecting the world to treat u fairly coz u r a good person is like expecting the lion not to attack u coz u r a vegetarian. Think about it.' Take care Amit -- -- 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
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 pre 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
Re: $$Excel-Macros$$ Need help with forecast formula
It looks like you are forecasting daily ship $ correctly. Is the problem in summing up the total for the month or in showing cumulative totals in your month-to-date (column L)? If the latter is the case, you can simply use an if function to grab from column M when column E has a null value. Steven -- -- 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
Re: $$Excel-Macros$$ Trap window activate event from personal macro workbook
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 pre 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
Re: $$Excel-Macros$$ Fwd: Required Latest No. of Drawing - Help
This isn't showing the After example as requested. What this means is: Show us what you want the result to look like Do you want to know the DATE of the latest version? If so, you can create a column with the formula for row 4: =MAX(E4:N4) that would give you the most recent date. If you're wanting the Rev #, then you could use something like: =INDIRECT(R3CMATCH(MAX(E4:N4),E4:N4)+4,FALSE) 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: Rohan Young rohan.j...@gmail.com To: excel-macros@googlegroups.com Sent: Mon, September 26, 2011 3:18:27 AM Subject: Re: $$Excel-Macros$$ Fwd: Required Latest No. of Drawing - Help On Thu, Sep 22, 2011 at 6:04 PM, dguillett1 dguille...@gmail.com wrote: Can you provide an AFTER example -Original Message- From: Rohan Young Sent: Thursday, September 22, 2011 2:30 AM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Fwd: Required Latest No. of Drawing - Help -- Forwarded message -- From: Rohan Young rohan.j...@gmail.com Date: Tue, Jul 26, 2011 at 2:22 PM Subject: Required Latest No. of Drawing - Help To: excel-macros@googlegroups.com Dear Experts I want to make a list drawing in which i required only latest revision of drawings it can be R3, R5, i need only latest version rest of previous i do not required in that list please help me out with any formula or vba code (see attachment) Thanks Regds, -- -- - 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 -- -- 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
$$Excel-Macros$$ Need Urgent help
Hi, I want to learn VBA Excel so what is the path that should i follow ,does any book will help or what means i despretely learn VBA ,so please help me how to learn -- syed aliya -- -- 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
Re: $$Excel-Macros$$ Fwd: Required Latest No. of Drawing - Help
Can you show us an AFTER example(s) of what you want this to look like.. -Original Message- From: Rohan Young Sent: Monday, September 26, 2011 2:18 AM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Fwd: Required Latest No. of Drawing - Help On Thu, Sep 22, 2011 at 6:04 PM, dguillett1 dguille...@gmail.com wrote: Can you provide an AFTER example -Original Message- From: Rohan Young Sent: Thursday, September 22, 2011 2:30 AM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Fwd: Required Latest No. of Drawing - Help -- Forwarded message -- From: Rohan Young rohan.j...@gmail.com Date: Tue, Jul 26, 2011 at 2:22 PM Subject: Required Latest No. of Drawing - Help To: excel-macros@googlegroups.com Dear Experts I want to make a list drawing in which i required only latest revision of drawings it can be R3, R5, i need only latest version rest of previous i do not required in that list please help me out with any formula or vba code (see attachment) Thanks Regds, -- -- 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 -- -- 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
Re: $$Excel-Macros$$ vlookup for two same value in acceding order with two Criteria
Thanks, It worked, but there is one argument, if there are more then three entries in a particular day i.e. on 1st of sept there are three diffrent times mentioned or more than that, then very first and very last time should reflects. (means in time of morning and out time of evening) I have highligthed cell in attached format. the argument is that in a single day more than two entries could be enter. -- -- 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 attensolutionvkey.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Re: $$Excel-Macros$$ Trap window activate event from personal macro workbook
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.netwrote: 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 pre 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
Re: $$Excel-Macros$$ Trap window activate event from personal macro workbook
Dear sam/paul Isempty(activecell.value) is OK now.But the macro doesn't work as i expected,It triggers only once.Actually what i want is whenever I switch from SAP window to EXCEL window(Focus is set to EXCEL),the macro should execute. As i have understood,the *workbook_activate *event triggers only when we are switching between excel windows.Kindly help Aju v chacko On Mon, Sep 26, 2011 at 9:24 PM, Sam Mathai Chacko samde...@gmail.comwrote: 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.netwrote: 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 pre 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
Re: $$Excel-Macros$$ Trap window activate event from personal macro workbook
It looks like you are correct! the Workbook_Activate event only triggers when switching between Excel workbooks. It looks like there should be an Application.WindowActivate event, but I haven't been able to get it to work in the small test case I tried. I also found an event that requires a class module, but have not looked at it further. You COULD create a SelectionChange event, that would trigger if a cell is selected, but that would require you to select the Excel application, AND select a cell. (any cell would do) I'll try to look further. 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 1:10:44 PM Subject: Re: $$Excel-Macros$$ Trap window activate event from personal macro workbook Dear sam/paul Isempty(activecell.value) is OK now.But the macro doesn't work as i expected,It triggers only once.Actually what i want is whenever I switch from SAP window to EXCEL window(Focus is set to EXCEL),the macro should execute. As i have understood,the workbook_activate event triggers only when we are switching between excel windows.Kindly help Aju v chacko On Mon, Sep 26, 2011 at 9:24 PM, Sam Mathai Chacko samde...@gmail.com wrote: 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 pre 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
Re: $$Excel-Macros$$ vlookup for two same value in acceding order with two Criteria
See attached to do as desired From: vickey Sent: Monday, September 26, 2011 10:14 AM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ vlookup for two same value in acceding order with two Criteria Thanks, It worked, but there is one argument, if there are more then three entries in a particular day i.e. on 1st of sept there are three diffrent times mentioned or more than that, then very first and very last time should reflects. (means in time of morning and out time of evening) I have highligthed cell in attached format. the argument is that in a single day more than two entries could be enter. -- -- 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 attensolutionvkey.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Re: $$Excel-Macros$$ Trap window activate event from personal macro workbook
Ah! now you are talking. Aju, unfortunately there is no application level even that triggers when the application is active. The application class events are associated to the workbook events and below. And I am not sure if an API can be used to do this. the other suggestion I have is to use a scheduled event for every second that will run in the background and run when the app is active, but that could be flawed. Regards, Sam On 9/26/11, Paul Schreiner schreiner_p...@att.net wrote: It looks like you are correct! the Workbook_Activate event only triggers when switching between Excel workbooks. It looks like there should be an Application.WindowActivate event, but I haven't been able to get it to work in the small test case I tried. I also found an event that requires a class module, but have not looked at it further. You COULD create a SelectionChange event, that would trigger if a cell is selected, but that would require you to select the Excel application, AND select a cell. (any cell would do) I'll try to look further. 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 1:10:44 PM Subject: Re: $$Excel-Macros$$ Trap window activate event from personal macro workbook Dear sam/paul Isempty(activecell.value) is OK now.But the macro doesn't work as i expected,It triggers only once.Actually what i want is whenever I switch from SAP window to EXCEL window(Focus is set to EXCEL),the macro should execute. As i have understood,the workbook_activate event triggers only when we are switching between excel windows.Kindly help Aju v chacko On Mon, Sep 26, 2011 at 9:24 PM, Sam Mathai Chacko samde...@gmail.com wrote: 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 pre 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
Re: $$Excel-Macros$$ Trap window activate event from personal macro workbook
dear sam/paul, Thanks for your guidance regards aju On 9/27/11, Sam Mathai Chacko samde...@gmail.com wrote: Ah! now you are talking. Aju, unfortunately there is no application level even that triggers when the application is active. The application class events are associated to the workbook events and below. And I am not sure if an API can be used to do this. the other suggestion I have is to use a scheduled event for every second that will run in the background and run when the app is active, but that could be flawed. Regards, Sam On 9/26/11, Paul Schreiner schreiner_p...@att.net wrote: It looks like you are correct! the Workbook_Activate event only triggers when switching between Excel workbooks. It looks like there should be an Application.WindowActivate event, but I haven't been able to get it to work in the small test case I tried. I also found an event that requires a class module, but have not looked at it further. You COULD create a SelectionChange event, that would trigger if a cell is selected, but that would require you to select the Excel application, AND select a cell. (any cell would do) I'll try to look further. 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 1:10:44 PM Subject: Re: $$Excel-Macros$$ Trap window activate event from personal macro workbook Dear sam/paul Isempty(activecell.value) is OK now.But the macro doesn't work as i expected,It triggers only once.Actually what i want is whenever I switch from SAP window to EXCEL window(Focus is set to EXCEL),the macro should execute. As i have understood,the workbook_activate event triggers only when we are switching between excel windows.Kindly help Aju v chacko On Mon, Sep 26, 2011 at 9:24 PM, Sam Mathai Chacko samde...@gmail.com wrote: 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 pre 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
