Right click sheet menu

**DesM** · 09-14-2007, 12:10 PM

Would anyone have code to allow the sheets in a workbook to be selected from the right click menu?
Des M

**Ron Coderre** · 09-14-2007, 01:30 PM

You can right-click on the |<< >>| buttons to the left of the sheet tabs for a list of sheets you can select. Did you want different functionality?

**DesM** · 09-15-2007, 02:59 AM

Thanks, but I know about the right click on <<>>. It would be better for me to have the selection list on the right click menu when I right click anywhere.
Des M

**royUK** · 09-15-2007, 03:10 AM

There's a free addin to create a toolbar that allows navigation to sheets here

http://www.excel-it.com/vba_examples.htm

**DesM** · 09-15-2007, 11:30 AM

Thanks Roy.
I downloaded the addin and it's good but I would like to be able to select from the right click menu because it is faster than having to pick from a dropdown.
DesM

**Greg M** · 09-15-2007, 01:38 PM

Hi there,

Have I understood correctly - you want to be able to right-click anywhere on a worksheet, display a dropdown list containing the names of all the visible worksheets, and then move automatically to the selected one?

Regards,

Greg M

**royUK** · 09-15-2007, 03:02 PM

H
Here you are

The code below should be placed in Private Module of the Workbook Object (ThisWorkbook); right click on the Excel icon, top left next to File and choose View Code.

Please Login or Register  to view this content.

This is the navgate code, place it in a Standard module

Please Login or Register  to view this content.

**DesM** · 09-16-2007, 06:35 AM

RoyUK, thanks indeed for this and it works perfectly.
Besides solving my problem I'm trying to use it to improve my VBA. I have managed to put a divider line on top of the first sheet entry (and not get it above them all. I used IF and ws.index).
I'm now trying to put a tick mark beside the selected sheet. I have managed to get the tick mark to show (CommandBars.ActionControl.State = msoButtonDown) but I can't get it to cancel when I select another sheet. Any hints?

Greg M: thanks for replying. I was just about to respond when I got RoyUK's answer which solved the problem.

Thanks again, DesM

**royUK** · 09-16-2007, 07:43 AM

I have amended the code, the code below should be placed in Private Module of the Workbook Object (ThisWorkbook); right click on the Excel icon, top left next to File and choose View Code.

Please Login or Register  to view this content.

In a Standard Module

Please Login or Register  to view this content.

**Greg M** · 09-16-2007, 08:04 AM

Hi Roy,

Many thanks for that very interesting code.

May I suggest the following small addition to the "ThisWorkbook" module:

Please Login or Register  to view this content.

This will ensure that the "Selected Sheet" checkbox is updated even if the user navigates via the worksheet tabs or Ctrl+Page Up/Down.

Best regards,

Greg M

**royUK** · 09-16-2007, 08:11 AM

Good idea Greg

**DesM** · 09-16-2007, 08:27 AM

Roy, thanks for the revised code. I see that it puts a tick box in the menu items. Is it possible to just use the msobuttonup/down to place a tick beside the item?
I think that to remove them I should loop through the items in the menu making its "state" = msobuttonup. Can't figure out how to look through the menu items.
However, thanks very much for your help.
Greg, thanks too for that extra bit of code.
Des