+ Reply to Thread
Results 1 to 9 of 9

VBA Chart Axis editing

  1. 11-04-2011, 09:47 AM #1
    theDirtyHarry
    theDirtyHarry is offline
    Registered User
    Join Date
    11-04-2011
    Location
    Linz
    MS-Off Ver
    Office 2010
    Posts
    13

    Question 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:

    Please Login or Register  to view this content.
    (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

    Please Login or Register  to view this content.
    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?

    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:

    Please Login or Register  to view this content.
    In Excel 2010, I can only get this to work with something like that:

    Please Login or Register  to view this content.
    Is there a workaraound?
    Last edited by theDirtyHarry; 11-09-2011 at 10:57 AM. Reason: [SOLVED]
    Reply With QuoteRegister To Reply
  2. 11-04-2011, 10:07 AM #2
    Andy Pope
    Andy Pope is offline
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,482

    Re: VBA Chart Axis editing

    Series name
    Please Login or Register  to view this content.
    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.
    Cheers
    Andy
    www.andypope.info
    Reply With QuoteRegister To Reply
  3. 11-07-2011, 06:29 AM #3
    theDirtyHarry
    theDirtyHarry is offline
    Registered User
    Join Date
    11-04-2011
    Location
    Linz
    MS-Off Ver
    Office 2010
    Posts
    13

    Re: VBA Chart Axis editing

    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!
    Reply With QuoteRegister To Reply
  4. 11-07-2011, 06:55 AM #4
    Andy Pope
    Andy Pope is offline
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,482

    Re: VBA Chart Axis editing

    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.

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  5. 11-07-2011, 10:08 AM #5
    theDirtyHarry
    theDirtyHarry is offline
    Registered User
    Join Date
    11-04-2011
    Location
    Linz
    MS-Off Ver
    Office 2010
    Posts
    13

    Re: VBA Chart Axis editing

    I used

    Please Login or Register  to view this content.
    as you suggested and it works like a charm. Thank you very much!
    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

    Please Login or Register  to view this content.
    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 with

    Please Login or Register  to view this content.
    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.

    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...
    Reply With QuoteRegister To Reply
  6. 11-07-2011, 10:49 AM #6
    Andy Pope
    Andy Pope is offline
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,482

    Re: VBA Chart Axis editing

    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.
    Reply With QuoteRegister To Reply
  7. 11-08-2011, 01:09 PM #7
    theDirtyHarry
    theDirtyHarry is offline
    Registered User
    Join Date
    11-04-2011
    Location
    Linz
    MS-Off Ver
    Office 2010
    Posts
    13

    Exclamation Re: VBA Chart Axis editing

    Well, I have found something interesting:
    While 

    Please Login or Register  to view this content.
    gives me no result at all, both

    Please Login or Register  to view this content.
    as well as

    Please Login or Register  to view this content.
    work as intended. How can that be the intended functionality? Is there any way to ask / tell Microsoft direktly?
    Reply With QuoteRegister To Reply
  8. 11-09-2011, 10:22 AM #8
    Andy Pope
    Andy Pope is offline
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,482

    Re: VBA Chart Axis editing

    I can verify the ineffective superscript code.

    A bug has been reported to MS.
    Reply With QuoteRegister To Reply
  9. 11-09-2011, 10:49 AM #9
    theDirtyHarry
    theDirtyHarry is offline
    Registered User
    Join Date
    11-04-2011
    Location
    Linz
    MS-Off Ver
    Office 2010
    Posts
    13

    Thumbs up Re: VBA Chart Axis editing

    Thanks for confirming and reporting the bug!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1