VBA Chart Axis editing
Hi there,
I am currently trying to transfer my Excel 2003 scipts to my new PC and Excel 2010. I am having few problems though, one of them is the following:
It seems to be no longer possible to format an axis title of a graph partly in superscript. What I want to do is to make Excel create a new graph with VBA, and in this process I also want to define the axis titles. One of them should read "unitname / s-1" where the "-1" part should be superscript. In Excel 2003 this worked fine with the following code:
(Excuse the excessive use of "With" loops, I have several other things going on there in between as well.)
This no longer seems to work. What I have found is that if I set the last command to
it leads to the whole word (not just 2 characters!) being superscripted. However, this is not what I am looking for. Has anybody an idea what I could do here?.AxisTitle.Characters(Start:=13, Length:=2).Font.Superscript = True
Also, the xlCenter command does not lead to any visible result neither.
Harry
PS: On a side note, I had to find out that obviously it is no longer ok to define the name of a dataset with something like this:
In Excel 2010, I can only get this to work with something like that:ActiveChart.SeriesCollection(1).Name = ActiveSheet.Range("A1:A3")
Is there a workaraound?ActiveChart.SeriesCollection(1).Name = ActiveSheet.Range("A1")
Last edited by theDirtyHarry; 11-09-2011 at 09:57 AM. Reason: [SOLVED]
Series name
For me the superscript code works in xl2007 and xl2010. I'm sure this was an issue at one point so it maybe worth checking you have the latest patches and service packs applied.activechart.SeriesCollection(1).name = "='" & activesheet.name & "'!" & activesheet.range("A1:a3").address
Hello Andy,
Thanks for answering my post.
I am using a corporate PC, therefore I have no option to manually force updates to install. As far as I can see though, it seems as if my versions of Windows and Office are up to date.
Concerning your code snippet I am afraid I cannot understand it fully. What should I use the Series name for? Could you please refine your answer?
Thanks a lot!
So has your IT dept. updated you to SP1 fr Excel 2010?
Where you have a problem using multiple cells for the name range try using the code I posted instead of the code you posted, which was.
ActiveChart.SeriesCollection(1).Name = ActiveSheet.Range("A1:A3")
I used
as you suggested and it works like a charm. Thank you very much!activechart.SeriesCollection(1).name = "='" & activesheet.name & "'!" & activesheet.range("A1:a3").address
The problem with superscripting is persistent though. My Excel version number starts with 14.0.6106 so I suppose I am using a post SP1 version.
I did find a workaround in the meantime, but it is not entirely satisfying.
If I use
I get the results I want. As you can see I am no longer using .Font.Superscript but .BaselineOffset. I found this when I recorded a macro and used it in my VBA code. This is not what seems to be the decisive change though - if I use it in the same With loop as I used .Font.Superscript in my initial post, it does not work either. Only if I place the command out of the innermost With loop the appropriate characters get superscripted, as intended. However, the problem with this is that now I superscript characters after aligning the axis title withWith ActiveSheet.ChartObjects(i) With .Chart With .Axes .HasTitle = True .AxisTitle.Caption = "unitname / s-1" .AxisTitle.HorizontalAlignment = xlCenter End With .Axes(xlValue, xlPrimary).AxisTitle.Select Selection.Format.TextFrame2.TextRange.Characters(14, 2).Font.BaselineOffset = 0.3 End With End With
and while this may not be a visible problem when only superscripting a few characters, it might get irritating as soon as a larger number is superscripted and therefore the dimension of the axis name is getting shorter. This is nitpicking, I know, but I am still curious where this behaviour might come from..AxisTitle.HorizontalAlignment = xlCenter
On another side note, changing the value of .AxisTitle.HorizontalAlignment seems not to really have an effect either. Neither using xlTop nor xlJustify changed the appearance of my graphs. I am slowly getting afraid my whole VBA code is pretty useless as a whole...
Post SP1 wil have the text SP1 in full the version details.
The charting engine and OM charts a great deal between 2003 and 2007. Whilst some if not most of the coding will work you will need to test and adapt any chart and or shape code you have.
Re: VBA Chart Axis editing
Well, I have found something interesting:
While
gives me no result at all, bothActiveSheet.ChartObjects(i).Chart.Axes(1).TickLabels.Font.Superscript = True
as well asActiveSheet.ChartObjects(i).Chart.Axes(1).TickLabels.Font.Subscript = True
work as intended. How can that be the intended functionality? Is there any way to ask / tell Microsoft direktly?ActiveSheet.ChartObjects(i).Chart.Axes(1).TickLabels.Font.Strikethrough = True
I can verify the ineffective superscript code.
A bug has been reported to MS.
Re: VBA Chart Axis editing
Thanks for confirming and reporting the bug!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks