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

If while in the VBA window I hit run I get the mismatch error (run time error 13). If I comment out actual then same error for baseline and if I comment out both then same message for tgt.

It first doesn't like "sub smilyface()".

 

[SkipVought]

What do you see in the Watch Window for when your break

Sheets("WSE").Cells(29, "X").Value

Skip,

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

 

[shelby55]

Hi

Same runtime error 13 - type mismatch.

 

[SkipVought]

SOMETHING is incorrect about the Sheet Name.

Is your sheet named WSE or WS5, or????

Skip,

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

 

[shelby55]

Hi Skip

I've checked my code and there is only one spot for the sheet name to go and it is correct with my name, not the fake names used in this thread.

Note also that if I put the code in the "on open" event it works fine. So if I save the date as Sept 3 and then open the worksheet, the face is happy and green. If I leave on a date I know that the actual is below the baseline then I open it and it's a red frown face.

So it appears I can't get it to be in the chart_activate event to change whenever the data changes and not on the "on open" event. What am I missing? Am I entering correctly above in the right areas? Is on the "this worksheet" where I'm supposed to enter:

Private Sub chart_activate()
Call SmilyFace
End Sub

Thanks.

 

[shelby55]

Hi Skip

One more thing:

I don't think the case values count for all instances:

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

But what about if the target is >= baseline? When I tried to open to a day that was >= baseline which should be the same face (yellow, neutral) as below target, I got the error 13 on open. Is this related?

 

[SkipVought]

SmilyFace should be in a MODULE.

The Chart_Activate event shoud be in the Chart Sheet Object. I am concerned that you copy/pasted WITHOUT CAPS chart_activate. Did you actually TYPE this code?

Skip,

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

 

[shelby55]

Hi

SmilyFace is by itself in Module 3. Based on your comments
I double clicked on Chart8 under Microsoft Excel objects. I typed Chart_Activate and "activate" shows in the pull down list on the top right with "chart" showing in the pull down list on the left. Between the Sub and End Sub I type in "Call SmilyFace".

I get out of the VBA window and get the run-time error 13. It doesn't like the

Actual = .Cells(29, "X").Value

Skip, I really appreciate all your time on this - thanks so much.

 

[shelby55]

Hi Skip

Does it matter that Actual, Baseline and Tgt aren't identified i.e. the first line of

 Dim BaseLine, Tgt, Actual

without an AS whatever Object statement?

Or is that what you meant before by them being varient by default?

 

[SkipVought]

The latter.

Skip,

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

 

[shelby55]

Hi

I knew it couldn't be that simple. Any thoughts on what the heck this is? Could it be that I have to refresh a page first or something? There is a delay when the chart refreshes..could that be an issue?

Anything else I can try? Thanks again Skip.

 

[SkipVought]

Last resort. Scrub & e mail me the workbook, mojop. You should have the address from a yr ago.

Skip,

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

 

[SkipVought]

But what about if the target is >= baseline?

Your original requirement:
[tt]
'if actual>=target then green happy face,
'if actual <target but >=baseline then yellow neutral face, '
'if actual <baseline then red frown face.
[/tt]
Here is a diagram representing the possible values and results:
[tt]
[red]RED[/red] | [highlight]YELLOW[/highlight] | [green]GREEN[/green]
| |
| |
actual < baseline < target < actual
[/tt]
Bottom line: if target is less than baseline it's still red, so the target>=baseline adds nothing to the logic.

And it could be further simplified in code as

        With oSmiley
            Select Case Actual
                Case Is < BaseLine  'RED frown
                    .Adjustments.Item(1) = -0.05
                    .Fill.ForeColor.RGB = RGB(255, 0, 0)
                Case Is >= Tgt      'GREEN smile
                    .Adjustments.Item(1) = 0.05
                    .Fill.ForeColor.RGB = RGB(0, 255, 0)
                Case [b]Else[/b]          'YELLOW neutral
                    .Adjustments.Item(1) = 0#
                    .Fill.ForeColor.RGB = RGB(255, 204, 0)
            End Select
        End With

Skip,

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

 

[shelby55]

Hi Skip

I really appreciate all you did to check this out but I don't understand why the version I sent you works but mine doesn't.

I have Excel 2003 SP3 - am I missing a patch or something? Is there anything else that would prevent this from working on one computer but not another that I should check?

Thanks again.

 

[SkipVought]

FYI, I have removed the Workbook_Open code.

What would happen if you make these rows a comment, like...

Private Sub Workbook_Open()
'ThisWorkbook.RefreshAll
'AssignMaxDate
Call copyWorkbook
End Sub

save it. then open. Mine works after I open.

Tell me if anything works differently.


Skip,

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

 

[shelby55]

Hi

Thanks for persevering on this!

I did as you suggested and the query didn't run automatically (I guess due to not having the refresh all) so I had to manually refresh and when selecting the chart tab I got the same type mismatch error with Actual being noted as stopping the code, just like before.

 

[SkipVought]

I PURPOSLY did NOT want you to run ANY QUERY. That is the purpose of the COMMENTS I added to ONLY run the LAST STATEMENT!!!

please do it again, JUST AS I SENT TO YOU. NO QUERY REFRESH, PLEASE.

This may be a tedious step by step process.

Skip,

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

 

[shelby55]

Hi Skip

Sorry about that. Okay, did as you suggested but same error - type mismatch on Actual.

 

[SkipVought]

Try to substitute...

       Actual = .Cells(29, "X").Value
       BaseLine = .Cells(29, "Y").Value
       Tgt = .Cells(29, "Z").Value

for

       Actual = .Cells(29, 24).Value
       BaseLine = .Cells(29, 25).Value
       Tgt = .Cells(29, 26).Value

Skip,

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

 

[shelby55]

Hi Skip

Ta da - that worked. I got a red frown face on open and then when I selected Sept 3 it was green as it should be.

So what does this all mean? Thanks.