Navigate to worksheet using Ribbon dropdown menu

jeffreybrown

Well-known Member
Joined
Jul 28, 2004
Messages
5,147
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?
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,450
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,147
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,206
Messages
5,835,981
Members
430,398
Latest member
Wookiee_

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