PDA

View Full Version : Background printing in excel 97



Dave
16-09-2002, 04:08 PM
Hello,
Can anyone help me with this problem I am having in Excel 97. I have used the " fill Colour" button to highlight cells on the page. When I print of course, it prints with a shaded background, but I want to use the shading for on screen only and have the page print as normal (with no shading).
Is this possible and if so How?

Thanks in advance
Dave

godfather
16-09-2002, 04:13 PM
Done thousands of them...but never been able to de-select for printing. The work around was to use yellow shading, it does not show on a Black and White laser. If you have an inkjet, see if you can select B&W.

If you could set your shading as a background image it would work, as no background images are printed out of Excel. It would take days to align though!

parry
16-09-2002, 06:37 PM
Hi Dave, a #8 wire approach could be to create a macro that copied the contents to a new blank sheet (pasted as values only so no formatting is copied across), printed that sheet then deleted the sheet again after printing. You could amend the macro in such a way that the code applied to the active sheet so would work with any workbook.

Dave
17-09-2002, 07:26 AM
Thankyou Godfather for the help, do you know if any other colors show on screen, but will not print?

Also thankyou Parry, but unfortunately I have never tried using "Macro's", perhaps you could tell me of some sites that do tutorials?

Regards
Dave

godfather
17-09-2002, 08:06 AM
Only a pale yellow as far as I know, its not that it "doesnt print", its more that it is "seen" as white by the printer. You may also note that many companies use light yellow paper for their fax originals, and these photocopy as white OK as well.

parry
17-09-2002, 09:54 AM
Hi Dave, I found this site which has very basic info about recording macros -http://www.fgcu.edu/support/office2000/excel/customize.html.

How it works is that Excel records your keystrokes & mouse clicks. When saved, the macro is then played back for you.

The only problem with macros is that it uses absolute referencing for everything. For example if you recorded a macro by copying the contents of Sheet1 to Sheet2, you could not use the same recorded macro to copy the contents of Sheet3 to Sheet4 unless you edited the code.

Another thing to be wary of with macros is there is no undo function. Once it has run then thats it. However, while playing with macros to get it working how you want, first save the workbook so you could then exit and say no to Save workbook to be back to where you were.

Have a go, you will be surprised at how much time they can save you and you can do some clever things :-)

For your printing I would do the following with your key strokes/mouse clicks after you press the record button:-

1) Add a new sheet
2) Select the sheet that has formatting and select all
3) Click the copy button
4) Select the newly created sheet and select cell a1
5) Select Edit-Paste Special then the formats option & click OK
6) Select the Sheet that has formatting again and select all
7) Select Edit-Clear-Formats
8) Print the page (it will have no formatting at the moment)
9) Select the new sheet and select all
10) Click the copy button
11) Select the Sheet you printed (cell a1) then Edit-Paste Special-Formats
12) Delete the newly created sheet

The macro will work correctly as is provided you are running it on on the current sheet & worekbook and if you dont rename or add new sheets.

I could create some code for you if you want but it would be a good intro for you to see what macros can do yourself.

parry
17-09-2002, 10:52 AM
Hi Dave, on second thoughts this is probably a bit complicated for your first go at recording a macro since there are quite a few keystrokes. I still recommend having a play yourself on a new workbook with something simple.

I have written some code that will remove formatting from the current sheet then replace the formatting after printing. For the print I have just left the default options so there isnt an option to only print page 1 of 10 for example or change the print area. Set up the print area before running the macro.

Create a new workbook with formatting and paste the code into it to make sure its working to your satisfaction. If Ok then follow the instructions again to paste into your proper workbook.

Inserting Code
1) Select Tools-Macro-Visual Basic Editor
2) From the Menu select Insert-Module
3) A page will appear on the right hand frame with the words "Option Explicit"
4) Under the words "Option Explicit" paste the code below (from line Sub PrintNoFormat() downwards) into the page
5) From the Menu select File-Save
6) From the Menu select File-Close & Return to Microsoft Excel

To run the macro, select the sheet you want to print then from the Menu select Tools-Macro-Macros and select the PrintNoFormat macro and click the run button.

enjoy :-)
-----------------------------------------

Sub PrintNoFormat()
'Start this macro in the sheet you want printed. It will remove formatting
'then print the sheet then put back the formatting

Dim PrintSheet As String
Dim NewSheet As String
Application.ScreenUpdating = False
PrintSheet = Application.ActiveSheet.Name
Cells.Select
Selection.Copy
Sheets.Add
NewSheet = Application.ActiveSheet.Name
Range("A1").Select
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets(PrintSheet).Select
Application.CutCopyMode = False
Selection.ClearFormats
Range("A1").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Sheets(NewSheet).Select
Selection.Copy
Sheets(PrintSheet).Select
Cells.Select
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets(NewSheet).Select
Application.CutCopyMode = False
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Sheets(PrintSheet).Select
Range("a1").Select
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Dave
17-09-2002, 04:33 PM
Thankyou both very much for all the help, Parry thankyou for the instructions for the Macro for my specific situation, I will certainly give it a go.

Thanks again
Dave

parry
17-09-2002, 06:20 PM
My pleasure. Let me know the code works OK.

Dave
21-09-2002, 02:36 PM
Yes Parry, it worked just fine. Thankyou again. The only problem I had with it was that it also removed all the border frames from around sections of the sheet.
When I get time I will have to learn about Macro's and VB script.

Thanks Again
Regards
Dave

parry
21-09-2002, 05:09 PM
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 :-)

Dave
22-09-2002, 09:20 AM
Thanks again for your help Parry.

Could you perhaps recommend a Beginners Book on VBA, and I do mean an absolute beginner.

Regards
Dave

parry
22-09-2002, 09:41 AM
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.

Dave
22-09-2002, 09:43 AM
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

parry
22-09-2002, 10:26 AM
Know what you mean. My thirst for knowledge has grown heaps since Ive learned a little bit & youll surprise yourself at what you can do. Everyone at the office thinks Im 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 dont 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.

antmannz
22-09-2002, 07:35 PM
I recommend this (http://www.vbatutor.com/vbatutor.htm) site for anyone wanting to know more about VBA.

Dave
27-09-2002, 06:37 AM
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.

antmannz
27-09-2002, 09:31 AM
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 :)

Dave
27-09-2002, 10:53 AM
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

antmannz
27-09-2002, 01:04 PM
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.