VBA: Some series in chart have wrong x values

Cross-post notice: This question was originally posted at Dream In Code. I apologize for the cross-posting, but I fear Dream In Code is really not the right place for the question; this forum seems more appropriate. I got some help there with my VB syntax, but that forum is not so Excel oriented.

I also apologize if this post should have been on the Charting forum. I couldn't decide, but since this is VBA-related, this forum seemed more appropriate to me.

I'm a Java programmer by trade, but for a personal project, I've been working on a weight-management application in Excel. (I'm using Excel 2010, in case that's relevant here, but I'd like it to work in Excel 2007 as well.) I have a scale that measures total weight and weight of fat, so I keep track of those values daily and the application computes a trend based on each of those values. At the end of each month, I want to create a chart tracking all four: weight, weight trend, fat, and fat trend.

In personal.xlsb, I have the following macro:

Please Login or Register  to view this content.

Weight.xlsm contains the MonthlyChart macro:

Please Login or Register  to view this content.

Weight.xlsm also contains the referenced UpdateHistory function:

Please Login or Register  to view this content.

Here's the deal: Series 1 and 2 on the chart (weight and weight trend) properly show dates for the x values when you hover over a point on the chart. Series 3 and 4 (fat and fat trend) show dates for the x values if weight-history.xlsx is open, but show integer values (e.g. 40673 instead of 5/10/2011) if weight-history.xlsx is closed.

Is there something I'm doing wrong here or does this seem to be an Excel issue?

Thanks

Looks like this is an excel issue with data from a closed workbook.

You can get around it by explicitly formatting the primary and secondary category axes number format to that of the requried date format.

Thanks, Andy. Now just one more question: how do I do that?

The thing is that the data is formatted properly in the weight-history workbook and is formatted properly when displayed along the axis. I only have this problem if I hover over a point in series 3 or 4. You can see what I'm talking about by looking at the attached images. I've tried researching how to format just what displays when you hover over a point, but either the information isn't out there, is really hard to find, or is something that I don't know how to find.

I've only just realized while creating those screen shots that my series names aren't coming through, either. Those worked in an earlier version of the macro, so I'm sure I can fix that one on my own.

Thanks again.

[Edit] I was right; fixing the series names was easy; I simply had to apply the chart template before assigning names to the series. [/Edit]

Select and format the horizontal axis. Change the Number format to the required date format.

Add the secondary horizontal axis via the Layout tab. Format this to use the same number format.

I think you need to dive more into Excel's possibilities:

Suggestion:

- open the workbook "E:\WeightUtils\Weight.xlsm"
- put the macrocode of macro 'monthlychart" in then Workbook_open event or in a 'workbook autoopen' macro:
- most of your code in 'Sub CreateMonthlyChart()' is redundant; this suffices:

Please Login or Register  to view this content.

Originally Posted by Andy Pope

Select and format the horizontal axis. Change the Number format to the required date format.

Add the secondary horizontal axis via the Layout tab. Format this to use the same number format.

Of course! Here I was searching for a VBA solution and all I had to do was this formatting for the chart template. Thanks!

Thanks for the input, snb. Andy's solution works beautifully for me, though, so I'm going that way with it. I appreciate your willingness to help, though.

The simpler code you suggest is quite elegant, so thank you for that. I wasn't sure how to get the path of the current workbook; now I know.