Formatting X and Y axis' in Errorbars with VBA...Not working.

[qucmarco]

Hi,

I have the need to create a timeline using information from MS Project 2007 and Excel 2010. I have figured out how to export the tasks and related details from Project into excel using VBA while keeping the indentation and even doing some color coding based on the state of the task (e.g. ahead, behind, on-track, etc.). </SPAN>
The next step is to create a timeline in Excel based on this data. My initial approach was to see if I could create a custom button on the menu bar to execute a macro in Project like I did for the export, but I could not get this to work. So I decided to create a button in Excel to pretty much do the same thing (i.e. create a macro in Excel and add a button on the menu bar to run it.). This actually worked well as using the “run macro” option in Excel created all the code needed to produce the report. However I found out that for some reason the code does not keep the correct formatting on the error bars for both the x and y axis’. The formatting is different for both of these and it appears that when I run the code below, it “selects” whichever axis is last in the coding for formatting. Here is the code.</SPAN>
/*</SPAN>

Sub Project_Timeline()
‘
‘ Project Timeline
‘</SPAN>
‘First I add the chart, and then move it to the correct location. Then I add the data series </SPAN>
‘for Duration first, select the appropriate cells do some formatting like changing the date ‘format, removing the primary and secondary axis’ for the chart, changing the title, and ‘changing the marker type. Next I modify the errorbars y and x axis and format these. I ‘then do the same thing for the subsequent series (not included below). </SPAN>

ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlXYScatter
ActiveChart.SetSourceData Source:=Range(“Sheet1!$30:$30”)
ActiveSheet.Shapes(“Chart 1”).IncrementLeft -527.25
ActiveSheet.Shapes(“Chart 1”).IncrementTop -112.5
ActiveSheet.Shapes(“Chart 1”).ScaleWidth 2.2645833333, msoFalse, _
msoScaleFromTopLeft
ActiveSheet.Shapes(“Chart 1”).ScaleHeight 1.9635418489, msoFalse, _
msoScaleFromTopLeft
ActiveChart.SeriesCollection(1).Name = “=””Duration”””
ActiveChart.SeriesCollection(1).Xvalues = “=Sheet1!$C$33:$C$47”
ActiveChart.SeriesCollection(1).Values = “=Sheet1!$I$33:$I$47”
ActiveChart.Legend.Select
Selection.Delete
ActiveSheet.ChartObjects(“Chart 1”).Activate
ActiveChart.ChartTitle.Select
Selection.Caption = “Project Timeline”
ActiveChart.Axes(xlValue).MajorGridlines.Select
Selection.Delete
ActiveSheet.ChartObjects(“Chart 1”).Activate
ActiveChart.Axes(xlValue).Select
Selection.Delete
ActiveSheet.ChartObjects(“Chart 1”).Activate
ActiveChart.Axes(xlCategory).Select
Selection.TickLabels.NumberFormat = “m/d/yyyy”
Selection.TickLabels.NumberFormat = “[$-409]d-mmm-yy;@”
ActiveChart.SeriesCollection(1).Select
ActiveChart.SetElement (msoElementDataLabelLeft)
ActiveChart.SeriesCollection(1).HasErrorBars = True
ActiveChart.SeriesCollection(1).ErrorBars.Select
ActiveChart.SeriesCollection(1).ErrorBar Direction:=xlY, Include:= _
xlMinusValues, Type:=xlFixedValue, Amount:=1
ActiveChart.SeriesCollection(1).ErrorBars.EndStyle = xlNoCap
ActiveChart.SeriesCollection(1).ErrorBar Direction:=xlY, Include:= _
xlMinusValues, Type:=xlPercent, Amount:=5
ActiveChart.SeriesCollection(1).ErrorBar Direction:=xlY, Include:= _
xlMinusValues, Type:=xlPercent, Amount:=100
With Selection.Format.Line
.Visible = msoTrue
.ForeColor.ObjectThemeColor = msoThemeColorAccent1
.ForeColor.TintAndShade = 0
.ForeColor.Brightness = 0
End With
With Selection.Format.Line
.Visible = msoTrue
.Weight = 1.5
End With
With Selection.Format.Line
.Visible = msoTrue
.DashStyle = msoLineDash
End With
ActiveChart.SeriesCollection(1).ErrorBars.Select
ActiveChart.SeriesCollection(1).ErrorBar Direction:=xlX, Include:= _
xlPlusValues, Type:=xlFixedValue, Amount:=1
ActiveChart.SeriesCollection(1).ErrorBars.EndStyle = xlNoCap
ActiveChart.SeriesCollection(1).ErrorBar Direction:=xlX, Include:= _
xlPlusValues, Type:=xlCustom, Amount:=0
With Selection.Format.Line
.Visible = msoTrue
.ForeColor.ObjectThemeColor = msoThemeColorAccent1
.ForeColor.TintAndShade = 0
.ForeColor.Brightness = 0
End With
With Selection.Format.Line
.Visible = msoTrue
.ForeColor.ObjectThemeColor = msoThemeColorAccent2
.ForeColor.TintAndShade = 0
.ForeColor.Brightness = 0.400000006
.Transparency = 0
End With
With Selection.Format.Line
.Visible = msoTrue
.Weight = 3
End With</SPAN>

End sub</SPAN>
*/</SPAN>

Formatting seems to be based on the 1^st axis selected for the errorbar (in this case Y). Is there a way to select only one of the axis in an errorbar so this one can have focus and you can make subsequent changes to it. Similar to:</SPAN>

ActiveChart.SeriesCollection(1).ErrorBars.Select.xlY or ActiveChart.SeriesCollection(1).ErrorBars.Select.Direction=xlY</SPAN>
With Selection….</SPAN>

The value for the Type:=xlCustom, Amount:=0 does not work in the code but I have been able to fix this by doing the following:</SPAN>

Type:=xlCustom, Amount:=”=Sheet1!R33C6:R47C6”</SPAN>

Can someone help?</SPAN>
Thanks in advance,</SPAN>
Q</SPAN>