| 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 |
|
#5
Posted to microsoft.public.excel.charting
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.charting
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.charting
|
|||
|
|||
|
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 |
| 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 | |||
Linear Mode
