| Forum Home | ||||
| Press F1 | ||||
| Thread ID: 24666 | 2002-09-16 05:08:00 | Background printing in excel 97 | Dave (1341) | Press F1 |
| Post ID | Timestamp | Content | User | ||
| 79965 | 2002-09-21 06:09:00 | Hi Dave. Sorry, I had assumed you wanted to remove all formatting. To fix just replace the line that has Selection.ClearFormats with... Selection.Interior.ColorIndex = xlNone If you want to have the choice whether to print with or without borders then let me know. Learning some VBA is good knowledge to have. I only recently read a beginners book but have learned enough to read the code and understand what its doing. Best way is to record something then see what its doing under the hood. cheers :-) |
parry (27) | ||
| 79966 | 2002-09-21 22:20:00 | Thanks again for your help Parry. Could you perhaps recommend a Beginners Book on VBA, and I do mean an absolute beginner. Regards Dave |
Dave (1341) | ||
| 79967 | 2002-09-21 22:41:00 | Your welcome Dave. I actually read one of those learn in 24 hours books on VB - the stand alone program which VBA is based upon. Ive read snippets of a friends book called Learn Excel 2000 VBA in 24 Hours which I thought was quite good. I would suggest you get that one. These books are not as comprehensive as others but they teach the basics well and are quite cheap. I didnt want to pay $100+ for a book so these are good options. |
parry (27) | ||
| 79968 | 2002-09-21 22:43:00 | Parry, I have just tried your latest suggestion with changing the "Selection.ClearFormats", and it worked fine. Thanks again for all your help. The only problem now is that you have made me want to learn even more about VBA, as I am now wanting look at printing with certain cells shaded and all others as per your advice. Damn Computers/Softare, they just keep getting you hooked on wanting to learn new ways of doing things. :) Regards Dave |
Dave (1341) | ||
| 79969 | 2002-09-21 23:26:00 | Know what you mean. My thirst for knowledge has grown heaps since I’ve learned a little bit & you’ll surprise yourself at what you can do. Everyone at the office thinks I’m a genius at Excel now. If only they knew ;-) For your printing problem, the code works by doing little things (each line doing something). First you select something then you do something to that selection like copy or print etc. The line cells.select selects the whole sheet and the whole sheet was still selected when the line Selection.ClearFormats (or Selection.Interior.ColorIndex = xlNone) was processed. This is why it applied removing the formatting to everything in the sheet. To solve this you just enter a new line above the Selection.Interior.ColorIndex = xlNone line and enter a range that you want to have the colours/patterns removed. A range is selected as follows:- example Range(“a1”).select This selects just cell a1 Range(“b1:c3”).select ….This selects cells b1 to c3 Note the “ “ around the cell references. Remember that the next lines do something to that range so if you don’t want a particular area effected (ie leave formatting in place) then selecting everything but that range. If you want to remove one type of formatting then a different thing to another you would do something like this... Range("a1").select Selection.Interior.ColorIndex = xlNone Range("a2").select Selection.ClearFormats So this removes just colours from cell a1 (& leaves other formats like bordering alone) while cell a2 has all formats removed. Easy peasy aye! Any probs then post again. |
parry (27) | ||
| 79970 | 2002-09-22 08:35:00 | I recommend this (www.vbatutor.com) site for anyone wanting to know more about VBA. | antmannz (28) | ||
| 79971 | 2002-09-26 19:37:00 | Thankyou Antzzman for the link, it will be a big help to me. Parry - Thanks again for all your help, but can you tell me at all what this means, "runtime error 1004 - Unable to set the ColorIndex property of the Interior Class" I have been making some other changes to the sheet's and since then when I press the "Print" button (created using the Macro you supplied) I get this message, and it won't print. When I press debug it jumps into VBA and higlights "Selection.Interior.ColorIndex = xlNone" Can you tell me what I have done wrong. |
Dave (1341) | ||
| 79972 | 2002-09-26 22:31:00 | Try changing the line to read Selection.Interior.ColorIndex=xlColorIndexNone Note that if you have all of Excel's help files installed you can click on a word in the code (eg. ColorIndex) and press F1 for help. I found the VBA help files for Excel quite good. Also, as you type a line of code, the editor will often pop up with choices of the appropriate properties, etc for you to choose from. So if you were to type the line above rather than copy and paste it, when you type the "=" you would get a choice of "xlColorIndexNone" and "xlColorIndexDefault" or something similar. Makes coding way easier :) |
antmannz (28) | ||
| 79973 | 2002-09-26 23:53:00 | Sorry about the spelling of your name on my last post antmannz, I'll try to get it right this time :) I have changed the line to read as you have shown, but no change. This may mean something to you - If the sheet is not protected, it will print fine, but the minute I protect the sheet I get the error mesage. Thanks again Dave |
Dave (1341) | ||
| 79974 | 2002-09-27 02:04:00 | No prob re name spelling :) The sheet protection will indeed be causing the problem. Basically the macro is running a series of mouse-clicks and key presses and is running into the same constraints you or I would have if we tried to tinker with a protected sheet. Try inserting If ActiveSheet.Protection.Enabled Then ActiveSheet.Protection.Enabled=False End If at the start of the macro and ActiveSheet.Protection.Enabled=True at the end. This checks to see if the sheet is protected and will unprotect the sheet if it is, then turn the protection back on when finished. |
antmannz (28) | ||
| 1 2 | |||||