Custom Ticklabels on x-axis possible?

[LufiaMaxim]

Hi,

I'm wondering if there's anyway I can add my own ticklabels to an axis in the form of an Array or anyway possible, don't really care how. I found the Chart.SeriesCollection(1).XValues accepts an array for the xvalues, but that doesn't apply for the ticklabels.

While I'm at it, I'll pose another question to what I'm actually trying to do. I've made a graph with VBA and it's worked wonderfully so far. They say a picture is worth a thosuand words, so I'll just do that:

The reason I want custom ticklabels is because I want to have 12:00 AM be changed to Midnight and 12:00 PM be changed to Noon.

Currently, I use the NumberFormat Property to get the values you see on this chart as follows:
numberFormat = "MMM dd" & vbCr & "hh:mm AM/PM"

I've tried searching all over the web for a way to get a custom formatting to show Midnight and Noon, basically a conditional formatting for NumberFormat property, but it doesn't seem to exist, understandably so.

How can I achieve my goal of converting the times to Noon & Midnight? Is it really with custom ticklabels, if that's possible, and if so, how do I do it?

Any help will be greatly appreciated.

LufiaMaxim

 

[Jerry Sullivan]

Hi LufiaMaxim,

One approach would be to add a column to your data range that would serve as the X-Axis Label text. If you reference that column instead of X-Axis raw data values range, it frees you up to format the labels however you want.

You can use a formula like the one shown below to build your X-Axis Label text from your raw data.

<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:174px;" /><col style="width:144px;" /><col style="width:79px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ccccff; text-align:center; ">X-Axis Raw Data Range</td><td style="background-color:#ccccff; text-align:left; ">X-Axis Label Range</td><td style="background-color:#ccccff; text-align:right; ">Y-Values</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">1/18/2011 12:00 AM</td><td style="text-align:left; ">Jan 18
Midnight</td><td style="text-align:right; ">65</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">1/18/2011 4:00 AM</td><td > </td><td style="text-align:right; ">204</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">1/18/2011 8:00 AM</td><td > </td><td style="text-align:right; ">280</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">1/18/2011 12:00 PM</td><td style="text-align:left; ">Jan 18
Noon</td><td style="text-align:right; ">180</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">1/18/2011 4:00 PM</td><td > </td><td style="text-align:right; ">202</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; ">1/18/2011 8:00 PM</td><td > </td><td style="text-align:right; ">87</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:right; ">1/19/2011 12:00 AM</td><td style="text-align:left; ">Jan 19
Midnight</td><td style="text-align:right; ">209</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:right; ">1/19/2011 4:00 AM</td><td > </td><td style="text-align:right; ">133</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="text-align:right; ">1/19/2011 8:00 AM</td><td > </td><td style="text-align:right; ">187</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="text-align:right; ">1/19/2011 12:00 PM</td><td style="text-align:left; ">Jan 19
Noon</td><td style="text-align:right; ">100</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B2</td><td >=IF(MOD<span style=' color:008000; '>(A2,1)</span>=0,TEXT<span style=' color:008000; '>(A2,"MMM dd")</span> & CHAR<span style=' color:008000; '>(13)</span> & "Midnight",IF<span style=' color:008000; '>(MOD<span style=' color:#0000ff; '>(A2,1)</span>=0.5,TEXT<span style=' color:#0000ff; '>(A2,"MMM dd")</span> & CHAR<span style=' color:#0000ff; '>(13)</span> & "Noon","")</span>)</td></tr></table></td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4

 

[Jon Peltier]

Jerry -

That approach will destroy the numerical nature of the X values. However, one can add a dummy XY series with points at every midnight and noon, hide the points (no markers), and add custom labels using your constructed labels. You can add custom labels manually, but Rob Bovey's Chart Labeler (http://appspro.com) is indispensable for doing lots of labels quickly.

There is an example of this technique in my tutorial Fake Line Chart (Dummy XY Series for X Axis).

 

[LufiaMaxim]

Thank you both for your input.

JS411, your method unfortunately will not work for me, since I don't necessarily have points on exactly midnight on noon. In fact, chances of points being on those times are extremely unlikely.

Jon, thanks for your input. I've been to your website many times, great site. Before I decide to look into Rob Bovey's XY Chart Labeler - can you use VBA with his program? This is an automated chart which allows the user to specify start and end dates and the graph will update accordingly. The only times times I would need to show Noon/Midnight would be if the number of days shown is <=5. After that, the x-axis will change to show just "MMM d" (or, just the month and day).

I'd prefer not downloading yet another program unless I'm sure I can make good use of it. So if you or someone who's used his program can verify whether you can use his program with VBA, I'd love to give it a shot.

Until then, I'll keep playing around with Excel and try your idea of adding a dummy XY series. Thanks

Lufia

 

[Jon Peltier]

I think Rob put hooks into the Labeler so you can call it from VBA, but it's not real hard to just code it. Something like this

For iPoint = 1 to srsLabels.Points.Count
  With srsLabels.Points(iPoint)
    .HasDataLabel = True
    .DataLabel.Position = xlLabelPositionBelow
    .DataLabel.Text = rLabelRange.Cells(iPoint)
  End With
Next

where srsLabels is the series just added to anchor the labels, rLabelRange is the range in the sheet that contains the text of each label, and iPoint is the loop counter that cycles through the points in srsLabels.

 

[LufiaMaxim]

I actually went ahead and took a look at the program anyway. :P

I've basically deduced that I need to do exactly what you just put in your sample code, so thanks for further clarifying that for me.

Currently working on another "feature" of the chart (a scrollbar that isn't based on a range in a worksheet, but simply changes the x-axis labels. The difficult part [for me at least] is that the interval of change is dependant on the date-range the user selected ), but I'll come back around to this later today.

Thanks Jon

 

[Jerry Sullivan]

Jerry -

That approach will destroy the numerical nature of the X values. However, one can add a dummy XY series with points at every midnight and noon, hide the points (no markers), and add custom labels using your constructed labels. You can add custom labels manually, but Rob Bovey's Chart Labeler (http://appspro.com) is indispensable for doing lots of labels quickly.

There is an example of this technique in my tutorial Fake Line Chart (Dummy XY Series for X Axis).

Jon,

I really appreciate your advice and explanation.

I incorrectly assumed this was a Line Chart using data points taken at regular time intervals.

Having very little experience with XY Charts, I found your website very helpful at illustrating the differences between them and Line Charts.

Thank you!