none
How to print based on Tab Color of Visible/Non-Hid Worksheets RRS feed

  • Question

  • I am trying to print certain worksheets based upon if visible and tab color. Currently, I am using the below code to display worksheets only if cell data is "Yes". I have input worksheets that format to how I want in my "output" worksheets. My input and output tab colors are different and I am wanting to print my "output" pages that have "blue" tab color. Is this possible?

    If [B1] = "Yes" Then
    Sheets("GL").Visible = True
    Else
    Sheets("GL").Visible = False
    End If
    If [B1] = "Yes" Then
    Sheets("GL Output").Visible = True
    Else
    Sheets("GL Output").Visible = False
    End If

    Thank you!

    Tuesday, October 17, 2017 3:38 PM

All replies

  • Record a macro where you apply the specific blue that you are using - to find out the color to check for - and use code like this, replacing xlThemeColorLight2  with the value from your recorded macro.

    Sub PrintBlueTabs()
        Dim Sh As Worksheet
        For Each Sh In Worksheets
            If Sh.Tab.ThemeColor = xlThemeColorLight2 Then
                Sh.PrintOut
            End If
        Next Sh
    End Sub

    Tuesday, October 17, 2017 3:46 PM
  • I am getting a syntax error on "Sub PrintBlueTabs()". I am also getting errors on every line afterwards saying "End of Statement". I do not know anything about VBA/Code to know what those errors mean.
    Tuesday, October 17, 2017 4:30 PM
  • Copy my code and paste it into a Notepad document, then re-copy it and paste it into a standard codemodule. You may be getting illegal characters in the code - depends on your browser and operating system.
    Tuesday, October 17, 2017 5:08 PM
  • Sadly It still did not work. I am running off Windows 10 with Excel 2013. Thank you for your help.
    Tuesday, October 17, 2017 6:05 PM
  • Here is a working example: press the buttons on the first sheet.

    https://1drv.ms/x/s!AsKdy7Nfg_Fbgma9GjiCdXdwxs4y

    Tuesday, October 17, 2017 7:01 PM
  • Okay, I got it to partially work but now I am running into an error that says Error '1004':

    PrintOut method of Worksheet class failed

    Tuesday, October 17, 2017 7:29 PM
  • Hi,

    This is the forum to discuss questions and feedback for Microsoft Excel features, I'll move your question to the MSDN forum for Excel

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.


    Regards,
    Emi Zhang
    TechNet Community Support

    Please remember to mark the replies as answers if they helped.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Wednesday, October 18, 2017 6:29 AM
  • I missed the hidden worksheets: change both of the conditionals to use And Sh.Visible, like

            If Sh.Tab.ThemeColor = xlThemeColorAccent6 Then

    becomes

            If Sh.Tab.ThemeColor = xlThemeColorAccent6 And Sh.Visible Then

    Wednesday, October 18, 2017 12:37 PM