?
Solved

Excel VBA - Load Array if Charts have a Value

Posted on 2011-04-28
14
Medium Priority
?
448 Views
Last Modified: 2012-05-11
Experts,

Please see that attached workbook. I am using an Array to load the CodeNames of sheets I want to print. However, there are 5 sheets (ChartAbby.Name, ChartBobby.Name, ChartChuck.Name, ChartDebbie.Name and ChartEric.Name) which may or may not contain any data. They are pivot charts based on Sheet(TEAM), Pivot Tables 1 to 5.

What I am looking to do is add these 5 Chart Sheets to the array, ONLY IF they have DATA. In my example, ChartChuck.Name and ChartEric.Name should not be included, because they contain no data.

Thank you,
Kevin
Option Explicit
Sub PrintReport()

Dim PT As PivotTable
Dim WS As Worksheet

Application.ScreenUpdating = False

    'Refresh All Pivots
    For Each WS In ThisWorkbook.Worksheets
        For Each PT In WS.PivotTables
          PT.RefreshTable
        Next PT
    Next WS

    'Load Worksheets Into Array for Print Preview
    Sheets(Array(ChartAbby.Name, ChartBobby.Name, ChartChuck.Name, ChartConType.Name, ChartDebbie.Name, ChartEric.Name, ChartGoalAnnual.Name, ChartGoalMonth.Name, ChartSponsor.Name, SummaryByProc.Name, SummaryContType.Name, SummaryGoal.Name, SummarySponsor.Name, SummaryTeam.Name)).Select
    ActiveWindow.SelectedSheets.PrintPreview
    'I only want to load into the array: _
    ChartAbby.Name, ChartBobby.Name, ChartChuck.Name, ChartDebbie.Name and ChartEric.Name _
    If they contain a value _
    In this example, Chuck and Eric are blank - so they should not go to be printed.

Application.ScreenUpdating = True

End Sub

Open in new window

EE---TEMPLATE---version-1-6.xls
0
Comment
Question by:redrumkev
  • 7
  • 7
14 Comments
 
LVL 42

Expert Comment

by:dlmille
ID: 35488560
For this solution, I'm using a very good reference that I like to interrogate Chart Series http://j-walk.com/ss/excel/tips/tip83.htm

In a loop of possible charts to plot, I check for values in each chart's seriescollections and there are values then that chart gets added to the array.

Here's my code embedded in yours:
 
Option Explicit
Const strChartPrintCandidates = "AbbyBobbyChuckDebbieEric"
Sub PrintReport()

Dim PT As PivotTable
Dim WS As Worksheet
Dim myChart As Chart, chartsToPrint() As String, i As Integer

Application.ScreenUpdating = False

    'Refresh All Pivots
    For Each WS In ThisWorkbook.Worksheets
        For Each PT In WS.PivotTables
          PT.RefreshTable
        Next PT
    Next WS

    'Load Worksheets Into Array for Print Preview
    
    'ignore empty chart series
    i = 0
    For Each myChart In Application.Charts
           
        If InStr(strChartPrintCandidates, myChart.Name) Then 'first, see if its a chart we want to print
            
            If checkValues(myChart) Then
                ReDim Preserve chartsToPrint(i) As String
                chartsToPrint(i) = myChart.Name
                i = i + 1
            End If
        End If

    Next myChart
    
    'Sheets(Array(ChartAbby.Name, ChartBobby.Name, ChartChuck.Name, ChartConType.Name, ChartDebbie.Name, ChartEric.Name, ChartGoalAnnual.Name, ChartGoalMonth.Name, ChartSponsor.Name, SummaryByProc.Name, SummaryContType.Name, SummaryGoal.Name, SummarySponsor.Name, SummaryTeam.Name)).Select
    
    Sheets(chartsToPrint).PrintPreview
    
    'ActiveWindow.SelectedSheets.PrintPreview
    'I only want to load into the array: _
    ChartAbby.Name, ChartBobby.Name, ChartChuck.Name, ChartDebbie.Name and ChartEric.Name _
    If they contain a value _
    In this example, Chuck and Eric are blank - so they should not go to be printed.

Application.ScreenUpdating = True

End Sub
Function checkValues(myCht As Chart) As Boolean
Dim MySeries As New ChartSeries
Dim numRows As Long
Dim yVals As Variant, i As Integer, j As Integer
Dim mySer As Object

    With MySeries
    
        .Chart = myCht

        i = 1
        For Each mySer In myCht.SeriesCollection 'look at all collections to see if there's any data
            
            .ChartSeries = i

            On Error Resume Next
            
            Set yVals = .Values
            For j = LBound(yVals.Value2) To UBound(yVals.Value2) 'search for data
                If Not IsEmpty(yVals(j, 1)) Then
                    checkValues = True
                End If
            Next j

            On Error GoTo 0
            i = i + 1
        Next mySer

    End With
    
End Function

Open in new window


I'll spare the readers on the ChartSeries class module (see link above, if interested) that is also attached to your VBA Project Explorer.  Its in the attached file.

Cheers,

Dave
EE---TEMPLATE---version-1-6-r1.xls
0
 
LVL 23

Author Comment

by:redrumkev
ID: 35490823
Dave,

Thank you for your reply, I like the idea of this!

I am getting a compile error on the Function and not sure why?

Also, after your logic runs, I see that it should identify the "non-zero" charts and load them into the array. What would you recommend that I do to add the "static" worksheets to the array?

These are:
ChartConType.Name, ChartGoalAnnual.Name, ChartGoalMonth.Name, ChartSponsor.Name, SummaryByProc.Name, SummaryContType.Name, SummaryGoal.Name, SummarySponsor.Name, SummaryTeam.Name

Would that be something such as "add 9 to the array and loop through these names until they are all added", doing so after the "non-zero" charts have been added first?

Thank you!
Kevin

 
0
 
LVL 23

Author Comment

by:redrumkev
ID: 35490832
See attached - the version with your code pasted as module 2:


EE---TEMPLATE---version-1-6-2.xls
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 42

Expert Comment

by:dlmille
ID: 35492438
You need to drag the ChartSeries class module from my post into your workbook.

See the attached file.

I'm now looking at your other questions with 1-6-2.

Dave
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35492490
do you want ALL charts that have data to be in the print preview?  Just comment out the if /end if statement in the loop:


 
For Each myChart In Application.Charts
           
        'If InStr(strChartPrintCandidates, myChart.Name) Then 'first, see if its a chart we want to print
            
            If checkValues(myChart) Then
                ReDim Preserve chartsToPrint(i) As String
                chartsToPrint(i) = myChart.Name
                i = i + 1
            End If
        'End If

    Next myChart

Open in new window



Dave
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35492797
You either need to 1) create an array of sheets to print, or store that in a worksheet tab to inform the app that these sheets are print candidates, OR 2) test each sheet to see if it has a print_area range defined.

I chose the second option.

This tests each worksheet, if its a chart - then tests for data, otherwise, if the sheet is visible and has a print_area defined, then its included into the tabsToPrint() string array for printing.

Here's the code and use attached (remember to copy the ChartSeries class module over, when you move to your final copy!)
Sub PrintReport()

Dim PT As PivotTable
Dim WS As Worksheet
Dim mySheet As Variant, myChart As Chart, tabsToPrint() As String, i As Integer, printTab As Boolean
Dim testPrintRange As Range

Application.ScreenUpdating = False

    'Refresh All Pivots
    For Each WS In ThisWorkbook.Worksheets
        For Each PT In WS.PivotTables
          PT.RefreshTable
        Next PT
    Next WS

    'Load Worksheets Into Array for Print Preview
    
    'ignore empty chart series
    i = 0
    For Each mySheet In Application.Sheets 'not Application.Worksheets - this will cover worksheets & chartsheets

        printTab = True

        If TypeName(mySheet) = "Chart" Then
            Set myChart = mySheet
            If Not checkValues(myChart) Then
                printTab = False
            End If
        ElseIf mySheet.Visible Then
            On Error Resume Next
                testPrintRange = mySheet.Range("Print_Area") 'test to see if sheet tab has a print range, otherwise, don't print it
                If Err.Number <> 0 Then printTab = False
                Err.Clear
            On Error GoTo 0
        End If
        
        If printTab And mySheet.Visible = True Then
            ReDim Preserve tabsToPrint(i) As String
            tabsToPrint(i) = mySheet.Name
            i = i + 1
        End If
    
    Next mySheet
    
    'Sheets(Array(ChartAbby.Name, ChartBobby.Name, ChartChuck.Name, ChartConType.Name, ChartDebbie.Name, ChartEric.Name, ChartGoalAnnual.Name, ChartGoalMonth.Name, ChartSponsor.Name, SummaryByProc.Name, SummaryContType.Name, SummaryGoal.Name, SummarySponsor.Name, SummaryTeam.Name)).Select

    Sheets(tabsToPrint).PrintPreview
    
    'ActiveWindow.SelectedSheets.PrintPreview
    'I only want to load into the array: _
    ChartAbby.Name, ChartBobby.Name, ChartChuck.Name, ChartDebbie.Name and ChartEric.Name _
    If they contain a value _
    In this example, Chuck and Eric are blank - so they should not go to be printed.

Application.ScreenUpdating = True

End Sub

Open in new window

See attached,

Enjoy!

Dave
EE---TEMPLATE---version-1-6-2-r1.xls
0
 
LVL 23

Author Comment

by:redrumkev
ID: 35493212
Dave,

Ok - you got it right on the last one 1-6-2-r1. The only thing is that I also want some of the non-chart pages to print as well.

I am not just going to use the print preview, instead I am going to pass these pages to the acrobat printer so that on execution of the macro, the non-blank charts are "(dynamically) activated" (which you did above) plus the other "static" worksheets I want (see below). All of these go to the PDF routine and I am left with an PDF output (which I have working already) in a folder somewhere.

My ultimate goal being: once a week/month, I update the data, execute the macro and then the PDF appears in the correct output folder.

So - for 1-6-3 how do I add (ALWAYS) the worksheets with yellow tabs, which are:
TEAM
GOAL
BY PROC
CONT TYPE
SPONSOR

Could we just add the above 5 sheets (first and always, regardless of their content) to the array to begin with, so from 0 to 4 (0, 1, 2, 3 and 4) and then start your i numbering for the:
If printTab And mySheet.Visible = True Then
            ReDim Preserve tabsToPrint(i) As String
            tabsToPrint(i) = mySheet.Name
            i = i + 1
        End If

Open in new window

with I starting at 5 (for 5, 6, ... n)?

Right now I have 7 on the print preview from your 1-6-2-r1 code. I would want this to be 12 pages in total, with those "static 5 (yellow tabs)" as the first pages and the dynamic "non-blank charts" as 6 through max(n) within the array.

Attached is the sheets I would want to be printed (and in the correct order, which is sheet location, left to right) with the charts filled as they are now!

I must say, this is some amazing help - A++ all the way!

Thank you,
Kevin
EE---TEMPLATE---version-1-6-3-GO.pdf
0
 
LVL 42

Accepted Solution

by:
dlmille earned 2000 total points
ID: 35493271
OUCH.  I had tested this and somehow a statement got messed up.

The attached provides the 12 pages, using Print_Area as the screening mechanism for whether a tab prints or not.

Check this out, and if you want to go further and specify which tabs should be printed, you can create a range name with those sheets in it and process that way as well.

Dave
EE---TEMPLATE---version-1-6-2-r2.xls
0
 
LVL 23

Author Comment

by:redrumkev
ID: 35493376
Dave,

I want to really THANK you for you time, you have been a big help! I also use to just ask questions on here as well, then I started answering and it gets kind of addicting, doesn't it? I might just hit the 1 million mark in the next week or so!

1-6-2-r2 seems to be the ticket. I might explore the range name idea, only reason is that if someone else comes along and does a Print_Area on sheets DATA or AGENT NAME (which should NEVER be printed) that might mess things up?! But that is also me worrying about problems down the road, right?

So for now, I am going to close this out and award you points and an A+. If I run into an issue with the range names, I will post a link back here to the new question (I have more than exhausted the 500 points on this question) so to go any further would be asking way too much.

Thanks for being one of the good guys out there!!!
Kevin
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35493547
Congratulations on your near-million mark.  I just turned "Sage".  Yes, its addicting.  I've been a member since 2004 asking questions about systems integration, etc., but after I asked my first Excel question (last December) I decided "I can do this" and have been contributing ever since - I try to do a couple a day for fun and to "stay sharp".  I've also started writing articles - I like to work on "tough stuff" especially if its esoteric, re: with pivot tables, charts, and active-x controls, though I help folks "get out the wash" as well...

Yes, you'll worry about the print_area issue, as I have.  I just had an idea.  You could create a named range called "No_Print" scoped to each sheet and then in the loop test for that - that could be your ticket and save you having to create a list of sheet names you'd have to maintain.

Here's that solution.

Dave
EE---TEMPLATE---version-1-6-2-r3.xls
0
 
LVL 23

Author Comment

by:redrumkev
ID: 35506808
Dave,

I had an idea over the weekend, but not sure of how to implement it. See attached 1-6-3, where I updated the (codename) of worksheets DATA and AGENT NAMES to be INPUTDATA and AGENTNAMES.

My thought was that within the if statements, what if we condition it so that whenever INPUTDATA.Name or AGENTNAMES.Name is called, they automatically will be coded to False, thus they do not pass your logic of:

        If printTab And mySheet.Visible = True Then
            ReDim Preserve tabsToPrint(i) As String
            tabsToPrint(i) = mySheet.Name
            i = i + 1
        End If

Open in new window


I tired placing it in as follows:

ElseIf mySheet.Visible Then
            On Error Resume Next
                Set testPrint = mySheet.Range("No_Print") 'test to see if sheet tab has a print range, otherwise, don't print it
                    If mySheet = AGENTNAMES.Name Or INPUTDATA.Name Then printTab = False 'Automatically removes AGENTNAMES.Name or INPUTDATA.Namefrom consideration
                If Err.Number = 0 Then printTab = False
                Err.Clear
            On Error GoTo 0
        End If

Open in new window


However this causes all my static worksheets (the yellow tab colored ones) not to print.

My thought on this was two possible benefits. One, by using the (Name) property for the sheet, if a user changes DATA to OTHERDATA, the code still runs. Second, if the user adds a print area to either INPUTDATA or AGENTNAMES, they still will not print when the sub is executed.

Kevin
EE---TEMPLATE---version-1-6-3.xls
0
 
LVL 23

Author Comment

by:redrumkev
ID: 35506833
That file didn't have my If statement line... see attached 1-6-4

Kevin
EE---TEMPLATE---version-1-6-4.xls
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35506853
The test for Print_Area is not in my last post - instead it just checks the range name No_Print.  So just put that in every sheet you don't want to print and you're all good...


Your if statement on agentnames.name should read:

change:
If mySheet = AGENTNAMES.Name Or INPUTDATA.Name Then printTab = False
to:
if mySheet = AGENTNAMES.NAME or mySheet = INPUTDATA.Name Then printTab = False
 
Dave
0
 
LVL 23

Author Comment

by:redrumkev
ID: 35507146
Ok - thank you, I forgot about the No_Print - so I will just use that!!!

Kevin
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question