Results 1 to 5 of 5
  1. #1
    Join Date
    May 2005
    Posts
    15

    Unanswered: Conditionally display Block arrow autoshape

    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

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    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.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    May 2005
    Posts
    15
    It would indeed be of a great help if you could guide me on how to achieve this concept.

  4. #4
    Join Date
    Oct 2003
    Posts
    1,091
    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
    Attached Files Attached Files
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  5. #5
    Join Date
    Oct 2003
    Posts
    1,091
    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.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •