Command Bar ShowPopup No Longer Works in Excel 2013

[kleavitt95]

I built a macro in Excel 2010 that does a Vlookup from one spreadsheet to another, and when the macro is run, a popup box appears and lets the user click which tab they want the macro to perform the Vlookup on. I used the following code and it was simple and very effective.

Dim cbTool as CommandBar
Set cbTool = CommandBar("Workbook Tabs")
cbTool.ShowPopup

This worked perfectly until my boss upgraded to Excel 2013 and running this macro now returns the error "Method 'ShowPopup' of object 'CommandBar' failed." Clearly something in Excel 2013 has changed since it's still working in Excel 2010.

I've looked everywhere trying to find replacement code to achieve this in Excel 2013 but haven't had any luck. Any ideas what code I could use to pop up a list of all workbook tabs in the middle of a macro so the user can select a tab? Thank you!!

 

[Legacy 134911]

I don't think that code would work in any version. Try this:

Set cbTool = [B]Application.CommandBars[/B]("Workbook Tabs")

 

[Kenneth Hobson]

You just need an s.

Sub Main()
    Dim cbTool As CommandBar
    Set cbTool = CommandBars("Workbook Tabs")
    cbTool.ShowPopup
End Sub

or just

CommandBars("Workbook Tabs").ShowPopup