Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  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
    I have a lot of Excel workbooks that when opened hide the standard user interface and present the user with a custom menu of only the functions I want used. When these workbooks are opened in Excel 2007, and I assume 2010, the custom menu is placed on the Add-ins tab and the normal ribbon is displayed. Does anyone have code I could put in the Auto_Open() routine to automatically select the Add-ins tab?

    BTW: The files are .XLS NOT .XLSM and I wish them to stay that way.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  2. #2
    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
    You would have to use SendKeys (sending Alt X).
    Regards,
    Rory

    Microsoft MVP - Excel

  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
    Rory,

    Makes sense but I can't seem to get it to work. Here's my code.
    Code:
    Sub Auto_Open()
    
       VehicleMenu
       Sheets("Fit").Activate
       SendKeys "%x", True
       
    End Sub                   'Auto_Open()
    I've tried both the Upper case X and the False argument to no avail.
    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
    SendKeys is of course not particularly reliable, but this worked for me (once I realised I needed to install an add-in that made the add-ins tab visible!)

    Code:
    Sub Auto_Open()
    
       SendKeys "%X~", True
       Sheets("Fit").Activate
       
    End Sub

    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,

    Ah! the tilde! That did it. Don't know why it needs an Enter Key but that solved the problem...a thumbs up for you....
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #6
    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
    Well, I thought this problem was solved but....
    If I run my Auto_Open() code manually {Alt+F8} it works fine.
    However, when I open the workbook from the jump list it does not work.
    I've tried converting it to an .xlsm file and still no love.
    Any Ideas....file attached.

    BTW: I've set a breakpoint and the code does execute! It just doesn't change the active tab on the ribbon.
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #7
    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
    I did say it wasn't reliable...

    If you can convert to 2007 format, you can just create your own tab and make it the first one on the Ribbon?

    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    I opened a new instance of Excel2007, opened your file directly and it worked fine, menu created automatically etc.
    I opened a new instance of Excel2010, opened your file directly and it worked fine.

    I closed the 'new instances', went back to my already opened Excel2010 (with other files already opened), did a file-open of your file, and it worked fine.
    Menu created.
    When I closed your file, the Vehicles menu stayed, and obviously debugged when a menu option was selected ('Add Entry')

    So, apart from anything else, perhaps a 'remove menu cleanup' would be required perhaps using the 'before close event'?

    zeddy

  9. #9
    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
    Quote Originally Posted by zeddy View Post
    I opened a new instance of Excel2007, opened your file directly and it worked fine, menu created automatically etc.
    I opened a new instance of Excel2010, opened your file directly and it worked fine.

    I closed the 'new instances', went back to my already opened Excel2010 (with other files already opened), did a file-open of your file, and it worked fine.
    Menu created.
    When I closed your file, the Vehicles menu stayed, and obviously debugged when a menu option was selected ('Add Entry')

    So, apart from anything else, perhaps a 'remove menu cleanup' would be required perhaps using the 'before close event'?

    zeddy
    Zeddy,

    Thanks for the reply but the menu was not the problem it was getting the Add-in Tab to be selected.

    It was interesting that in 2010 the menu persisted as it was set up as follows:
    Code:
       Set newMenu = myMenuBar.Controls.Add(Type:=msoControlPopup, temporary:=True)
    And should not, and does not in 2007 or less, remain after the workbook was closed.
    Is this a change in 2010?
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  10. #10
    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
    Quote Originally Posted by rory View Post
    I did say it wasn't reliable...
    Rory,

    Yes you did. But I still don't understand why it would work, 100% of the time, when run manually and not, also 100%, when the workbook is loaded. I don't like it when things aren't consistent especially in software!

    More Info: If Excel is already running, e.g. You start Excel w/o loading a file then you use File Open to load the workbook the SendKeys works. If you run the Auto_Open() macro manually after loading the file the SendKeys works. However, if the file is loaded automatically via the Jump List, Double-clicking on the file in Explorer or from a Desktop shortcut which loads Excel and passes the file the SendKeys does NOT work

    Also: to send a key combo, e.g. hold down Alt and press x you should code SendKeys "%(x)" according to "VB & VBA in a Nutshell" pg. 509.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  11. #11
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi RetiredGeek

    Re: Thanks for the reply but the menu was not the problem it was getting the Add-in Tab to be selected.

    I should have said: I opened a new instance of Excel2007, opened your file directly and it worked fine, menu created automatically , and the Add-in Tab was selected

    I opened your file again this morning in an Excel2010 session I already had running (with other workbooks already open).
    When your file was opened, the Add-in Tab was selected and the custom menu visible.

    When I closed the Cars workbook, the Add-in tab was still selected and the custom menu still visible.

    zeddy

  12. #12
    New Lounger
    Join Date
    Dec 2009
    Location
    San Diego, CA, USA
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I tried to start Excel with a ribbon tab other than "Home" active. "Application.SendKeys ("%R{RETURN}")" worked just fine in a test spreadsheet, but didn't work in an Auto_Open macro. Moving the tab I wanted active to the left of the "Home" tab solved the problem.

  13. #13
    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
    Quote Originally Posted by SDdave View Post
    I tried to start Excel with a ribbon tab other than "Home" active. "Application.SendKeys ("%R{RETURN}")" worked just fine in a test spreadsheet, but didn't work in an Auto_Open macro. Moving the tab I wanted active to the left of the "Home" tab solved the problem.
    Dave,

    I think you meant %R{Enter} as there is no {Return} designation in the documentation?

    How did you move the Add-in tab to the left of the Home tab?

    More interesting info: SendKeys will select any other tab from the Auto_Open() macro EXCEPT the Add-in tab!
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  14. #14
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Re: More interesting info: SendKeys will select any other tab from the Auto_Open() macro EXCEPT the Add-in tab!

    I replied previously:
    I opened a new instance of Excel2007, opened your file directly and it worked fine, menu created automatically , and the Add-in Tab was selected

    I also had the Add-in Tab automatically selected whenever I opened your Cars file in Excel2010

    But every time I close your Cars file, whether in Excel2007 or Excel2010, the custom menu does NOT remove itself.

    zeddy

  15. #15
    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
    zeddy,

    I opened a new instance of Excel2007, opened your file directly and it worked fine, menu created automatically , and the Add-in Tab was selected.

    I also had the Add-in Tab automatically selected whenever I opened your Cars file in Excel2010
    From previous post:

    If Excel is already running, e.g. You start Excel w/o loading a file then you use File Open to load the workbook the SendKeys works. If you run the Auto_Open() macro manually after loading the file the SendKeys works. However, if the file is loaded automatically via the Jump List, Double-clicking on the file in Explorer or from a Desktop shortcut which loads Excel and passes the file the SendKeys does NOT work

    But every time I close your Cars file, whether in Excel2007 or Excel2010, the custom menu does NOT remove itself.
    The application is designed to exit Excel via the last menu option. Exiting Excel removes the menu.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Page 1 of 2 12 LastLast

Posting Permissions

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