Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
X axis captions
Hi All,
I have a macro creating a column chart. It takes category axis captions from range("E1:AB1"), values from a selected row (row No is stored in a variable "sourcerow"), so my VBA line is: ActiveChart.SetSourceData Source:=Sheets(lapnev).Range( _ "E1:AB1,E" & sourcerow & ":AB" & sourcerow), PlotBy:=xlRows It works well except that in some cases only every second caption is displayed on the chart (E1 is displayed, F1 is left blank, G1 is displayed, H1 is left blank, etc.). The value columns are correct above the blank captions. I couldn't figure out any differences between correct and incorrect cases. How can I force to always display the whole caption range? Thanks, Stefi |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
X axis captions
Hi,
This will force every category label to be displayed ActiveChart.Axes(xlCategory).TickLabelSpacing = 1 Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Stefi" wrote in message ... Hi All, I have a macro creating a column chart. It takes category axis captions from range("E1:AB1"), values from a selected row (row No is stored in a variable "sourcerow"), so my VBA line is: ActiveChart.SetSourceData Source:=Sheets(lapnev).Range( _ "E1:AB1,E" & sourcerow & ":AB" & sourcerow), PlotBy:=xlRows It works well except that in some cases only every second caption is displayed on the chart (E1 is displayed, F1 is left blank, G1 is displayed, H1 is left blank, etc.). The value columns are correct above the blank captions. I couldn't figure out any differences between correct and incorrect cases. How can I force to always display the whole caption range? Thanks, Stefi |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
X axis captions
Thanks Andy, my code already contained this line, but your post guided me to
search for a solution around TickLabels. Changing TickLabels.Font.Size from 10 to 8 solved my problem, though I still don't understand what caused the different displays of the same content. Regards, Stefi €žAndy Pope€ť ezt Ă*rta: Hi, This will force every category label to be displayed ActiveChart.Axes(xlCategory).TickLabelSpacing = 1 Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Stefi" wrote in message ... Hi All, I have a macro creating a column chart. It takes category axis captions from range("E1:AB1"), values from a selected row (row No is stored in a variable "sourcerow"), so my VBA line is: ActiveChart.SetSourceData Source:=Sheets(lapnev).Range( _ "E1:AB1,E" & sourcerow & ":AB" & sourcerow), PlotBy:=xlRows It works well except that in some cases only every second caption is displayed on the chart (E1 is displayed, F1 is left blank, G1 is displayed, H1 is left blank, etc.). The value columns are correct above the blank captions. I couldn't figure out any differences between correct and incorrect cases. How can I force to always display the whole caption range? Thanks, Stefi |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
X axis captions
I checked again my charts and found that TickLabelSpacing was set to 2 in
charts displayed with every second ticklabels. Adding an extra ActiveChart.Axes(xlCategory).TickLabelSpacing = 1 at the end of the macro also fixed the problem. The original code was this: With ActiveChart.Axes(xlCategory) .CrossesAt = 1 .TickLabelSpacing = 1 .TickMarkSpacing = 1 .AxisBetweenCategories = True .ReversePlotOrder = False End With ActiveChart.Axes(xlCategory).Select With Selection.TickLabels .Alignment = xlCenter .Offset = 100 .ReadingOrder = xlContext .Orientation = xlHorizontal End With ActiveChart.Axes(xlCategory).AxisTitle.Select With ActiveChart.Axes(xlValue) .MinimumScaleIsAuto = True .MaximumScale = skalamax .MinorUnit = 1 End With Which statement after .TickLabelSpacing = 1 changed TickLabelSpacing setting to 2? Regards, Stefi €žStefi€ť ezt Ă*rta: Thanks Andy, my code already contained this line, but your post guided me to search for a solution around TickLabels. Changing TickLabels.Font.Size from 10 to 8 solved my problem, though I still don't understand what caused the different displays of the same content. Regards, Stefi €žAndy Pope€ť ezt Ă*rta: Hi, This will force every category label to be displayed ActiveChart.Axes(xlCategory).TickLabelSpacing = 1 Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Stefi" wrote in message ... Hi All, I have a macro creating a column chart. It takes category axis captions from range("E1:AB1"), values from a selected row (row No is stored in a variable "sourcerow"), so my VBA line is: ActiveChart.SetSourceData Source:=Sheets(lapnev).Range( _ "E1:AB1,E" & sourcerow & ":AB" & sourcerow), PlotBy:=xlRows It works well except that in some cases only every second caption is displayed on the chart (E1 is displayed, F1 is left blank, G1 is displayed, H1 is left blank, etc.). The value columns are correct above the blank captions. I couldn't figure out any differences between correct and incorrect cases. How can I force to always display the whole caption range? Thanks, Stefi |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
createing a dynamic array of buttons with captions loaded from she | Excel Discussion (Misc queries) | |||
Cell values as captions | Excel Discussion (Misc queries) | |||
How do I put captions on Excel charts? | Charts and Charting in Excel | |||
Can the captions for Option Buttons be drawn from other cells ? | Excel Discussion (Misc queries) | |||
How to insert X axis scale values next to axis and X axis grid lin | Charts and Charting in Excel |