VBA To Format Autoshape Based on Conditions 1

[shelby55]

Hello

I am using Excel 2003.

I have a dashboard type report that has graphs. I want to be able to program a face (happy, frown, indifferent) based on the results of another cell.

How can I do this via VBA? I was thinking that I'll need all 3 faces on the page but the applicable one is only visible (with the right colour) based on the results of the specific cell compared to its target.

How do I go about doing this - thanks.

Shelby

 

[shelby55]

HI Skip

I know what it's doing but I don't know what part of your syntax is doing what to know how to apply it for what I need.

For instance,

nTot = Application.CountA(.Range("A4:A108"))
        nWK = Application.CountA(.Range("L4:L108"))

Is the above referring to the reference data or the position of the items? CountA - is that the name of the field/named range or what you've assigned it? Why Application versus workbook or worksheet?

I've never had my original questions answered: how do I reference named ranges in the syntax, how to reference the various worksheets (the one with the formulae versus the chart where the face is to go).

 

[SkipVought]

I have 2 ranges in my chart source data, like...
[tt]
Wk Tot
1 22
2 33
3 44
4
[/tt]
I am getting the COUNTA (spreadsheet function that COUNT ocurrences in a range), for each range, in this example the Tot count would be 3 and the Wk count would be 4.

So my LEFT property would be based on being 3/4 of the Inside Plot Width.

Your chart may be entirely different. Is you LAST plottred value ALWAYS at the far right of the plot area, or somewhere else?

Skip,

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

 

[shelby55]

Hi

Thanks Skip. The plot value will always be on the far right. But I can reference the value from another worksheet, not the chart.

I also need to reference the target and baseline values to know what face and what colour to use.

The face should always occur in the top right corner of the chart off of the plot area.

 

[SkipVought]

Where are the Target and baseline values (references)

What is the actual code you are using to change the smiley shape?

Skip,

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

 

[shelby55]

Hi Skip

I don't have any code figured out just yet for the face, that is what this thread is all about.

I do have partial code figured out if I want a red frown face:

Shapes.AddShape(msoShapeSmileyFace, 367.5, 84.75, 78#, 75.75). _
        Select
    Selection.ShapeRange.Fill.Visible = msoTrue
    Selection.ShapeRange.Fill.Solid
    Selection.ShapeRange.Fill.ForeColor.SchemeColor = 10
    Selection.ShapeRange.Fill.Transparency = 0#
    Selection.ShapeRange.Line.Weight = 0.75
    Selection.ShapeRange.Line.DashStyle = msoLineSolid
    Selection.ShapeRange.Line.Style = msoLineSingle
    Selection.ShapeRange.Line.Transparency = 0#
    Selection.ShapeRange.Line.Visible = msoTrue
    Selection.ShapeRange.Line.ForeColor.SchemeColor = 64
    Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255)
    Selection.ShapeRange.Adjustments.Item(1) = 0.7181
End Sub

Once I get help answering my questions hopefully I can use the above.

The value for the latest information on the chart is located on worksheet WSE, cell X29; the baseline is on worksheet WSE, cell Y29; the target is on worksheet WSE, cell Z29. This is where they will always be located.

Thanks.

 

[SkipVought]

Your code merely adds a RED SMILEY FACE.

You can, however, use the Wingdings font,
[tt]
J is a smiley
K is a neutral
L is a frown.
[/tt]



Skip,

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

 

[shelby55]

Hi Skip

Actually I found out that the smiley face can be edited to be frown or neutral based on the Selection.ShapeRange.Adjustments.Item(1) attribute. 0.7727 is for neutral face and 0.7181 for frown face.

From what I've read, I can't add wingdings to a Chart - it doesn't allow for the insert symbol function.

So can you help me with the code I need to add a face based on criteria from a worksheet? Thanks.

 

[SkipVought]

You do not need to insert ANYTHING, except a TEXTBOX one time.

You FORMAT the textbox FONT to Windings and CHANGE the TEXT to "J", "K" or "L" in accordance with your chart source data.

Skip,

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

 

[shelby55]

Hi

Thanks Skip....but again we come down to you haven't provided the code I need to do this nor have you answered the questions I asked about how to reference things.

I'm a newbie to this which is why I'm on Tek Tips to ask these questions......

 

[SkipVought]

The value for the latest information on the chart is located on worksheet WSE, cell X29; the baseline is on worksheet WSE, cell Y29; the target is on worksheet WSE, cell Z29.

dim BaseLine, Tgt, Latest
with Sheets("WSE")
   Latest = .Cells(29, "X").value
   BaseLine = .Cells(29, "Y").value
   Tgt = .Cells(29, "Z").value
end with

Skip,

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

 

[SkipVought]

According to Andy Pope, Microsoft MVP - Excel, the Adjustments values are...

http://www.eggheadcafe.com/software/aspnet/33428978/xl2007--shapes--adjust.aspx

Frown -0.05

Smile 0.05

and I found the Neutral as 0.0.

Skip,

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

 

[shelby55]

Great...but you still haven't helped me with what I've been asking.

 

[SkipVought]

Please tell me what I've missed.

Skip,

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

 

[shelby55]

Hi

I have no idea what this code is doing:

dim BaseLine, Tgt, Latest
with Sheets("WSE")
   Latest = .Cells(29, "X").value
   BaseLine = .Cells(29, "Y").value
   Tgt = .Cells(29, "Z").value
end with

Is it to reference the worksheets? So what hasn't been answered is how I reference the chart worksheet to then add the face.

How do I use the code above with the face i.e. different scenarios mean different faces.

 

[SkipVought]

1. If you already have a face on your chart, there is no need to ADD a face. This is a ONE TIME THING, unless you want to DELETE the face each time and then ADD it.

2. Is/Are your chart(s) in a worksheet (embedded) or are they Chart Sheets?

Skip,

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

 

[shelby55]

Hi

They are chart sheets. As per my previous post, I don't have a face on yet and the face will be different depending on the results of actual to target and baseline.

So if actual>=target then green happy face, if actual <target but >=baseline then yellow neutral face, if actual <baseline then red frown face.

I agree it is a one-time thing but that is what I am asking you to please help me code.

 

[SkipVought]

Sub SmilyFace()
    Dim oSmiley As Shape
    Dim BaseLine, Tgt, Actual
    With Sheets("WSE")
       Actual = .Cells(29, "X").Value
       BaseLine = .Cells(29, "Y").Value
       Tgt = .Cells(29, "Z").Value
    End With
'this must refer to the proper chart object in your VBA Project
    With Chart3
        For Each oSmiley In .Shapes
            oSmiley.Delete
        Next
        Set oSmiley = .Shapes.AddShape(msoShapeSmileyFace, .PlotArea.InsideWidth, 0, 50, 50)
'if actual>=target then green happy face,
'if actual <target but >=baseline then yellow neutral face, '
'if actual <baseline then red frown face.
        With oSmiley
            Select Case Actual
                Case Is >= Tgt
                    .Adjustments.Item(1) = 0.05
                    .Fill.ForeColor.RGB = RGB(0, 255, 0)
                Case Is < BaseLine
                    .Adjustments.Item(1) = -0.05
                    .Fill.ForeColor.RGB = RGB(255, 0, 0)
                Case Is < Tgt
                    .Adjustments.Item(1) = 0#
                    .Fill.ForeColor.RGB = RGB(255, 255, 0)
            End Select
        End With
    End With
End Sub

Skip,

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

 

[shelby55]

HI Skip

Ta da....this is exactly what I wanted. Thanks Skip!!

Just one more teeny, tiny question: how do I make the face bigger on the page.

Thanks again!!

 

[shelby55]

Hi Skip

Sorry but one more thing: remember when I said that the user could select a new date from the summary page? That means that the data in the chart could change meaning the face could/should change.

Since right now I've only called the above in the open event won't I need to add it somewhere else so it applies when they change the "most current" data?

If so, what event? Thanks.

 

[SkipVought]

Set oSmiley = .Shapes.AddShape(msoShapeSmileyFace, .PlotArea.InsideWidth, 0, [b]50, 50[/b])

check VBA HELP for AddShape Method: expression.AddShape(Type, Left, Top, Width, Height)

Call SmilyFace in the Chart_Activate event.

Change SmilyFace as follows...

....
    With [b]ActiveChart[/b]
        For Each oSmiley In .Shapes
          if oSmiley.AutoShapeType = msoShapeSmileFace then _
            oSmiley.Delete
        Next
....

Skip,

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