Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    New Lounger
    Join Date
    Jan 2015
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Remove shading just for printing only

    Hi to all,

    I am trying to find a way to take a spreadsheet and through VBA, when ready to print the sheet out I want all of the cells with Shading to simply print out without the shading, but retains the shading in the worksheets in the end. I had rigged up a way to make it remove the shading with the macro, and make it print without the shading, but afterwards the shading was removed from the worksheet. Simply I have it to select the entire worksheet, then go to Home and in the Fills box use the "no fills" selection, then use the print command and after click "undo" to return the sheet to it's prior shaded state. Only, the macro would not use the "undo" action. I am no Excel guru so I am needing help in making this work. And I forgot one thing, I wanted this to be activated with a Command Button to be used when ready to print, which I had used one already.

    Thanks in advance for any assistance,
    BudE

  2. #2
    Lounger
    Join Date
    Feb 2011
    Posts
    28
    Thanks
    0
    Thanked 10 Times in 8 Posts
    One method would be to create a copy of the sheet you want to print, remove the shading from that sheet and print it, then delete the unwanted sheet. Substitute Sheet1 with the name of the sheet you're working on in the following:

    Code:
    Sub PrintWithoutFormats()
        Sheets("Sheet1").Select
        Sheets("Sheet1").Copy Before:=Sheets(1)
        Sheets("Sheet1 (2)").Select
        Cells.Select
        Selection.ClearFormats
        ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
            IgnorePrintAreas:=False
        ActiveWindow.SelectedSheets.Delete
    End Sub
    You then have to click on Delete to remove the unwanted sheets, but someone may know a way to autoconfirm the deletion which would make it smoother.

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts
    Hi BudE

    ..welcome to the lounge.

    Try this:
    Code:
    Private Sub Workbook_BeforePrint(Cancel As Boolean)
    ActiveSheet.PageSetup.BlackAndWhite = True
    End Sub
    No need to have a command button.

    zeddy

  4. #4
    New Lounger
    Join Date
    Jan 2015
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by unclehewie View Post
    One method would be to create a copy of the sheet you want to print, remove the shading from that sheet and print it, then delete the unwanted sheet. Substitute Sheet1 with the name of the sheet you're working on in the following:

    Code:
    Sub PrintWithoutFormats()
        Sheets("Sheet1").Select
        Sheets("Sheet1").Copy Before:=Sheets(1)
        Sheets("Sheet1 (2)").Select
        Cells.Select
        Selection.ClearFormats
        ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
            IgnorePrintAreas:=False
        ActiveWindow.SelectedSheets.Delete
    End Sub
    You then have to click on Delete to remove the unwanted sheets, but someone may know a way to autoconfirm the deletion which would make it smoother.

    Hi again and thank you for your help. I placed this code in a new worksheet, on sheet1 and the code hung and stated: "IgnorePrintAreas:=False " Not sure what that is but it didn't let the code work.

  5. #5
    New Lounger
    Join Date
    Jan 2015
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by zeddy View Post
    Hi BudE

    ..welcome to the lounge.

    Try this:
    Code:
    Private Sub Workbook_BeforePrint(Cancel As Boolean)
    ActiveSheet.PageSetup.BlackAndWhite = True
    End Sub
    No need to have a command button.

    zeddy

    And thank you too zeddy. I placed the code in the VBE but not sure what to do to make it work.How do I use this code? How to activate it or whatever?

    thanks

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts
    Hi BudE

    The code must be placed in the ThisWorkbook section, not in a module.
    In the VBE, display the Project Explorer ( [Ctrl][R] ) in the left-hand pane of the VBE.
    Double-click the ThisWorkbook , then in the right-pane, paste the routine.

    This code is an event-driven code. As the routine name suggests, whenever you do a print (or print-preview), this code will be automatically executed. So you don't need a button.

    zeddy

  7. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 Posts
    As an alternate to Zeddys code, you could use unclehewie" code with a few modifications, You do not want to remove all formatting such as bold. As I understand it that you want to remove just the row shading. I made those adjustments along with inhibiting any alerts when deleting the sheet. It is also generic in that you do not have to worry about sheet names

    Place in a standard module
    Code:
    Sub PrintWithoutFormats()
        Application.ScreenUpdating = False
        ActiveSheet.Copy Before:=Sheets(1)
        Cells.Interior.Pattern = xlNone
        ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
            IgnorePrintAreas:=False
        Application.DisplayAlerts = False
            ActiveWindow.SelectedSheets.Delete
        Application.DisplayAlerts = False
        Application.ScreenUpdating = True
    End Sub
    I would use Zeddy's because it is the easiest approach
    Attached Files Attached Files
    Last edited by Maudibe; 2015-01-30 at 09:32.

  8. #8
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    Is this ROW shading? If so, how did it get there? If conditional formatting just record a macro applying the CF and removing the CF. Now, change it to remove the CF>print>replace CF.
    Don Guillett
    Excel Developer
    dguillett @gmail.com

  9. #9
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 Posts
    Zeddy's snippet is about as easy as they come and will handle conditional formatting as far as cell fill color. However, it will change font color or color images if applicable which may not be desirable. If such the case, then unclehewie's approach might prove worthy.

  10. #10
    New Lounger
    Join Date
    Jan 2015
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Cool

    Quote Originally Posted by Maudibe View Post
    Zeddy's snippet is about as easy as they come and will handle conditional formatting as far as cell fill color. However, it will change font color or color images if applicable which may not be desirable. If such the case, then unclehewie's approach might prove worthy.


    Wow, just like magic. I used Zeddy's version and it worked like magic. Thank you Zeddy and everyone that offered help on this. My head is so sore from me banging it on the desk trying to find a solution. And yes, just the In Cell Shading is what I was after. They send the work in from outside and for some reason add shading...which might work for them. However, when they fax it in to me it comes out as a Black area and not able to read. Countless efforts have been made to get them to not do that. And to think you did a very simple small bit of code to work the magic. Now to get all of the station Sups to put this in their worksheets.

    Have a terrific day and thanks a million.
    This site "ROCKS"
    Bud

  11. #11
    New Lounger
    Join Date
    Jan 2015
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Maudibe View Post
    Zeddy's snippet is about as easy as they come and will handle conditional formatting as far as cell fill color. However, it will change font color or color images if applicable which may not be desirable. If such the case, then unclehewie's approach might prove worthy.
    Hey, back one more time. Something I didn't think of when doing this. The code works fine and does just what it should, but the lack of thought on my part was, when they need for the shading to print out for their own personal reasons, whatever they might be, how could it be set to print with the shading? That was my over looking things. So to print out to fax to me, removing the shading works, but to allow them to print the shading for themselves...maybe for some kind of reports or something what could I do to allow that just for them?

    BudE

  12. #12
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    Put is a checkbox and if checked apply the macro, if not, don't
    Don Guillett
    Excel Developer
    dguillett @gmail.com

  13. #13
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post
    Quote Originally Posted by BudE View Post
    Hi to all,

    .. and after click "undo" to return the sheet to it's prior shaded state. Only, the macro would not use the "undo" action. ...

    BudE
    Hi BudE

    How about saving the worksheet before printing. Remove the shading and then print and close without saving.

    This locks in all the changes plus the shading. You can use something like:

    ThisWorkbook.Close savechanges:=False

    And this will close the workbook without saving it thus preserving the state it was before the printing.

    Please let us know if this solved your issue.

    Wassim
    Last edited by Wassim; 2015-02-02 at 12:25.
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  14. #14
    New Lounger
    Join Date
    Jan 2015
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi there. If you notice what Zeddy offered, that is what I used and that part works great. Only thing was at other times I want the shading to actually print for the originator when they need it to, for presentation purposes or whatever they do with it. What it is actually is they print out these worksheets and then scan and fax to me and others. Only, when you have the shading it would come out black when we received it. Only needed for it to send it to us without the shading. But I left out the part I still want them (originator or sender) to be able to print out the shading on their end when they want to. That was the part I left out originally when I posted here and is the last bit I need to figure out. Oh, and I'm not sure if I want the worksheet to close after they print it out to send to us. I'm trying not to disturb how they work in their offices so as to not cause any friction. Any way to make it work both ways?

    Thanks,
    Bud

  15. #15
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts
    Hi Bud

    Change my original to this:
    Code:
    Private Sub Workbook_BeforePrint(Cancel As Boolean)
    saywhat = "Print for fax (no shading)?"
    btns = vbYesNo + vbDefaultButton2 + vbQuestion
    boxtitle = "Print setup"
    answer = MsgBox(saywhat, btns, boxtitle)
    If answer = vbYes Then
    ActiveSheet.PageSetup.BlackAndWhite = True
    Else
    ActiveSheet.PageSetup.BlackAndWhite = False
    End If
    End Sub
    zeddy

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •