Call Image Based on Chart Results 1

[shelby55]

Hello

I'm using Excel 2003.

I have a workbook with many worksheets:
- rawdata which is a query (using MSQuery) to get the data from an Access db
- summary sheet with a list of dates from raw data that the user selects. The list is data validation and a named range of mydates. The selected date is a named range of selected_date
- data for charts - the user is able to select a date from the summary and the charts represent the date provided plus 10 weeks previous to that. This worksheet is the data from raw data filtered to the selected_date showing actual value for the week, target and baseline
- charts - 10 in total which are line graphs

I want to be able to show an image representative of the last data point in the graphs. So if result is greater than the target then a green star, between baseline and target a yellow dolphin and less than target, a red shark. I want to put these 3 images in a worksheet and call them based on the results of each of the charts.

How do I do this? Thanks.

 

[SkipVought]

That data point is in your SOURCE DATA. That's were to test the value. Then it is merely a matter of making visible the proper shape based on that data.

Skip,

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

 

[shelby55]

Hi

Because there are 10 graphs you had suggested in another post that it was better to save the 3 images to a worksheet in the same workbook and "call them" based on the results of the current data point instead of having all 3 images in the worksheet and making them visible or not based on the results. However, I can't seem to get an answer anywhere on what the syntax would look like. I have syntax for the visible/invisible but need to know how to change it for this new method.

The code to identify the shapes is:

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

The code for the applicable shape per results and to make that shape visible is:

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

I just am not sure what to change....thanks.

 

[SkipVought]

So you have 3 shapes saved SOMEWHERE.

Sheets("SOMEWHERE").Shapes(TheSelectedShapeName).Copy
    With Charts(YourChart)
        .Paste
        With .Shapes(.Shapes.Count)
            .Top = 20
            .Left = 20
        End With
    End With

Skip,

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

 

[shelby55]

Hi

Thanks Skip....but what about the results? Do I incorporate that into the Select Case syntax?

Thanks.

 

[SkipVought]

Sub showshape(sShapeName)
    Sheets("SOMEWHERE").Shapes(sShapeName).Copy
    With Charts(YourChart)
        .Paste
        With .Shapes(.Shapes.Count)
            .Top = 20
            .Left = 20
        End With
    End With
   
end sub

Skip,

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

 

[shelby55]

Hi Skip

You just repeated what you told me before....where does this code reside in relation to my other code?

Thanks.

 

[shelby55]

HI Skip

I'm still not getting this. I replaced the previous showshape sub with the one you provided but I'm getting a compile error in the select statement at the last End With indicating "End With without With". But I didn't change that at all so not sure what's going on.

Can you please just explain what the new sub is doing and how does this (or does it) change the Select Case from the original.

Thanks.

 

[shelby55]

Hi

Also, in the ShowShape sub, you indicate that the code is With Charts("MyCharts"). Do I add all 10 in there or do I need to replicate this code for all 10 charts?

Thanks.

 

[shelby55]

Hi

Okay, I got it working but now I have the various images just piling on the page every time it's activated...how do I remove the images on deactivate?

Thanks.

 

[shelby55]

Hi

Okay still working away on this and I can remove the existing image prior to applying the new one by adding code before the new image:

 for each sp in activesheet.shapes
       sp.delete

But the problem is that I have a company logo which is being removed also. We solved in the original method of making pictures visible or not but how do I apply to this method so logo isn't deleted?

Thanks.

 

[PHV]

For Each sp In ActiveSheet.Shapes
If sp.Name <> "spGBGH" Then
sp.Delete
End If
Next

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[shelby55]

Hi

Thanks PHV. I tried that and the compiler doesn't like the "Next" in code section following the one you suggested and the error is"Next Without For":

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

The code without the addition you suggested worked fine so not sure why that "Next" is a problem now.

Thanks.

 

[PHV]

I tried that
Where ?
What is the relation between the code you've just posted and my suggestion ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[shelby55]

Hi

The code is in code section following the one you suggested. I actually just took out the Next that was a problem and now it's working fine. It was there previously from the hide/visible method that no longer applies.

Thanks PHV!

 

[shelby55]

Hi

When I try to close this database I'm prompted "do you want to save the changes you made to "my workbook name". I'm assuming this is because of the code adding images to the charts. Is there code that can be included so this prompt doesn't show up?

Thanks.

 

[PHV]

Something like this ?
ActiveWorkbook.Save
Application.Quit

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[shelby55]

Hi PHV

I can't believe this but I think I figured it out myself just now:

In the beforeclose event of the ThisWorkbook:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.DisplayAlerts = False
Workbooks("ED-PIP DART Prototype V3.2.xls").Close
Application.DisplayAlerts = True
End Sub

It seems to have done the trick...do you see any issues? Thanks!