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 really appreciate you sticking this out with me.

In a module I have the following code:

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 Chart8
            For Each oSmiley In .Shapes
            If oSmiley.AutoShapeType = msoShapeSmileFace Then _
            oSmiley.Delete
        Next
        Set oSmiley = .Shapes.AddShape(msoShapeSmileyFace, .PlotArea.InsideWidth, 0, 75, 75)
'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
                    .Fill.ForeColor.RGB = RGB(0, 255, 0)   
                Case Is < BaseLine
                    .Adjustments.Item(1) = 0.7181
                    .Fill.ForeColor.RGB = RGB(255, 0, 0)
                Case Is < Tgt
                    .Adjustments.Item(1) = 0.7727
                    .Fill.ForeColor.RGB = RGB(255, 204, 0)
            End Select
        End With
    End With
End Sub

In "thisworksheet" I put the code:

Private Sub chart_activate()
Call SmilyFace
End Sub

So what am I doing wrong? Thanks.

 

[shelby55]

Hi Skip

I think I explained this before but when the user selects a new "user date" from the summary (and user date is a named range via data validation) the WSE changes and the values in target, baseline and actual change. BUT the values are still stored in x29, y29 and z29.

Will that matter for this project?

 

[SkipVought]

In my previous post I emphasized

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 [b]ActiveChart[/b]
....

Skip,

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

 

[shelby55]

Hi

Right...and I have With Chart8 - is that the wrong syntax? It does have a name of Admit_Chart but is referred to as Chart8 on when in the VBA window. Plus referencing as such worked when I was just using the syntax on open.

 

[SkipVought]

EXACTLY what statement is the error on?

Skip,

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

 

[shelby55]

Hi

No error statement, just the face isn't turning the right colour/type with results.

For instance, I know that choosing Sept 3 should yield a green happy face because it's above the target. The graph shows this data point above the target and cell X29 also shows the changed value but the face didn't change - stayed red frown which was from the "on open" values from July 17.

 

[SkipVought]

I think it was this statment...

            If oSmiley.AutoShapeType = [b]msoShapeSmileyFace[/b] Then _
                oSmiley.Delete

Skip,

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

 

[SkipVought]

Regarding the Chart Object as ActiveChart, YOUR code will only work with ONE chart.

I thought you said you have MULTIPLE charts?????

Skip,

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

 

[shelby55]

Hi Skip

But I have that statement in the code I've used...so take it out? change it?

 

[SkipVought]

Please post the VALUES in your 3 cells, both the value as displayed in the sheet and the value in the FORMULA BAR, if different, please.

Skip,

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

 

[shelby55]

Hi

The formula for x29 = IF(C29=0,"",SUMPRODUCT(('WK4'!$AT$3:$AT$1094)*(('WK4'!$DM$3:$DM$1094)='WK5'!A29))/N29)

The formula for y29 ==IF(C29=0,"",SUMPRODUCT(('WK4'!$CG$3:$CG$1094)*(('WK4'!$DM$3:$DM$1094)='WK5'!A29))/C29)

The formula for z29 = =IF(C29=0,"",SUMPRODUCT(('WK4'!$CH$3:$CH$1094)*(('WK4'!$DM$3:$DM$1094)='WK5'!A29))/C29)

On open the graph and summary table default to the most recent date, in this case July 17 and the values on WK5 are:
x29=54
y29=57
z29=62

So it makes sense the original face is red frown.

The values when selecting Sept 3 on WK5 are:
x29=68
y29=57
z29=62

but face stays red frown and should be a green happy face.

The chart is showing the same data as above (and as in WK5) for the selected dates.

 

[SkipVought]

I get RED on the first and GREEN on the second.

???

Skip,

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

 

[SkipVought]

Your code has

    With Sheets("[b]WSE[/b]")
       Actual = .Cells(29, "X").Value
       BaseLine = .Cells(29, "Y").Value
       Tgt = .Cells(29, "Z").Value
    End With

However...
"The values when selecting Sept 3 on WK5 are:"

???

Skip,

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

 

[shelby55]

Hi

That's what I said...the first shows red on open but it should be green for when I selecdt September 2 and isn't. Note that when I select a new date it is during the same session as when I opened the document if that makes a difference. So it opens to July 17, great, but if I choose from the userdate pull down list (which is a data validation named field) then all the summary and chart data switches to the dates advised.

Wk5 actually shows 20 weeks of data and the cells of x29, y29 and z29 change with the user select.

As above, I create the smilyface sub and then call it on chart_activate.

This is placed under the "on open" event of "this worksheet" (not as part of it, but after end sub statement). Should it be on the worksheet of the chart? Or somewhere else?

Note that even if I save Sept 3 to open to that, the face isn't green so obviously it's not "firing".

 

[shelby55]

Hi Skip

To answer your question, sorry but I'm editing my real code worksheet names for ease - WK5 is WKE in what I'm providing to you.

 

[SkipVought]

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

On open the graph and summary table default to the most recent date, in this case July 17 and the values on WK5 are:
x29=54 actual
y29=57 baseline
z29=62 target

So it makes sense the original face is red frown.

I also get RED. Is that not correct???


Skip,

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

 

[shelby55]

Hi

Yes it is correct BUT when I change the user date to September 3rd, then the face should change to green but it doesn't. It probably has to do with the fact that it's on change event not on open, correct?

So what should my event be if users can change the user range date while viewing the data?

 

[SkipVought]

I get GREEN for those values!!!

put a break in your code and STEP thru. Use the watch window to check the values.

faq707-4594

Skip,

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

 

[shelby55]

HI

I did as you indicated and it gave me type mismatch errors at actual, baseline, tgt but I don't get any errors when I open the document or move from sheet to sheet.

The watch indicated the above value=out of context, type is empty and context is module3smilyface for all 3.

Did I put the code in the proper spot? And call it correctly (see the code above)? I am completely and totally confused right now.

 

[SkipVought]

if you just RUN, does it error? If so what statement?

The missmatch does not make sense as all three variables are VARIENT by default, which takes ANY kind of value.

Skip,

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