Navigate to worksheet using Ribbon dropdown menu

jeffreybrown

Well-known Member
Joined
Jul 28, 2004
Messages
5,145
I found DropDown.zip on Ron de Bruin's site which adds a dropdown to the ribbon and holds values from a worksheet range. I thought I could use these values to naviagate to a worksheet with the same name. At the end the the procedure there is a MsgBox which returns...

The variable MySelectedItem has the value "Item 11" and now can be used in other code.

...but I get a run-time error 91

Code:
Object variable or With block variable not set

Code:
Sub wsNavigate()
    Sheets(MySelectedItem).Select
End Sub

When I don't add the above (Sub wsNavigate) the variable returns MySelectedItem = "Item 11"

Any thoughts on how I am not calling the variable correctly to use a sheet navigation or is this the wrong use?
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,378
As a test, this works if there is a sheet actually named Item 11
Code:
Sub wsNavigate()
    MySelectedItem = "Item 11"
    Sheets(MySelectedItem).Select
End Sub

Make sure the spelling of the sheet name is exactly Item 11 with no trailing spaces. Also make sure MySelectedItem doesn't have a trailing space. You might want to use...
Code:
   Sheets(Trim(MySelectedItem)).Select
 

jeffreybrown

Well-known Member
Joined
Jul 28, 2004
Messages
5,145
Thanks AlphaFrog,

A bit of an oversight on my part and you are right I had the sheet name spelled wrong on the tab. Instead of Item 11 I had Item11. Thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,329
Members
414,055
Latest member
mcarduner

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top