Hey there,
I have a vba procedure that determines the size of a dataset collected from another source, processes it, and builds embedded control charts for the user to view. All the formatting is set by code as well as the additon of the data series and labels. The graphs are set up right now to display the data on a line chart with a time-scale category axis. The category labels are set to named divisions of time (such as a shift colour) and are displayed after every 2nd tick mark. The categories have a predefined width, and the chart is made to widen as more datapoints are plotted.
What I'm trying to do now, is to display a second set of labels below (or above) the first on the same axis. These labels would denote the date on which the original labels fall (ex. Red shift - Jun 23).
I've found that Excel will do this to some extent just by selecting two rows containing the desired labels and setting those as the category labels (through Chart->source data). However, selecting the information this way causes Excel to "forget" the original tick mark spacing and labels, as well as extending the tick marks down to the bottom of the labels.
ie.
|---'---|---'---|---'---|---'---
Red _ _ Blue _ _ Green_ Red
Vs.
|---|---|---|---|---|---|---|---
|Jun| _ |Jun| __|Jun| _ |Jun|
|01 _ _ | 02 _ _ | 03 _ _| 04
| _ _ _ | _ _ _ _| _ _ _ _|
| Red _ | Blue _ | Green | Red
The date label gets compressed into representing only the first tick mark whereas the shift colour is spaced evenly between two. I'd like to have them both spaced evenly across the categories.
I can just turn off the tick marks and eliminate the lines, but this defeats the purpose of having them in the first place. Is there a way to programatically show both labels in the same fashion? The first label is set already through VBA from a range.
The end result would look like this:
|---'---|---'---|---'---|---'---
Red _ _ Blue _ _Green _Red
Jun 01 _Jun 02 _Jun 03 Jun04
I have a vba procedure that determines the size of a dataset collected from another source, processes it, and builds embedded control charts for the user to view. All the formatting is set by code as well as the additon of the data series and labels. The graphs are set up right now to display the data on a line chart with a time-scale category axis. The category labels are set to named divisions of time (such as a shift colour) and are displayed after every 2nd tick mark. The categories have a predefined width, and the chart is made to widen as more datapoints are plotted.
What I'm trying to do now, is to display a second set of labels below (or above) the first on the same axis. These labels would denote the date on which the original labels fall (ex. Red shift - Jun 23).
I've found that Excel will do this to some extent just by selecting two rows containing the desired labels and setting those as the category labels (through Chart->source data). However, selecting the information this way causes Excel to "forget" the original tick mark spacing and labels, as well as extending the tick marks down to the bottom of the labels.
ie.
|---'---|---'---|---'---|---'---
Red _ _ Blue _ _ Green_ Red
Vs.
|---|---|---|---|---|---|---|---
|Jun| _ |Jun| __|Jun| _ |Jun|
|01 _ _ | 02 _ _ | 03 _ _| 04
| _ _ _ | _ _ _ _| _ _ _ _|
| Red _ | Blue _ | Green | Red
The date label gets compressed into representing only the first tick mark whereas the shift colour is spaced evenly between two. I'd like to have them both spaced evenly across the categories.
I can just turn off the tick marks and eliminate the lines, but this defeats the purpose of having them in the first place. Is there a way to programatically show both labels in the same fashion? The first label is set already through VBA from a range.
The end result would look like this:
|---'---|---'---|---'---|---'---
Red _ _ Blue _ _Green _Red
Jun 01 _Jun 02 _Jun 03 Jun04
