• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 453
  • Last Modified:

Excel VBA - Load Array if Charts have a Value

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
redrumkev
Asked:
redrumkev
  • 7
  • 7
1 Solution
 
dlmilleCommented:
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
 
redrumkevAuthor Commented:
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
 
redrumkevAuthor Commented:
See attached - the version with your code pasted as module 2:


EE---TEMPLATE---version-1-6-2.xls
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
dlmilleCommented:
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
 
dlmilleCommented:
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
 
dlmilleCommented:
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
 
redrumkevAuthor Commented:
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
 
dlmilleCommented:
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
 
redrumkevAuthor Commented:
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
 
dlmilleCommented:
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
 
redrumkevAuthor Commented:
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
 
redrumkevAuthor Commented:
That file didn't have my If statement line... see attached 1-6-4

Kevin
EE---TEMPLATE---version-1-6-4.xls
0
 
dlmilleCommented:
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
 
redrumkevAuthor Commented:
Ok - thank you, I forgot about the No_Print - so I will just use that!!!

Kevin
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

  • 7
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now