Images per Graph or Call Each Per Graph

[shelby55]

Hi

I am using Excel 2003.

Thanks to Skip and PHV I was able to create a graph that has images to represent the results of the last period of data on the graph. It was done by having all 3 images on the graph and the property of visible set to true or false depending on the results.

This is working great but I've been asked to add 10 more graphs and I'm worried that by adding these images to each graph (so 30 more images) it will inflate the size of the workbook. Is there anyway to have once in the document and call on the image based on results?

I should note that these images aren't standard Excel ones. Thanks.

 

[SkipVought]

Hi,

I produce HUNDERDS of charts, using only ONE CHART! After all, unless the requirement is if VIEW multiple charts on the same sheet, you usually view ONE chart at a time.

In most cases the only thing that changes is the data. The chart format is identical. This may also mean that Titles may change as well, but it's much easier, IMHO, to manage ONE chart pointing it to the data required.

It is even possible to significantly or completely change the look of th chart as well; meaning the chart type, and other formatting.

Depending on the requirements, you may have one or more simple controls, like a combobox.

That is what I would suggest.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi Skip

Good suggestion...but how would I got about doing this? The current graph is based on named ranges.

Thanks.

 

[SkipVought]

How would the new chart be different?

Please explain your data structure, what data is currently plotted on your chart and what other data would be plotted on the 'new' chart.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi Skip

The new chart would be different in that there would be entirely different named ranges for the specific graphs. The only thing that would stay the same is the x axis which is based on a named range for the

In this workbook the user selects the end date which is what is the source for the graph's data. That's why it's named ranges, because the data is dynamic.

Does it still sound like something that could be done as one chart?

 

[SkipVought]

So please pick two different named ranges and describe how the user choice defines either one or the other.

Do each different named range define a different KIND of data or just a different range within the same; ie one named range defines patient average days and another named range defines cost of service delivery?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi Skip

The user doesn't do anything different right now for the data that will be produced by the graphs except for the date of interest (which is shown on the graphs as the last full week of data).

Other than that the data per graph will be named ranges of completely different data.

For instance, the first graph shows the baseline, target and % cases witin the target length of stay for admitted patients.

Another graph would show the baseline, target and % triage level 4 and 5 cases within 4 hours length of stay. The target and baseline would be different for this metric than for the indicator above.

 

[shelby55]

Hi Skip

I know you said you would go the route of one graph for all scenarios but if that proves too difficult due to the data being graphed, is it possible to have the images only appear once in the Workbook and show up as called based on data results?

Thanks.

 

[SkipVought]

So you could do sloething like this...

Dim sValRange as String

Select Case [some value]
  Case "value 1"
     'assign Named Range A
     sValRange = "Range A"
  Case "value 2"
     'assign Named Range B
     sValRange = "Range B"
End Select

YourChart.SeriesCollection(1).Values = "=SheetX!" & sValRange

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

"sloething"

Translation...

something

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Do each of the dozen charts have the same number of series?

Here's the down side for one chart. You have to do some detail analysis and coding to control the display of 12 or so different plots.

Here's the down side for multiple charts. Rather than having to maintain the look and feel of one chart, if anything changes (and hoe often does THAT happen?), you often have to go into each chart and tweek the code for each one.

I'd MUCH rather the former than the latter, having had to endure both circumstances in the past.

But is is not a hard & fast rule.

If you were to maintain 12 charts, I'd have a set of images stored. Copy then to the chart on the Chart_Activate event and delete them on the Chart_Deactivate event. That would limit the number of images in the workbook at any time to TWO sets of images.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi Skip

I apologize but I'm just seeing this response from you...very informative, thanks.

So how do I "store" the images...would they be within another sheet within the workbook? Or images within the folder where the Excel sheet is?

Thanks very much.

 

[SkipVought]

Whichever you prefer. I was implying in the workbook.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi Skip

Okay I'm revisiting this again. I have all the graphs built now I just need to call the image based on certain results.

For the original version of this where I was making the image visible or not based on results the code was:

Sub showshape(sName As String)
Dim sp As Shape

Application.ScreenUpdating = False
For Each sp In ActiveSheet.Shapes
With sp
    Select Case .Name
        Case sName, "spGBGH"
            sp.Visible = True
        Case Else
            sp.Visible = False
    End Select
    End With
    Next
    Application.ScreenUpdating = True
End Sub


Sub Chart_Activate()

    Dim BaseLine, Tgt, Actual
    Dim lngRowNum As Long
    Dim sp As Shape
        
    With Sheets("Graph_Data")
    'this is because if not a full week for most current date
    'then data and date will be in B28 not B29
    
        If IsDate(.Cells(29, 2).Value) Then
        lngRowNum = 29
        Else
        lngRowNum = 28
        End If
 
       Actual = .Cells(lngRowNum, 24).Value
       BaseLine = .Cells(lngRowNum, 25).Value
       Tgt = .Cells(lngRowNum, 26).Value
    End With

'if actual>=target then green star,
'if actual <target but >=baseline then yellow dolphin
'if actual <baseline then red shark
        Application.ScreenUpdating = False
        For Each sp In ActiveSheet.Shapes
        With sp
       
            Select Case Actual
                Case Is < BaseLine  'RED shark
                    showshape "spShark"
                Case Is >= Tgt      'GREEN star
                    showshape "spStar"
                Case Else          'YELLOW dolphin
                    showshape "spDolphin"
            End Select
        End With
        Next
        Application.ScreenUpdating = True
        
End Sub

So can you start me on what syntax to use in the chart_activate event? Thanks.

 

[shelby55]

Hi

Another question: would the Alert method (using Choose and he named ranges plus criteria) be efficient for this or is VBA better?

Thanks.

 

[SkipVought]

you'll have to refresh us, as there could be more than me responding.

Are you stuck on something in particular?

What happens if you put a break in the Chart_Activate procedure and STEP thru the statements?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi

I tried the Alert method but it's not recognizing the "camera" object criteria for changing symbols.

What I'm stuck on is that the code that you helped me develop previously was for the shape visible yes/no but now I am required to add 10 charts and the worksheet will be bloated with that 3 shapes on each chart. I want to have the 3 shapes be on one worksheet and "called" to the specific chart worksheet based on results. So not sure what properties I need to work with for this though I know the event is correct (chart activate).

Thanks.

 

[SkipVought]

hmmmm. I am not familiar with the alert method. Alert method related to what object? There was nothing in your posted code that gave a hint.

And you mentitioned the Camera feature which does not change anything to my knowledge. Rather it makes a PICTURE of a range. When changes occur in the range, it is reflected in the PICTURE: hence the Camera.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi Skip

I'm not going to use the alert method now because the picture (from the camera) can't be updated on a page with an embedded chart...can you help with the other method which is to only have one image that is called per chart worksheet depending on the results of the last data point.

The current code I have is for the visible property but that won't apply anymore, so what property do I use? Thanks.