I'm working on editing macros and need help with coding macros for the x-axis of a graph. The same macros was used from Excel 2003 to Excel 2007 which caused the problem. Does anybody know why macros for the X-axis of a chart would not work in Excel 2007, but work in Excel 2003?
Because charts and shapes in xl2007 are completely new and the OM has changed and may not be as complete.
If you post your code and detail the problem perhaps we can help
Hopefully, with the code below you can help me find the problem to why only the value 0:00 gets outputted on the charts and the values from 1:00-23:00 does not.
In one sub, the code that refers to x-axis values is:
ActiveChart.SeriesCollection(1).XValues = "=Sheet1!R4C3:R4C" & numCols
Please Login or Register to view this content.
In another sub, i think this is the code that refers to the x-axis:
Please Login or Register to view this content.
Last edited by VBA Noob; 05-27-2008 at 03:38 PM.
Can you post a workbook example.
The workbook is to big for me to attach it. Would I be able to email it to you?
Last edited by ZavNice; 05-27-2008 at 11:48 AM.
Can you not make an example?
otherwise andy AT andypope DOT info
I sent it to your email
If you select the main chart sheet and use the Select Data option you will see the category axis only has text for 00:00
It appears that the time slots is static information so just set the reference using the Edit button.
how would i make the changes with visual basic, without recording it?
Please Login or Register to view this content.
Thank You so much! Do you know why this problem occured in 2007, but not in 2003 when the same macro was used?
It must have something to do with the way the information is displayed as just setting series 1 was not enough, as it would have been for xl2003.
Oh okay. And one more question Expert Programmer
Why am I getting a run-time error for method 'cells' of object '_Global' failed for the following code? :
I thought I fixed it by doing this:Please Login or Register to view this content.
But instead it gave my time intervals a weird formula on the charts. So, I'm not sure why I'm getting an error.Please Login or Register to view this content.
Try this
The error is because you were not qualifying the Cells reference. And Sheet1 was probably not the active sheet.Please Login or Register to view this content.
Oh okay. Thanks
I have just had a look at your code in more detail.
In the routine Chart_Click1 you have a variable that is not defined or set a value. numcols is zero where as it should be 24.
You have added On Error Resume Next which is hiding all the bad codes you have.
Once you correct that the axis labels will work as expected.
I'm new to Visual Basic so this is why I'm asking all these questions.
So the variable that isn't defined is numCols? And how would I set numCols to 24? Would it be like this:And what should I replace with On Error Resume Next?Please Login or Register to view this content.
whilst using On Error is fine for certain situtations you should not use it just to avoid sorting out the mistakes in your code.
The assignment of the value to the variable is correct.
I will check the rest of your coding later tonight.
okay thanks for taking time out to look at it for me
Try this revision.
I used the Node and Date textbox to hold both the heading and information. Plus I renamed these textboxes in order to avoid coding confusion as it appears there were duplicate controls.
Please Login or Register to view this content.
I'm getting an run time error for this line of code:The error says "Unable to set the Background property of the Font class"Please Login or Register to view this content.
what version of xl are you running ?
Microsoft Excel 2007
Then simply comment out the 2 occurances of that code as the textboxes do not support that property.
By commenting that out it gives me more errors. Is that the only way I would be able to output the date, by changing all that code?
The changes to the code address the errors that were already in your code masked by the On Error Resume Next statement.
A simple copy and paste will change all that code in a few keystrokes.
I also emailed you your original file. I could post it here but I was unsure whether the content was sensitive.
Of course you can revert back to your original code and error trapping and just fix the numcol variable
Okay. I got your email of the file. Thanks. And are you saying that the original code was already error trapped or I could make changes to error trap?
The original code had Resume On Error.
If you comment that out you would find it had multiple errors.
Oh okay. And when I make an assignment to numCols, I put that in the sub Chart_Click1(index As Integer, cpuRow As Integer) right?
Yes.
You can use CTR+F in VBE to check all the versions of the code I have posted and sent.
Is there anyway you can think of that I can place the dates on the Main Sheet under the graphs, to be put in "Text Box 4"? So the date would change according to which graph I selected.
The code already does that.
As I stated I changed the name of the textbox because you had to with the same name and the code was erroring.Please Login or Register to view this content.
Thank You for all of your help. You guided me through this. I was able to use some of the code you gave me and was able to alter it to fit all my needs. If I need anymore help I know who to go to. Thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks