Open Workbook on Specific Tab in Excel 2007/10

Topic

New Reply

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!
Computer Specs

Reply | Quote

Replies

You would have to use SendKeys (sending Alt X).

Reply | Quote

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!
Computer Specs

Reply | Quote

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

Reply | Quote

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!
Computer Specs

Reply | Quote

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.

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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?

Reply | Quote

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!
Computer Specs

Reply | Quote

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

Reply | Quote

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!
Computer Specs

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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!
Computer Specs

Reply | Quote

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

Reply | Quote

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!
Computer Specs

Reply | Quote

Hi RetiredGeek

OK. Got it now.
My problem was if I have NO Excel running and I double-click the Cars-Test.xls file in Explorer, then on my system it loads the file into Excel2003 rather than Excel2007 or Excel2010.

I was selecting the file from the ‘File-Recent-Recent Workbooks’ list in Excel2010.

When using the menu to quit the application, the .Quit with .DisplayAlerts = False is a bit severe if you were working on another Excel file.
You lose your work.
I try to avoid using .Quit for this reason.
(It is however possible to check the count of Excel files that are ‘open’, and perhaps .Quit can be used after checking this).

zeddy

Reply | Quote

(It is however possible to check the count of Excel files that are ‘open’, and perhaps .Quit can be used after checking this).

Zeddy,

Great Idea! Thanks.

Update:
Here’s the code to implement your idea.

Code:

       iWkBkCnt = Workbooks.Count
       If iWkBkCnt > 2 Then   '*** Allows for Personal.xls ***
         KillVehicleMenu
         ActiveWorkbook.Close
       Else
         .Quit
       End If

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

In my version of Excel 2010, both {ENTER} and {RETURN} are valid keys for the Application.SendKeys Method.

I needed to move a custom tab that I’d already created. To move an existing tab to the left of “Home”, right click the ribbon, click “Customize the Ribbon”, highlight the desired tab, and keep clicking the “up arrow” on the right till the tab is in front of “Home”.

Reply | Quote

In my version of Excel 2010, both {ENTER} and {RETURN} are valid keys for the Application.SendKeys Method.

I needed to move a custom tab that I’d already created. To move an existing tab to the left of “Home”, right click the ribbon, click “Customize the Ribbon”, highlight the desired tab, and keep clicking the “up arrow” on the right till the tab is in front of “Home”.

Dave,

I guess the book I’m using is a bit out of date {1998} VBA/VB 6 is rally that old!

In Excel 2007 I don’t get an option for “Customize the Ribbon” when I right-click the ribbon. Geez, am I gonna hafta upgrade again.

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

If you have created your own tab, you can position it where you like using the CustomUI xml part (insertBeforeMso=”TabHome”)

Reply | Quote