Conditionally display Block arrow autoshape

neeraj_chow · #1 (**permalink**) 12-01-06, 07:29

I have lots of chart dispalying weekly sales figures of specific products. I was thinking of embedding on the chart a separate autoshape ((Block arrrows) Up or down) depending on wether the sales have increased or decreased.
Is there any way (simple/VBA) way that I can condilitionally show either the UP or DOWN Block arrow autoshape with the %age increase/decrease in it.
I am using Excel XP.
Thanks in advance for the assistance rendered.

I have also posted my query at
http://www.mrexcel.com/board2/viewtopic.php?t=245939

shades · #2 (**permalink**) 12-01-06, 17:43

Yes, you can. I had about 70 autoshapes to do that automatically (each run of the macro only took 1-2 seconds); and change color. The way I handled it was to set up a hidden worksheet in a table, with the result in one column, and then the reference to the autoshape in the next column. I used "i" as the designation for the autoshapes (AS1, AS2, AS3, etc.), and then looped the code with "i". This way if you ever decide to change, you change the worksheet, not the underlying VBA code.

If you are interested, I can post the code later this weekend or Monday.

neeraj_chow · #3 (**permalink**) 12-05-06, 02:52

It would indeed be of a great help if you could guide me on how to achieve this concept.

shades · #4 (**permalink**) 12-05-06, 16:22

Here is an attachment that works through the data. There are two worksheets (for simplicity).

Worksheet: Show
Show has data in Col. B, D, F, H, J. Beside each number is an AutoShape. Each AutoShape is numbered AS2 through AS37. They are number from right -to-left (although the second is the sequence is third (just because of the originals etup - didn't have time to change).

Worksheet: Work
Work has formulas to check various data points in each row. The AutoShape reference is in Column C. The formulas in Col. A compare values on Show (4 comparisons in separate rows for each category).

To update the AutoShapes, click on the button ("Update Chart Arrows).

Here is the code: Notice that there are four separate macros. The first is the controlling macro and checks the values in Column A on Work. Then depending on its value, it will proceed to the proper color to change the direction and color of the AutoShape.

Code:

Sub OriginalValueCheck()
'This checks the cells in Column A on worksheet Work (rows 2 through 37)
Application.ScreenUpdating = False
    Dim i As Long
    Sheets("Work").Visible = True
    Sheets("Work").Select
    For i = 2 To 37
        Select Case Cells(i, 1).Value
        Case Is < 0: RedAutoShape i
        Case Is > 0: GreenAutoShape i
        Case 0: OrangeAutoShape i
        End Select
    Next i
    
    Sheets("Work").Visible = False
    Sheets("Show").Select
Application.ScreenUpdating = True

End Sub

Code:

Sub RedAutoShape(i)
    Sheets("Show").Select
    With ActiveSheet.Shapes("MattAS " & i)
        .AutoShapeType = msoShapeDownArrow
        .Fill.Visible = msoTrue
        .Fill.ForeColor.SchemeColor = 10
        .Line.ForeColor.SchemeColor = 10
        .Rotation = 0#
    End With
    Sheets("Work").Select

End Sub

Code:

Sub GreenAutoShape(i)
    Sheets("Show").Select
    With ActiveSheet.Shapes("MattAS " & i)
        .AutoShapeType = msoShapeUpArrow
        .Fill.Visible = msoTrue
        .Fill.ForeColor.SchemeColor = 57
        .Line.ForeColor.SchemeColor = 57
        .Rotation = 0#
    End With
    Sheets("Work").Select

End Sub

Code:

Sub OrangeAutoShape(i)
    Sheets("Show").Select
    With ActiveSheet.Shapes("MattAS " & i)
        .AutoShapeType = msoShapeLeftRightArrow
        .Fill.Visible = True
        .Fill.ForeColor.SchemeColor = 52
        .Line.ForeColor.SchemeColor = 52
        .Rotation = 0#
    End With
    Sheets("Work").Select

End Sub

HTH

shades · #5 (**permalink**) 12-05-06, 16:40

Note: I have not incorporated any kind of error checking because I am the only one using it. In other words, I could check the AutoShape count on Show to make sure it is accurate and matches the number of rows used on Work.

To see this work, (row 5 has sum formulas) change the values on Show in cells B6:B13, D6: D13, F6:F13, H6:H13, J6:J13. Then click the Update button.

Internet Services

Web Development

Digital Marketing

Consumer Tech