Results 1 to 8 of 8

Thread: It takes two?

  1. #1
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts

    It takes two?

    Hi All,

    I found this neat code that will let you use VBA to switch the Tab on the ribbon. The code works great, well almost.
    If I call it from the Auto_Open() procedure in an Excel file it does not work. However, if I then press F8 and rerun the Auto_Open() procedure it does exactly what it is supposed to do, .i.e. select the Add-Ins tab. I've tried everything I can think of to insert delays into the process hoping that would do the trick but no luck. Any Ideas.

    Update: I just had a thought, amazing I know, but I was running this in compatibility mode, e.g. file type .xls. So I saved a copy as a .xlsm file and viola it works as it should. Now the only question is why when in compatibility mode does it work on the second run?
    Attached Files Attached Files
    Last edited by RetiredGeek; 2012-02-03 at 19:37.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  2. #2
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts
    Hi RG,

    I don't know the answer to your question, but thanks for pointing to this interesting topic!

    If anyone's interested, here's a link to the "Accessing the Ribbon with VBA" article by Tony Jollans. There's also a downloadable sample on that page.

    Gary

  3. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    I just love computers.

    This morning the code will not work on the initial load no matter what I do it only runs correctly on the second invocation. I'm getting balder by the minute.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Re the AutoOpen:
    Code:
       If CInt(Application.Version) > 11 Then SendKeys "%(x)", True
    Surely this should be <= 11 and not > 11?
    Code:
       If Application.Version = "14.0" Then SwitchTab "Add-Ins"
    For 2010, you do not need any of this code anyway as you can activate a tab directly in VBA, as long as you have obtained a reference to the IRibbonUI class in the onLoad callback.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Rory,

    Actually, they are both designed to do the same thing, I forgot to take out the earlier one which I put in with Office 2007 Ver 12.
    For 2010, you do not need any of this code anyway as you can activate a tab directly in VBA, as long as you have obtained a reference to the IRibbonUI class in the onLoad callback.
    Playing the noob here just how do I do that. I haven't a clue about the ribbon interface yet, I just finished figuring how to do everything I wanted with custom menus in 2003.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    See Ron's site here.
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Rory,

    Thanks for the link. From a quick read it looks like it will take a while to digest. Thanks again.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    It is well worth a read. Once you get the ideas, the Ribbon begins to actually make a lot of sense from a developer point of view.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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