Forum home » Delegate support and help forum » Microsoft Excel Training and help » Autoshapes VBA on Performance Dashboard
|
|
|
The UK's most regular Training information: See also: |
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Autoshapes VBA on Performance Dashboard
Resolved · Urgent Priority · Version 2003
Reply Fri 10 Sep 2010, 12:51Delegate Gary said...
Gary has attended:
Access VBA course
Excel VBA Advanced course
Autoshapes VBA on Performance Dashboard
Good afternoon
I have created some code that is basically a Select Case statement that gives different autoshapes within a cell for good and bad performance. But, I need to do some code so as when the macro is refreshed it deletes the existing autoshapes and begins again. The code that I have produced is:
Sub arrow()
For Each c In Range("A3:B7")
Select Case c.Value
Case Is = 1
With ActiveSheet.Shapes.AddShape(msoShapeUpArrow, c.Left + c.Width, c.Top, 6, c.Height)
.Fill.ForeColor.SchemeColor = 11
End With
Case Is = 2
With ActiveSheet.Shapes.AddShape(msoShapeDownArrow, c.Left + c.Width, c.Top, 6, c.Height)
.Fill.ForeColor.SchemeColor = 10
End With
Case Is = 3
With ActiveSheet.Shapes.AddShape(msoShapeDownArrow, c.Left + c.Width, c.Top, 6, c.Height)
.Fill.ForeColor.SchemeColor = 10
End With
Case Is = 4
With ActiveSheet.Shapes.AddShape(msoShapeDownArrow, c.Left + c.Width, c.Top, 6, c.Height)
.Fill.ForeColor.SchemeColor = 10
End With
Case Is = 5
With ActiveSheet.Shapes.AddShape(msoShapeDownArrow, c.Left + c.Width, c.Top, 6, c.Height)
.Fill.ForeColor.SchemeColor = 10
End With
End Select
Next c
End Sub
If you could bolt something onto this to enable me to rerun the macro time and time again, that would be appreciated.
Thanks
Gary
For upcoming training course dates see: Pricing & availability
RE: Autoshapes VBA on Performance Dashboard
Hi Gary, thanks for your query. Two things come to mind here. First of all you are trying to code the conditional formatting enhancements that come as standard in Excel 2007! However, there's nothing wrong with trying to replicate a work around for 2003, but you might try looking on the net for a free add-in that will do this for you.
However, if you do want to code it consider changing the fill colours of the arrows rather than deleting them out and putting them back in again. Every time you delete them out you are inserting new objects with new index numbers onto the sheet which is why you're having problems identifying the shapes and then deleting them. Just overlay a series of arrows all initially with no fill colour or border and change the formatting of the one you want dependent on the case. This should also work much quicker than continually re-drawing the objects on the screen.
Hope this helps,
Anthony
Reply Mon 20 Sep 2010, 09:15Delegate Gary said...
RE: Autoshapes VBA on Performance Dashboard
Yes, this is the code that I found on the internet also.
How would you centre the arrow in the cell??
Thanks
Gary
|