|
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 |
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 |
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 |
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 |
X axis captions
Then it remains a secret of Excel mentality!
Thanks, Stefi €žAndy Pope€ť ezt Ă*rta: Thanks for posting the update. I can not see anything in your code that would cause the chart to override the original setting of Ticklabelspacing. Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Stefi" wrote in message ... 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 |
X axis captions
Finally I've found out that in which cases Excel overrides the original
setting of Ticklabelspacing: If the worksheet window is maximized within Excel window (no matter Excel window is maximized or not) then it work OK in all cases. If it's not then Excel resets Ticklabelspacing to 2 in some cases. I still don't know what it depends on and why does it happen! Stefi €žStefi€ť ezt Ă*rta: Then it remains a secret of Excel mentality! Thanks, Stefi €žAndy Pope€ť ezt Ă*rta: Thanks for posting the update. I can not see anything in your code that would cause the chart to override the original setting of Ticklabelspacing. Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Stefi" wrote in message ... 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 |
X axis captions
Another thing:
ActiveChart.Axes(xlValue).MaximumScale = skalamax and ActiveChart.Axes(xlCategory).TickLabels.Font.Size = 8 both made Excel recalculate Ticklabelspacing (but only if worksheet window was not maximized). Stefi €žStefi€ť ezt Ă*rta: Finally I've found out that in which cases Excel overrides the original setting of Ticklabelspacing: If the worksheet window is maximized within Excel window (no matter Excel window is maximized or not) then it work OK in all cases. If it's not then Excel resets Ticklabelspacing to 2 in some cases. I still don't know what it depends on and why does it happen! Stefi €žStefi€ť ezt Ă*rta: Then it remains a secret of Excel mentality! Thanks, Stefi €žAndy Pope€ť ezt Ă*rta: Thanks for posting the update. I can not see anything in your code that would cause the chart to override the original setting of Ticklabelspacing. Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Stefi" wrote in message ... 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 |
| All times are GMT +1. The time now is 08:07 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com