Re: $$Excel-Macros$$ Excel VBA Macros eBook ..Absolutely FREE worth $45
Hey There is no password, I opened it without password. Regards Sumit On Tue, Nov 9, 2010 at 3:03 PM, Upendra Singh Sengar upendrasinghsen...@gmail.com wrote: Hi Ayush, What is the password to opent the file. (¨`·.·´¨) Keep `·.¸(¨`·.·´¨) Smiling !! (¨`·.·´¨)¸.·´ Upendra Singh `·.¸.·´9910227325 On 8 November 2010 19:35, Ayush jainayus...@gmail.com wrote: Dear Group Members, Good news for all of you !! Now you can download VBA macros ebook worth $45 for free. Here is the link to download :- http://recordexcelmacro.com/discussexcel.html I have talked to John Franco (Author) to get this eBook for our group. Please do not miss the chance to download the eBook. This eBook has 140 pages of valuable knowledge on recording macros and customize them. I am working with John to get more resources for you either free or paid.Keep watching this space. Feel free to forward this link and email to all your friends, coworkers and followers. So what are you waiting for...Download it now.. Thanks. Best regards, Ayush Jain Microsoft MVP 2010 -- -- 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/pages/discussexcelcom/160307843985936?v=wallref=ts -- -- 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/pages/discussexcelcom/160307843985936?v=wallref=ts -- -- 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/pages/discussexcelcom/160307843985936?v=wallref=ts
Re: $$Excel-Macros$$ Excel VBA Macros eBook ..Absolutely FREE worth $45
Hi Sumit, Kindly mail me the PDF u've downloaded. (¨`·.·´¨) Keep `·.¸(¨`·.·´¨) Smiling !! (¨`·.·´¨)¸.·´ Upendra Singh `·.¸.·´9910227325 On 9 November 2010 15:22, sumit kumar sagars...@gmail.com wrote: Hey There is no password, I opened it without password. Regards Sumit On Tue, Nov 9, 2010 at 3:03 PM, Upendra Singh Sengar upendrasinghsen...@gmail.com wrote: Hi Ayush, What is the password to opent the file. (¨`·.·´¨) Keep `·.¸(¨`·.·´¨) Smiling !! (¨`·.·´¨)¸.·´ Upendra Singh `·.¸.·´9910227325 On 8 November 2010 19:35, Ayush jainayus...@gmail.com wrote: Dear Group Members, Good news for all of you !! Now you can download VBA macros ebook worth $45 for free. Here is the link to download :- http://recordexcelmacro.com/discussexcel.html I have talked to John Franco (Author) to get this eBook for our group. Please do not miss the chance to download the eBook. This eBook has 140 pages of valuable knowledge on recording macros and customize them. I am working with John to get more resources for you either free or paid.Keep watching this space. Feel free to forward this link and email to all your friends, coworkers and followers. So what are you waiting for...Download it now.. Thanks. Best regards, Ayush Jain Microsoft MVP 2010 -- -- 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/pages/discussexcelcom/160307843985936?v=wallref=ts -- -- 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/pages/discussexcelcom/160307843985936?v=wallref=ts -- -- 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/pages/discussexcelcom/160307843985936?v=wallref=ts -- -- 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/pages/discussexcelcom/160307843985936?v=wallref=ts
$$Excel-Macros$$ small help
hi friends plz find the attached sheet in which i want a formula in which if i enter the month it should give no. of days of a month in the next column. please help me out -- Sidhu -- -- 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/pages/discussexcelcom/160307843985936?v=wallref=ts New Microsoft Excel Worksheet.xls Description: MS-Excel spreadsheet
Re: $$Excel-Macros$$ small help
Dear Sudarshan Please see the attached file with the solution. On Tue, Nov 9, 2010 at 1:06 PM, sudarshan rampe sidhu...@gmail.com wrote: hi friends plz find the attached sheet in which i want a formula in which if i enter the month it should give no. of days of a month in the next column. please help me out -- Sidhu -- -- 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/pages/discussexcelcom/160307843985936?v=wallref=ts -- Regards Yahya -- -- 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/pages/discussexcelcom/160307843985936?v=wallref=ts Sudarshan.xls Description: MS-Excel spreadsheet
Re: $$Excel-Macros$$ array sort issue
First of all... You would think that Application.WorksheetFunction would apply to ALL worksheet functions. But in reality, it does not. ONLY those WorksheetFunctions that were written in a manner accessible to VBA are available. It's something about the environment where they were developed. That's a long way of saying that the Len() function is not available as a Worksheet function. But you don't need it as a worksheet function, because it is available as a VBA function! Simply use: iLen = Len(ary(iLoc)) which, is exactly what you did with: If Len(ary(iLoc)) iLen Then so... Paul - Original Message From: Ted suicid...@gmail.com To: MS EXCEL AND VBA MACROS excel-macros@googlegroups.com Sent: Mon, November 8, 2010 1:46:07 PM Subject: $$Excel-Macros$$ array sort issue In an attempt to develop my own sort for an array, I seem to have run into a problem. I don't know what I 'm doing wrong specifically, but as soon as I attempt to invoke the Len Function, the whole thing breaks. subscript out of range error. I think I may be overthinking this, so any guidance is much appreciated. Public Function arraySortByStringLength(ary As Variant) Dim iLoc As Long, iLen As Integer, iAry As Variant, i As Long Dim val As String, indx As Long iLen = ReDim iAry(UBound(ary)) i = 0 indx = 0 Do For iLoc = 0 To UBound(ary) If Len(ary(iLoc)) iLen Then iLen = Excel.WorksheetFunction.Len(ary(iLoc)) indx = iLoc val = ary(iLoc) End If Next iLoc iAry(i) = val ReDim Preserve ary(UBound(ary) - 1) ary = arraySlice(ary, indx) i = i + 1 Loop Until UBound(ary) 1 arraySortByStringLength = iAry End Function -- -- - 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/pages/discussexcelcom/160307843985936?v=wallref=ts -- -- 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/pages/discussexcelcom/160307843985936?v=wallref=ts
Re: $$Excel-Macros$$ array sort issue
Hi Ted, Instead of Excel.WorksheetFunction.Len just use Len. So your code would look like: iLen = Len(ary(iLoc)) Len function is available as VBA function too, so you don't need to use excel function. This is same as you are doing for conditional statement, IF. Note: The moment you press . (period or dot) all the functions, objects and properties appear alongwith. So, when you'll check, you won't find any LEN method under WorksheetFunction. Thanks Regards Ashish Jain McKinsey India Knowledge Center (Microsoft Certified Application Specialist) (Microsoft Certified Professional) http://www.excelitems.com http://www.openexcel.com On Nov 8, 11:46 pm, Ted suicid...@gmail.com wrote: In an attempt to develop my own sort for an array, I seem to have run into a problem. I don't know what I 'm doing wrong specifically, but as soon as I attempt to invoke the Len Function, the whole thing breaks. subscript out of range error. I think I may be overthinking this, so any guidance is much appreciated. Public Function arraySortByStringLength(ary As Variant) Dim iLoc As Long, iLen As Integer, iAry As Variant, i As Long Dim val As String, indx As Long iLen = ReDim iAry(UBound(ary)) i = 0 indx = 0 Do For iLoc = 0 To UBound(ary) If Len(ary(iLoc)) iLen Then iLen = Excel.WorksheetFunction.Len(ary(iLoc)) indx = iLoc val = ary(iLoc) End If Next iLoc iAry(i) = val ReDim Preserve ary(UBound(ary) - 1) ary = arraySlice(ary, indx) i = i + 1 Loop Until UBound(ary) 1 arraySortByStringLength = iAry End Function -- -- 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/pages/discussexcelcom/160307843985936?v=wallref=ts
$$Excel-Macros$$ Re: is there a printer installed on this computer?
Hi Omar, There are 2 ways: 1. You can get the printer name using Application.ActivePrinter So, if there is no printer installed, skip through the code. 2. Error Handling to check printer installed or not On Error GoTo RESCUE Activesheet.printpreview 'your code GOTO NEXTSTEP RESCUE: ' Your code if error NEXTSTEP: ' When all is OK Thanks Regards Ashish Jain McKinsey India Knowledge Center (Microsoft Certified Application Specialist) (Microsoft Certified Professional) http://www.excelitems.com http://www.openexcel.com On Nov 1, 10:31 pm, عمر omar27...@gmail.com wrote: I have a code to copy some data from sheet to another In the end of this code There is some lines to set up page layout page setup Header and footer This part of code doesn't work when I run my file on computer without printer installed And give me an error The question is How can I make this code works well when the printer is installed or when not installed ? Thanks -- -- 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/pages/discussexcelcom/160307843985936?v=wallref=ts
$$Excel-Macros$$ Re: Delete values within an array
Hi Matt, That's Great. We're our best teachers. Kudos :) Thanks Regards Ashish Jain McKinsey India Knowledge Center (Microsoft Certified Application Specialist) (Microsoft Certified Professional) http://www.excelitems.com http://www.openexcel.com On Nov 4, 11:01 pm, RemyMaza remym...@gmail.com wrote: I ended up fixing this by using a filter on my array: 'ReDim arrDeviceNames() to trim the EMPTY DEVICE arrNonEmptyDeviceNames = Filter(arrDeviceNames, EMPTY DEVICE, False) Thanks guys, Matt On Nov 3, 9:13 am, RemyMaza remym...@gmail.com wrote: I have an array of values that come in off of a form. One of these values is Empty Device. Here's the code I'm using to handle that: Devices = Array(cmb1.Value, cmb2.Value, cmb3.Value, cmb4.Value, cmb5.Value, cmb6.Value, cmb7.Value) Now that I have my Devices array, I loop through the number of devices that were selected and assign values to other variables and another array. Here's some of that code: For i = 0 To UBound(Devices) If Not Devices(i) = Then NumOfDevices = NumOfDevices + 1 If Devices(i) = EMPTY DEVICE Then NumOfEmptyDevices = NumOfEmptyDevices + 1 'Match Description to Ganged Select Case True Case Devices(i) = EMPTY DEVICE Ganged = Ganged X ReDim Preserve arrDeviceName(0 To i) arrDeviceName(i) = EMPTY DEVICE Case Devices(i) = 20A RECEPTACLE Ganged = Ganged O ReDim Preserve arrDeviceName(0 To i) arrDeviceName(i) = D20120 Case Devices(i) = 20A GFI Ganged = Ganged G ReDim Preserve arrDeviceName(0 To i) arrDeviceName(i) = G20120 I need to find out a way to handle EMPTY DEVICES. Later on in the code, I loop through the TotalNumOfDevices which equals all of the devices picked except for EMPTY DEVICE. That throws off my looping, because the numbers aren't ever equal when these are chosen. How would I dip into the arrDeviceName array and trim out all of the EMPTY DEVICES. Better yet, would be how to keep EMPTY DEVICE from even getting into the arrary. Currently, when I comment out the ReDim statment for EMPTY DEVICE, I still get a value of within my array. This essentially is throwing off the whole calculation. I'd appreciate any input! Cheers, Matt -- -- 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/pages/discussexcelcom/160307843985936?v=wallref=ts
Re: $$Excel-Macros$$ Re: Help Required!
Hi, I have attached a spreadsheet which contains list of document numbers and their respective names. What I want is I need a word macro that will do the following. Note: The file name will always starts with the Doc Number (5 digits). Ex: 12345 (Refer Sample_Findlaw.xls for list of Doc Number) User will enter the Name of the corresponding Doc Number in the word document. I need a macro that would populate a message box like Mismatch between Name and Doc Number when they enter wrong name in the word document. Example: Consider File Name is 97854Test1.doc (Refer the spreadsheet Sample_Findlaw.xls) Name for this Doc Number is : New York If the user enter Texas instead of New York then the macro would populate a message box. Please help me to fix this. Thanks, Sharma On Mon, Nov 1, 2010 at 10:41 AM, amrahs k amrahs...@gmail.com wrote: Hi, This is what I actually looking for. It works great and solves my query. Thanks for your efforts and help. Regards, Sharma On Sun, Oct 31, 2010 at 4:11 AM, roberto mensa robb@gmail.comwrote: the sub test is just one example of how to use the function into a routine. Sub test() Dim s As String s = AGREEMENT. THE ADOPTION THE ADULT OR MARRIED MINOR s = ProperCase_r(s) Debug.Print s End Sub r -- -- 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/pages/discussexcelcom/160307843985936?v=wallref=ts -- -- 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/pages/discussexcelcom/160307843985936?v=wallref=ts 97854Test1.doc Description: MS-Word document Sample_Findlaw.xls Description: MS-Excel spreadsheet
$$Excel-Macros$$ Conditional Formatting
Conditional Formatting You can use something called Conditional Formatting in your spreadsheets. Conditional Formatting allows you to change the appearance of a cell, depending on certain conditions. What we'll do is to colour the Overall Averages on our Student Exam spreadsheet, depending on the grade. Here's the spreadsheet we'll be working on. [image: The Student Grades Spreadsheet] - Open up your Student Exam spreadsheet (You did complete it, didn't you?) - Highlight the cells with Overall Grades, which should be cells B11 to I11 The Overall Averages range from 44 to 85. We'll colour each grade, depending on a scale. A different colour will apply to the following grades: - 50 and below - 51 to 60 - 61 to 70 - 71 to 80 - 81 and above So five different bands, and a colour for each. To set the Conditional Formatting in Excel 2007, do the following: - With your Overall Averages highlighted, click on the Home menu at the top of Excel - Locate the *Styles* panel, and the *Conditional Formatting* item: [image: The Styles Panel in Excel 2007] The Conditional Formatting menu gives you various options. The easiest one is the Colour Scales option. Select one of these and Excel will colour the cell backgrounds for you: [image: Color Scales in Excel 2007] That's not quite what we're looking for, though. We'd like to choose our own values. So click on *More Rules*, from the *Colour Scales* submenu. You'll see the following rather complex dialogue box: [image: New Formatting Rule dialogue box] The one we want is the second option, *Format only cells that contain*. This will allow us to set up our values. When you click this option, the dialogue box changes to this: The part we're interested in is the bottom part, under the heading *Edit the Rule Description*. It says *Cell Value* and *Between*, in the drop down boxes. These are the ones we want. We only need to type a value for the two boxes that are currently blank in the image above. We can then click the * Format* button to choose a colour. So type 0 in the first box and 50 in the second one: Then click the *Format* button. You'll get another dialogue box popping up. This is just the Format Cells one though. You've met this before. Click on the Fill tab and choose a colour. Click OK and you should see something like this under Edit the Rule Description: The Preview is showing the colour we picked. So we've said, *If* the Cell Value is between 0 and 50 *then* colour the cell Red. Click OK on this dialogue box to get back to Excel. You should find that one of the cells has turned red. To format the rest of the cells, click on Conditional Formatting on the Styles panel again. From the menu, click on *Manage Rules*: You'll get yet another complex dialogue box popping up! This one: Our first rule is already there - Cell Value Between. The only thing we're doing here is adding New Rules, similar to the one we've just set up. Click the *New Rule* button then. You'll see the exact same dialogue boxes you used to set up the first rule. Set a new colour for the next scores - 51 to 60. Choose a colour, and keep clicking OK until you get back to the Rules Manager dialogue box. It should now look something like this one: We now have to colours in our range. Do the rest of the scores, choosing a colour for each. The scores are these, remember: - 50 and below - 51 to 60 - 61 to 70 - 71 to 80 - 81 and above When you've done them all, your dialogue box should have five colours: The colours above are entirely arbitrary, and you don't have to select the same ones we did. The point is to have a different colour for each range of scores. But click OK when you're done. Your Overall Averages will then look something like this: Formatting your spreadsheet in this way allows you to see at a glance relevant information. In the spreadsheet above, it's obvious who's failing - just look for the red cells! -- -- 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/pages/discussexcelcom/160307843985936?v=wallref=ts
Re: $$Excel-Macros$$ Excel VBA Macros eBook ..Absolutely FREE worth $45
Hi Ayush, Thanks a ton.. for the effort.. Something seems to be wrong at my end as the link you have given is not opening.. Is there any way you can send the file to me plz.. Cheers, Andy On Mon, Nov 8, 2010 at 7:35 PM, Ayush jainayus...@gmail.com wrote: Dear Group Members, Good news for all of you !! Now you can download VBA macros ebook worth $45 for free. Here is the link to download :- http://recordexcelmacro.com/discussexcel.html I have talked to John Franco (Author) to get this eBook for our group. Please do not miss the chance to download the eBook. This eBook has 140 pages of valuable knowledge on recording macros and customize them. I am working with John to get more resources for you either free or paid.Keep watching this space. Feel free to forward this link and email to all your friends, coworkers and followers. So what are you waiting for...Download it now.. Thanks. Best regards, Ayush Jain Microsoft MVP 2010 -- -- 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/pages/discussexcelcom/160307843985936?v=wallref=ts -- -- 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/pages/discussexcelcom/160307843985936?v=wallref=ts
Re: $$Excel-Macros$$ Conditional Formatting
Great info... Thanks for sharing keep it up.. On Tue, Nov 9, 2010 at 7:01 PM, Rajasekhar Praharaju rajasekhar.prahar...@gmail.com wrote: Conditional Formatting You can use something called Conditional Formatting in your spreadsheets. Conditional Formatting allows you to change the appearance of a cell, depending on certain conditions. What we'll do is to colour the Overall Averages on our Student Exam spreadsheet, depending on the grade. Here's the spreadsheet we'll be working on. [image: The Student Grades Spreadsheet] - Open up your Student Exam spreadsheet (You did complete it, didn't you?) - Highlight the cells with Overall Grades, which should be cells B11 to I11 The Overall Averages range from 44 to 85. We'll colour each grade, depending on a scale. A different colour will apply to the following grades: - 50 and below - 51 to 60 - 61 to 70 - 71 to 80 - 81 and above So five different bands, and a colour for each. To set the Conditional Formatting in Excel 2007, do the following: - With your Overall Averages highlighted, click on the Home menu at the top of Excel - Locate the *Styles* panel, and the *Conditional Formatting* item: [image: The Styles Panel in Excel 2007] The Conditional Formatting menu gives you various options. The easiest one is the Colour Scales option. Select one of these and Excel will colour the cell backgrounds for you: [image: Color Scales in Excel 2007] That's not quite what we're looking for, though. We'd like to choose our own values. So click on *More Rules*, from the *Colour Scales* submenu. You'll see the following rather complex dialogue box: [image: New Formatting Rule dialogue box] The one we want is the second option, *Format only cells that contain*. This will allow us to set up our values. When you click this option, the dialogue box changes to this: The part we're interested in is the bottom part, under the heading *Edit the Rule Description*. It says *Cell Value* and *Between*, in the drop down boxes. These are the ones we want. We only need to type a value for the two boxes that are currently blank in the image above. We can then click the *Format* button to choose a colour. So type 0 in the first box and 50 in the second one: Then click the *Format* button. You'll get another dialogue box popping up. This is just the Format Cells one though. You've met this before. Click on the Fill tab and choose a colour. Click OK and you should see something like this under Edit the Rule Description: The Preview is showing the colour we picked. So we've said, *If* the Cell Value is between 0 and 50 *then* colour the cell Red. Click OK on this dialogue box to get back to Excel. You should find that one of the cells has turned red. To format the rest of the cells, click on Conditional Formatting on the Styles panel again. From the menu, click on *Manage Rules*: You'll get yet another complex dialogue box popping up! This one: Our first rule is already there - Cell Value Between. The only thing we're doing here is adding New Rules, similar to the one we've just set up. Click the *New Rule* button then. You'll see the exact same dialogue boxes you used to set up the first rule. Set a new colour for the next scores - 51 to 60. Choose a colour, and keep clicking OK until you get back to the Rules Manager dialogue box. It should now look something like this one: We now have to colours in our range. Do the rest of the scores, choosing a colour for each. The scores are these, remember: - 50 and below - 51 to 60 - 61 to 70 - 71 to 80 - 81 and above When you've done them all, your dialogue box should have five colours: The colours above are entirely arbitrary, and you don't have to select the same ones we did. The point is to have a different colour for each range of scores. But click OK when you're done. Your Overall Averages will then look something like this: Formatting your spreadsheet in this way allows you to see at a glance relevant information. In the spreadsheet above, it's obvious who's failing - just look for the red cells! -- -- 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/pages/discussexcelcom/160307843985936?v=wallref=ts -- *Regards* * * *Ashish Koul* *akoul*.*blogspot*.com http://akoul.blogspot.com/ *akoul*.wordpress.com http://akoul.wordpress.com/
$$Excel-Macros$$ Re: Excel_tips
Thanks Viju for sharing these tips. Great. Keep it up :) Regards Ashish Jain McKinsey India Knowledge Center (Microsoft Certified Application Specialist) (Microsoft Certified Professional) http://www.excelitems.com http://www.openexcel.com On Nov 8, 4:05 pm, viju mobile vijumob...@gmail.com wrote: Excel_tips simple.pps 574KViewDownload -- -- 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/pages/discussexcelcom/160307843985936?v=wallref=ts
$$Excel-Macros$$ Re: Spreadsheet screen refresh is very slow AFTER macro runs
Hi Mike, Can you notice and send the back the change in Memory Usage by Excel application before and after running the macro? You can locate it in Task manager. You can try clearing clipboard(Application.CutCopyMode = False) to experiment harmlessly. Thanks Regards Ashish Jain McKinsey India Knowledge Center (Microsoft Certified Application Specialist) (Microsoft Certified Professional) http://www.excelitems.com http://www.openexcel.com On Nov 1, 9:37 pm, Mike Magill mike.mag...@gmail.com wrote: Hi, I have a rather long macro that controls and limits the printing process for the end user. It works okay but after running and you return to the spreadsheet the screen is very slow to refresh as you scroll around the worksheet. The macro gives the user the choice of viewing a filtered set of results on screen (Print_View_Option = V) or printing out the result (Print_View_Option = V). I think I've worked out that the issue only occurs when the user tries to print out the results and the macro adjusts the Page Setup. I've done some research that suggests the Page Setup routine is inherently slow but this doesn't explain the slowness AFTER the macro has finished. I've attached a slightly simplified version of the macro below that still manifests the same problem. Can anyone help? Sub Print_Options2() Dim RR As Object Dim LastRow As Long Dim ReportOrder As String Dim OverallFilterType As String Dim IndividualFilterType As String Dim PaperSize As String Dim ReportType As String Set RR = ThisWorkbook.Sheets(Risk Register) Print_View_Option = P PaperSize = A4 ReportType = Full Risk Register OverallFilterType = A IndividualFilterType = All Individual Control Assessments Application.ScreenUpdating = False Application.EnableEvents = False RR.Unprotect Password:=Password On Error Resume Next RR.ShowAllData On Error GoTo 0 ' Ensures any rows with wrapped text are expanded so that all text is visible LastRow = RR.Range(AD Rows.Count).End(xlUp).Row RR.Rows(6: LastRow).EntireRow.AutoFit ' Hide rows with no data Selection.AutoFilter Field:=30, Criteria1:=x If Application.Dialogs(xlDialogPrinterSetup).Show Then RR.DisplayPageBreaks = False With RR.PageSetup If PaperSize = A3 Then .PaperSize = xlPaperA3 Else .PaperSize = xlPaperA4 End If .PrintArea = $B:$AB .LeftFooter = _ Arial,BoldPrint Criteria:Arial,Regular Chr(10) _ - ReportType Chr(10) - OverallFilterType Chr(10) _ - IndividualFilterType .RightFooter = RR.Range(K1).Value End With ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True End If ' Show rows with no data Selection.AutoFilter Field:=30 ' When user has chosen to Print rather than View revert all settings back to standard If Print_View_Option = P Then ' Revert hidden columns to original state If RR.Range(J2) = Consolidation Then RR.Columns(A:A).EntireColumn.Hidden = True RR.Columns(B:B).EntireColumn.Hidden = False Else RR.Columns(A:B).EntireColumn.Hidden = True End If RR.Columns(C:P).EntireColumn.Hidden = False RR.Columns(Q:R).EntireColumn.Hidden = True RR.Columns(S:V).EntireColumn.Hidden = False RR.Columns(X:AB).EntireColumn.Hidden = False RR.Columns(AC:CD).EntireColumn.Hidden = True On Error Resume Next RR.ShowAllData On Error GoTo 0 End If RR.Range(I3).Activate RR.Protect Password:=Password, DrawingObjects:=True, Contents:=True, Scenarios:=True Application.ScreenUpdating = True Application.EnableEvents = True Set RR = Nothing 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/pages/discussexcelcom/160307843985936?v=wallref=ts
$$Excel-Macros$$ Macro copy /paste cell content if found
Hi, The following code will split the content in column K by using | as a border key, but would like place another condition to move the cells in different columns based on the character found in the cell. Sub Split_content() Dim objRange1 As Range 'Set up the ranges Set objRange1 = Range(K6:K66) 'Do the parse objRange1.TextToColumns _ Destination:=Range(K6), _ DataType:=xlDelimited, _ Tab:=False, _ Semicolon:=False, _ Comma:=False, _ Space:=False, _ Other:=True, _ OtherChar:=| End Sub Attached a sample data: - Sheet1 : Represent the actual data, - Sheet2 : Represent the desired result within sheet1 (*the result should be in sheet1*). I really appreciated if you can give me the modified code on this. Thanks in advance. Jimmy -- -- 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/pages/discussexcelcom/160307843985936?v=wallref=ts Frequency.xls Description: MS-Excel spreadsheet
$$Excel-Macros$$ Column Widths
First attempt at this, so hopefully this is how it works I have a spreadsheet with about 25 tabs and about 200 columns with lots of different widths. As its updated by many the widths get moved about. Before it is released, I run a macro to go through each column in each tab and restore it to its correct width ie along the lines of goto col a, set width 10, go to col B, set widthto 5 etc etc. It might nor be the best but it works fine until I have to add or delte colums. Thn I have to go through and amend the col width in VBa. Do-able but pain. I though I could set up a table that VBa could reference so that inserting a line or two would only require the table to be amended ie Col Width A10 B 5 C25 etc etc Can anyone help with the VBa code both to do this and also another piece to identify the current widths. Thanks -- -- 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/pages/discussexcelcom/160307843985936?v=wallref=ts
Re: $$Excel-Macros$$ Column Widths
There's lots of ways to accomplish this. Some will depend on the version of Excel that you're using. I have Excel 2007. My first suggestion is: Create a template sheet with the desired column widths and hide it. Then, your macro can unhide the template, select row 1 Copy to the clipboard Loop through each sheet and paste-special, column widths Hide your template For this example, the template sheet is called Col_Template (I know: How descriptive) then the macro looks like: Option Explicit Sub Fix_Cols() Dim I, CurSheet ' Application.ScreenUpdating = False CurSheet = ActiveSheet.Name Sheets(Col_Template).Visible = True Sheets(Col_Template).Select Rows(1:1).Select Application.CutCopyMode = False Selection.Copy For I = 1 To Sheets.Count If (Sheets(I).Name Col_Template) Then Sheets(I).Select Range(A1).Select Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Range(B2).Select End If Next I Sheets(CurSheet).Select Sheets(Col_Template).Visible = False Application.ScreenUpdating = True End Sub You COULD even call this macro from a BeforeSave event so that it corrects the columns before saving the file. === As an alternative, if the pastespecial doesn't work, you can still create your template and compare the column widths of each sheet to the template: Sub Fix_Cols() Dim I, CurSheet Dim C CurSheet = ActiveSheet.Name For I = 1 To Sheets.Count If (Sheets(I).Name Col_Template) Then For C = 1 To 100 'just checks the first 100 columns If (Sheets(I).Cells(1, C).ColumnWidth Sheets(Col_Template).Cells(1, C).ColumnWidth) Then Sheets(I).Cells(1, C).ColumnWidth = Sheets(Col_Template).Cells(1, C).ColumnWidth End If Next C End If Next I Sheets(CurSheet).Select Sheets(Col_Template).Visible = False End Sub Either way, these routines updated 100+ column widths in 50 sheets in 2-3 seconds. let me know if either of these work for you. Paul - Original Message From: iggy-mac iggy-...@telinco.co.uk To: MS EXCEL AND VBA MACROS excel-macros@googlegroups.com Sent: Tue, November 9, 2010 10:13:22 AM Subject: $$Excel-Macros$$ Column Widths First attempt at this, so hopefully this is how it works I have a spreadsheet with about 25 tabs and about 200 columns with lots of different widths. As its updated by many the widths get moved about. Before it is released, I run a macro to go through each column in each tab and restore it to its correct width ie along the lines of goto col a, set width 10, go to col B, set widthto 5 etc etc. It might nor be the best but it works fine until I have to add or delte colums. Thn I have to go through and amend the col width in VBa. Do-able but pain. I though I could set up a table that VBa could reference so that inserting a line or two would only require the table to be amended ie Col Width A 10 B 5 C 25 etc etc Can anyone help with the VBa code both to do this and also another piece to identify the current widths. Thanks -- -- - 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/pages/discussexcelcom/160307843985936?v=wallref=ts -- -- 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/pages/discussexcelcom/160307843985936?v=wallref=ts
Re: $$Excel-Macros$$ Fwd: User form
2010/11/9 neil johnson neil.jh...@googlemail.com Hi, I make textbox in user form. The label of this texbox email . When i enter the email address with out using or forget @ and Dot(.). It should give me msg . please use valid email Id . Wht will be code of this . Please find the attache sheet. Private Sub CommandButton1_Click() If isMail(Me.TextBox1.Value) Then 'email ok Else MsgBox mail not valid End If End Sub Function isMail(sMail As String) As Boolean Dim re As Object Set re = CreateObject(vbscript.regexp) re.Pattern = ^\w+([-+.]\w+) _ *...@\w+([-.]\w+) _ *\.\w+([-.]\w+)*$ isMail = re.test(sMail) End Function regards r -- -- 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/pages/discussexcelcom/160307843985936?v=wallref=ts
$$Excel-Macros$$ Help: Using SUMIF(S) Function
Hello Excel Gurus, I encountered a challenge on using SUMIF FUNCTION. Help me on summing total amount in different months into different cells. See attached fine for details. -- -- 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/pages/discussexcelcom/160307843985936?v=wallref=ts Help.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Re: $$Excel-Macros$$ Help: Using SUMIF(S) Function
hi, Check this website hope you will find the resolution http://www.homeandlearn.co.uk/excel2007/excel2007s6p4.html Thanks, Raj On Tue, Nov 9, 2010 at 11:19 PM, janet dickson janetdicks...@gmail.comwrote: Hello Excel Gurus, I encountered a challenge on using SUMIF FUNCTION. Help me on summing total amount in different months into different cells. See attached fine for details. -- -- 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/pages/discussexcelcom/160307843985936?v=wallref=ts -- -- 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/pages/discussexcelcom/160307843985936?v=wallref=ts
Re: $$Excel-Macros$$ Help: Using SUMIF(S) Function
Hi, Write following formula beside date column, i.e. In cell C4: =text(B4,mmm) Now copy this formula in all rows of coloumn C, You will get Month name like Aug in cell C4. Now you can use sum if. Sent on my BlackBerry® from Vodafone -Original Message- From: janet dickson janetdicks...@gmail.com Sender: excel-macros@googlegroups.com Date: Tue, 9 Nov 2010 20:49:59 To: excel-macros@googlegroups.com Reply-To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Help: Using SUMIF(S) Function Hello Excel Gurus, I encountered a challenge on using SUMIF FUNCTION. Help me on summing total amount in different months into different cells. See attached fine for details. -- -- 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/pages/discussexcelcom/160307843985936?v=wallref=ts -- -- 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/pages/discussexcelcom/160307843985936?v=wallref=ts
Re: $$Excel-Macros$$ Re: is there a printer installed on this computer?
Yup Omar, you're right. In your case it will be SUB PAGE_SETUP() On Error GoTo RESCUE '*PAGE SET UP LINES* '* '* '*HEADER FOOTER 'REST CODE OR EXIT SUB RESCUE: ' Your code if error 'RESUME NEXT (if required) END SUB Thanks Regards Ashish Jain McKinsey India Knowledge Center (Microsoft Certified Application Specialist) (Microsoft Certified Professional) http://www.excelitems.com http://www.openexcel.com On Nov 9, 10:11 pm, عمر omar27...@gmail.com wrote: THANKS ashish Before page setup lines we can use on error goto 1 Line Line ... 1 end sub Is that true? -Original Message- From: excel-macros@googlegroups.com [mailto:excel-mac...@googlegroups.com] On Behalf Of Ashish Jain Sent: Tuesday, November 09, 2010 3:36 PM To: MS EXCEL AND VBA MACROS Subject: $$Excel-Macros$$ Re: is there a printer installed on this computer? Hi Omar, There are 2 ways: 1. You can get the printer name using Application.ActivePrinter So, if there is no printer installed, skip through the code. 2. Error Handling to check printer installed or not On Error GoTo RESCUE Activesheet.printpreview 'your code GOTO NEXTSTEP RESCUE: ' Your code if error NEXTSTEP: ' When all is OK Thanks Regards Ashish Jain McKinsey India Knowledge Center (Microsoft Certified Application Specialist) (Microsoft Certified Professional)http://www.excelitems.comhttp://www.openexcel.com On Nov 1, 10:31 pm, عمر omar27...@gmail.com wrote: I have a code to copy some data from sheet to another In the end of this code There is some lines to set up page layout page setup Header and footer This part of code doesn't work when I run my file on computer without printer installed And give me an error The question is How can I make this code works well when the printer is installed or when not installed ? Thanks -- --- --- 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 athttp://www.excel-macros.blogspot.com 4. Learn VBA Macros athttp://www.quickvba.blogspot.com 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below linkhttp://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall;... -- -- 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/pages/discussexcelcom/160307843985936?v=wallref=ts