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
79955	2002-09-16 05:08:00	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	Dave (1341)
79956	2002-09-16 05:13:00	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!	godfather (25)
79957	2002-09-16 07:37:00	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.	parry (27)
79958	2002-09-16 20:26:00	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	Dave (1341)
79959	2002-09-16 21:06:00	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.	godfather (25)
79960	2002-09-16 22:54:00	Hi Dave, I found this site which has very basic info about recording macros -www.fgcu.edu 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 (27)
79961	2002-09-16 23:52:00	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	parry (27)
79962	2002-09-17 05:33:00	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	Dave (1341)
79963	2002-09-17 07:20:00	My pleasure. Let me know the code works OK.	parry (27)
79964	2002-09-21 03:36:00	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	Dave (1341)
1 2