Different Autoshape per Results 2

[shelby55]

Hello

I am using Excel 2003 and would like to add an autoshape to my worksheet for users to synopsize the worksheet results.

The shapes will be red octagon, yellow triangle and green circle and all will have text in them.

Thanks to Skip and PHV I have the following code for use with a graph in a different workbook...how do I edit for use with multiple autoshape types (this one has only faces with different traits)?

Sub Chart_Activate()
Dim oSmiley As Shape

    Dim BaseLine, Tgt, Actual
    Dim lngRowNum As Long
        
    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
'this must refer to the proper chart object in your VBA Project
    With Chart8
        For Each oSmiley In .Shapes
              If oSmiley.AutoShapeType = msoShapeSmileyFace Then _
                oSmiley.Delete

        Next
        Set oSmiley = .Shapes.AddShape(msoShapeSmileyFace, 590.18, 7.06, 77.63, 79.42)
        With oSmiley
            Select Case Actual
                Case Is < BaseLine  'RED frown
                    .Adjustments.Item(1) = 0.7180555
                    .Fill.ForeColor.RGB = RGB(255, 0, 0)
                
                Case Is >= Tgt      'GREEN smile
                    .Adjustments.Item(1) = 0.8111111
                    .Fill.ForeColor.RGB = RGB(0, 255, 0)
                    
                Case Else          'YELLOW neutral
                    .Adjustments.Item(1) = 0.7703704
                    .Fill.ForeColor.RGB = RGB(255, 204, 0) 
            End Select
        End With
    End With
End Sub

Note that I can change the criteria and reference cells...I just need to know how to reference the different shapes.

Thanks!

 

[SkipVought]

Hi,

I'd suggest having all three shapes already on the sheet at design time. User logic to determine which shape to assign the visible property True, while the other would be False at run time. You could also move the shape by assigning new values for the Top & Left properties.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[shelby55]

Hi Skip

Thanks, that is what I was thinking too. But not sure how ...can you start me off? Thanks.

 

[SkipVought]

On the sheet, insert your 3 shapes from the Insert ribbon.

Select each one, in turn, and type an appropriate name in the Name Box, to the left of the Formula Bar.

Assume your three names are spOct, spTri, spCir. Then you can reference them in your code as...

Sub test()
'this is how you call the one to show
    ShowShape "spOct"
End Sub

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
                    sp.Visible = True
                Case Else
                    sp.Visible = False
            End Select
        End With
    Next
    Application.ScreenUpdating = True
End Sub

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[shelby55]

Hi Skip

Sorry but just getting back to this..thanks for your reply.

Now the client is thinking about using other non-standard images for the results and I have some questions:
- do these images need to reside on the all of the computers that will be using this tool?
- though I'll still use the above to be visible or not depending on the results, how do I reference .wmf images?
- anything else I need to know about using non-standard images?

Thanks very much.

Shelby

 

[SkipVought]

Each image, picture, whatever, is in the Shapes collection, so it does not matter. If you add ALL your shapes, then it does not matter if that object's file is on the PC or not. It is embedded in your worksheet! All your code is doing is controlling the Visible property and the size & position: all properties of the Shape object.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[shelby55]

Hi Skip

Thanks very much. In the code you provided me I'm confused because you don't use the visible property to coincide with the results as in the original.

Are you saying that I create a separate function for each shape to call? I think there was a problem with that before because the code hasn't to be within the Chart_Activate or it doesn't copy over to the copy version.

Thanks.

 

[SkipVought]

Are you saying that I create a separate function for each shape to call?

No. One function, where you pass the name of the shape you want to make visible, as in the Test sub.

Put the code in the objects that you are copying, rather than in a separate module.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[shelby55]

Hi Skip

I'm sorry but I'm still not clear on what I do for this using my current code.

Can you give me another hint?

 

[ItIsHardToProgram]

what I do for this using my current code.

No. One function, where you pass the name of the shape you want to make visible, as in the Test sub.

            Select Case Actual
                Case Is < BaseLine  'RED frown
                    ShowShape "RedShape"
                
                Case Is >= Tgt      'GREEN smile
                    ShowShape "BlueShape"
                    
                Case Else          'YELLOW neutral
                    ShowShape "YellowShape"
            End Select

Remove all with

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[SkipVought]

'....
    Select Case Actual
        Case Is < BaseLine  'RED frown
            ShowShape "RedOctagon"
        Case Is >= Tgt      'GREEN smile
            ShowShape "GreenCircle"
        Case Else          'YELLOW neutral
            ShowShape "YellowTriangle"
    End Select
'....

or whatever you have named each shape.

REMEMBER:

At DESIGN TIME you add each shape and name each appropriately. This is a ONE TIME event.

At RUN TIME you make whatever shape visible, based on the parameters at that time. This occurs MANY TIMES.



Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[shelby55]

Hi

Thanks very much. Okay I understand that it's visible versus invisible so we don't have to do like in the other code where we delete the previous to add the new - these are already present on the graph.

I understand what I'll do for showing or not based on the data results. Skip you say I'm writing all in one module but in your example you have two subs so doesn't that mean more than one module?

Sorry guys I am really trying here.....

 

[soccercook44]

Nope, you can have more then one sub in a module.

 

[shelby55]

Hi

Thanks! Okay I'll try this out and let you know how it goes...thanks very much for everyone's input.

 

[SkipVought]

What I think you want is, all the code that relates to the chart and shapes on the chart, should be in the Chart Object Code Window.

In the Chart Object Code Window you will have, at least (and it could be more)...

1. ShowShape procedure (subroutine)

2. Chart_Activate event, in which you will have the code that calls ShowShape with the appropriate argument.



Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[shelby55]

Hi Skip

You read my mind...that is what I was trying to figure out because we need it all within the Chart Object Code Window for it to copy to the copy.

So I did that and had success with the pictures (thank you)but still have one issue: there is a company logo on the graph that needs to be there always. After adding this code it isn't visible which makes sense because of the code activesheet.shapes which select all pictures on the graph so the logo was included. At first I just added it to each condition:

select case actual
          case is < baseline
            showshape "spShark"
            showshape "logo"
       ..................

but this results in no picture showing up. I was thinking then that I need to indicate what each shape that I want included in the showshape function so that the visible true/false isn't applied to the logo but

set sp = Shapes(Array("spShark","spDolphin","spStar")

didn't work so not sure how to go about this.

Any and all assistance greatly appreciated - thanks.

 

[SkipVought]

This is why a clear statement of requirements is so important.

My original procedure ASSSUMES that the only shapes you have on your sheet are the shapes you want to control, so that only ONE of the shapes ON THE SHEET is visible at a time.

Now you have added another 'classification' of shpaes that does not fall into THAT logic. So you must either EXCLUDE this specific shape (the logo) from that logic or INCLUDE only the shapes (red ocatgon, green circle, yellow triangle) you want to control with that logic.

Which would you rather do?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[PHV]

In the ShowShape procedure, replace this:
Case sName
with this:
Case sName, "logo"

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

 

[shelby55]

Hi Skip

Sorry for the change in requirements...I didn't even consider the logo was a picture when doing this though obviusly it is.

I'd like to exclude the logo from the logic because I know I will always want it on the graph.

PHV: thanks for your reply but I tried what you indicated and it didn't work.

Thanks everyone.

 

[SkipVought]

PHV's solution will work IF you have named the logo shape 'logo'.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]