Using VBA to change start and stop arrows on a trendline

[eldridge.michael]

Good morning everyone,


I am trying to simplify a large group of bubble charts that my company has been using. The charts compare a clients March data to their September data and draw a trend line using the "Oval Arrow" as the start point and the "Arrow" as the end point. The problem is, that not every client improved, so Excel is drawing trend lines backwards essentially.


Each client has their own bubble chart, and there are two bubbles for each client, with up to 6 clients per region. So I have the charts stacked on top of each, so it gives the appearance of one fluid graph. I have set up an indicator "!!!", that says if x > y, "!!!".


The problem is I have a limited knowledge of VBA and I'm having problems writing a code that says if cell "H5"="!!!", then format the trend line of "Chart 1", in this way (flipping the starting and ending point icons).


I'm using Excel 2007, and if I haven't completely confused you, let me know if there is any other information I can provide.

 

[NARAYANK991]

Hi Michael ,


The ideal would be if you could upload your workbook.


The next best would be if you could upload your workbook without the data in it ; this would give us the chart and the code.


The least is if you can copy and paste your code here ; paste it in between backticks ( the key to the left of 1 on the second line from the top of your keyboard ) ; this will retain any code indentation that might be there in your workbook.


To upload anything to this forum , just use your own preferred file-sharing website ( my preference is SpeedyShare , http://speedy.sh/ ) , give others access rights / permission to download the file , and post the access link in this same topic.


Narayan

 

[eldridge.michael]

Narayan,


Thanks for the tip. Here is the link.


http://speedy.sh/84Db2/Hubba-Bubba-Charts-v2.0.xlsm

 

[eldridge.michael]

Figured it out ... here's an example.


'If Range("H11") = "!!!" Then


ActiveSheet.ChartObjects("Chart 4").Activate

With ActiveChart.SeriesCollection(1).Trendlines(1).Format.Line

.BeginArrowheadLength = msoArrowheadLong

.BeginArrowheadStyle = msoArrowheadTriangle

.BeginArrowheadWidth = msoArrowheadWide

.EndArrowheadLength = msoArrowheadLengthMedium

.EndArrowheadStyle = msoArrowheadOval

.EndArrowheadWidth = msoArrowheadWidthMedium

End With

End If


If Range("H11") <> "!!!" Then


ActiveSheet.ChartObjects("Chart 4").Activate

With ActiveChart.SeriesCollection(1).Trendlines(1).Format.Line

.BeginArrowheadLength = msoArrowheadLengthMedium

.BeginArrowheadStyle = msoArrowheadOval

.BeginArrowheadWidth = msoArrowheadWidthMedium

.EndArrowheadLength = msoArrowheadLong

.EndArrowheadStyle = msoArrowheadTriangle

.EndArrowheadWidth = msoArrowheadWide

End With

End If'

 

[NARAYANK991]

Hi Michael ,


Nice to know you have resolved the problem ; thanks also , for posting the solution.


Narayan