Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming,microsoft.public.excel.charting
|
|||
|
|||
Excel 2007 chart line width + axis labelling with VBA
Folks,
I'm creating a line-style chart with VBA in Excel 2007 and an attempting to control just two things: 1) The line weight (2 points) and 2) The text direction of the X-axis labels (they're inclined, I want them vertical). Now I can indeed achieve both these settings through the UI (Format Data Series/Line Style/Width and Format Axis/Alignment/Text direction/ Rotate through 270 degrees respectively) and naturally I thought that the macro recorder would help but alas, all I got was .... ActiveSheet.ChartObjects("Chart 19").Activate ActiveChart.Axes(xlCategory).Select I've gone through the online help and tried this and that but without success. Any ideas? TIA. |
#2
Posted to microsoft.public.excel.programming,microsoft.public.excel.charting
|
|||
|
|||
Excel 2007 chart line width + axis labelling with VBA
Hi,
Try, activechart.SeriesCollection(1).format.line.weight =2 activechart.Axes(xlcategory,xlPrimary).ticklabels. Orientation = xlTickLabelOrientationVertical Cheers Andy HB wrote: Folks, I'm creating a line-style chart with VBA in Excel 2007 and an attempting to control just two things: 1) The line weight (2 points) and 2) The text direction of the X-axis labels (they're inclined, I want them vertical). Now I can indeed achieve both these settings through the UI (Format Data Series/Line Style/Width and Format Axis/Alignment/Text direction/ Rotate through 270 degrees respectively) and naturally I thought that the macro recorder would help but alas, all I got was ... ActiveSheet.ChartObjects("Chart 19").Activate ActiveChart.Axes(xlCategory).Select I've gone through the online help and tried this and that but without success. Any ideas? TIA. -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info |
#3
Posted to microsoft.public.excel.programming,microsoft.public.excel.charting
|
|||
|
|||
Excel 2007 chart line width + axis labelling with VBA
Sadly the macro recorder does not seem to record actions to charts and
shapes in Excel 2007. Maybe this will get you started, note generally no need to select anything. Sub test() Dim cht As Chart Dim sr As Series Dim ax As Axis Set cht = ActiveSheet.ChartObjects("Chart 1").Chart ' << change name to suit Set ax = cht.Axes(xlCategory) ax.TickLabels.Orientation = xlTickLabelOrientationVertical For Each sr In cht.SeriesCollection sr.Format.Line.Weight = 2 Next End Sub If you fully declare your object variables, as above, you'll find the Intellisense helps a lot in working things out. Regards, Peter T "HB" wrote in message ... Folks, I'm creating a line-style chart with VBA in Excel 2007 and an attempting to control just two things: 1) The line weight (2 points) and 2) The text direction of the X-axis labels (they're inclined, I want them vertical). Now I can indeed achieve both these settings through the UI (Format Data Series/Line Style/Width and Format Axis/Alignment/Text direction/ Rotate through 270 degrees respectively) and naturally I thought that the macro recorder would help but alas, all I got was ... ActiveSheet.ChartObjects("Chart 19").Activate ActiveChart.Axes(xlCategory).Select I've gone through the online help and tried this and that but without success. Any ideas? TIA. |
#4
Posted to microsoft.public.excel.programming,microsoft.public.excel.charting
|
|||
|
|||
Excel 2007 chart line width + axis labelling with VBA
On Aug 22, 7:52*pm, "Peter T" <peter_t@discussions wrote:
Sadly the macro recorder does not seem to record actions to charts and shapes in Excel 2007. Maybe this will get you started, note generally no need to select anything. Sub test() Dim cht As Chart Dim sr As Series Dim ax As Axis * * Set cht = ActiveSheet.ChartObjects("Chart 1").Chart ' << change name to suit * * Set ax = cht.Axes(xlCategory) * * ax.TickLabels.Orientation = xlTickLabelOrientationVertical * * For Each sr In cht.SeriesCollection * * * * sr.Format.Line.Weight = 2 * * Next End Sub If you fully declare your object variables, as above, you'll find the Intellisense helps a lot in working things out. Regards, Peter T "HB" wrote in message ... Folks, I'm creating a line-style chart with VBA in Excel 2007 and an attempting to control just two things: 1) The line weight (2 points) and 2) The text direction of the X-axis labels (they're inclined, I want them vertical). Now I can indeed achieve both these settings through the UI (Format Data Series/Line Style/Width and Format Axis/Alignment/Text direction/ Rotate through 270 degrees respectively) and naturally I thought that the macro recorder would help but alas, all I got was ... * *ActiveSheet.ChartObjects("Chart 19").Activate * *ActiveChart.Axes(xlCategory).Select I've gone through the online help and tried this and that but without success. Any ideas? *TIA. |
#5
Posted to microsoft.public.excel.programming,microsoft.public.excel.charting
|
|||
|
|||
Excel 2007 chart line width + axis labelling with VBA
On Aug 22, 7:52*pm, "Peter T" <peter_t@discussions wrote:
Sadly the macro recorder does not seem to record actions to charts and shapes in Excel 2007. Maybe this will get you started, note generally no need to select anything. Sub test() Dim cht As Chart Dim sr As Series Dim ax As Axis * * Set cht = ActiveSheet.ChartObjects("Chart 1").Chart ' << change name to suit * * Set ax = cht.Axes(xlCategory) * * ax.TickLabels.Orientation = xlTickLabelOrientationVertical * * For Each sr In cht.SeriesCollection * * * * sr.Format.Line.Weight = 2 * * Next End Sub If you fully declare your object variables, as above, you'll find the Intellisense helps a lot in working things out. Regards, Peter T "HB" wrote in message ... Folks, I'm creating a line-style chart with VBA in Excel 2007 and an attempting to control just two things: 1) The line weight (2 points) and 2) The text direction of the X-axis labels (they're inclined, I want them vertical). Now I can indeed achieve both these settings through the UI (Format Data Series/Line Style/Width and Format Axis/Alignment/Text direction/ Rotate through 270 degrees respectively) and naturally I thought that the macro recorder would help but alas, all I got was ... * *ActiveSheet.ChartObjects("Chart 19").Activate * *ActiveChart.Axes(xlCategory).Select I've gone through the online help and tried this and that but without success. Any ideas? *TIA. Andy & Peter, Thanks, I eventually hit upon TickLabels.Orientation (despite the Byzantine labyrinth that is the Excel online help); instead of Format I eventually came upon the Border property which seemed to work also: With .SeriesCollection(1) .... .Border.Weight = n End With It seems the online help (along the Object Browser and Macro Recorder) bear the hallmarks of a project rushed out the door with quite a few gaps and rough edges. Anyway thanks again guys. |
#6
Posted to microsoft.public.excel.programming,microsoft.public.excel.charting
|
|||
|
|||
Excel 2007 chart line width + axis labelling with VBA
That's the method for formatting the border (ie line) weight in
Excel97-2003, and works in 2007 for compatibility. The .Weight property can accept any one of four constants, 1, 2, -4138, or 4, also named as xlHairline, xlThin, xlMedium & xlThick respectively. Providing your value for 'n' is one of these constants your code will work. But don't try say 5 (you can apply 3 but it will read back as -4138). For 2007 better to use the method Andy and I suggested. Regards, Peter T "HB" wrote in message news:f1adf471-21b9-45c0-914f- I eventually came upon the Border property which seemed to work also: With .SeriesCollection(1)... .Border.Weight = n End With |
#7
Posted to microsoft.public.excel.programming,microsoft.public.excel.charting
|
|||
|
|||
Excel 2007 chart line width + axis labelling with VBA
On Aug 23, 7:32*pm, "Peter T" <peter_t@discussions wrote:
That's the method for formatting the border (ie line) weight in Excel97-2003, and works in 2007 for compatibility. The .Weight property can accept any one of four constants, 1, 2, -4138, or 4, also named as xlHairline, xlThin, xlMedium & xlThick respectively. Providing your value for 'n' is one of these constants your code will work. But don't try say 5 (you can apply 3 but it will read back as -4138). For 2007 better to use the method Andy and I suggested. Regards, Peter T Thanks, that's indeed what I've done. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Line chart - time series - category axis date format (2007 VBA) | Charts and Charting in Excel | |||
Excel 2007 line chart automatic category axis text rotation | Charts and Charting in Excel | |||
Line chart categoray axis labels issue - Excel 2007 | Charts and Charting in Excel | |||
Bar chart x axis labelling | Charts and Charting in Excel | |||
Excel 2003, graph y axis labelling problem | Charts and Charting in Excel |