I N D E X menu via Tabs Popup

G

Greg

Hi,

I am using this code from Dave Haley’s free excel
newsletter (please refer to www.ozgrid.com )

Quote:
===================================================
“The other method that can be used, which is probably a
lot more user-friendly, is to manipulate the pop-up menu
displayed when you right click on any Cell in any
Worksheet. To this we can add our own menu item which
when clicked will show the standard Workbook Tabs Command
Bar. If you are not familiar with this Command Bar, you
can see it by right-clicking on your Sheet Scroll Tab
arrows on the bottom left of any Worksheet.

_____

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As
Object, ByVal Target As Range, Cancel As Boolean)
Dim cCont As CommandBarButton
On Error Resume Next
Application.CommandBars("Cell").Controls("Sheet
Index").Delete
On Error GoTo 0
Set cCont = Application.CommandBars
("Cell").Controls.Add _
(Type:=msoControlButton,
Temporary:=True)
cCont.Caption = "Sheet Index"
cCont.OnAction = "IndexCode"
End Sub

_____

To insert the code as shown above:

1. Open the your Workbook and then right click on the
Excel icon immediately to the left of the File Item on the
Worksheet Menu Bar and select View Code. Now enter the
code exactly as shown above

2. Go to Insert>Module to insert a Standard Module and
enter in the following code. This macro (IndexCode) is
called via the code we entered in Step 1 whenever a user
right clicks on any Worksheet.

Sub IndexCode()
Application.CommandBars("Workbook Tabs").ShowPopup
End Sub

===============================================
End of quotation.

When I shut the workbook where I have this code,
the “sheet index” control appears in every other workbook
upon rightclicking of a cell (Unless I close an
application completely). When clicked it shows the right
indexing for a given wkbook; however it opens the original
workbook (the one with the code) on the background.
Moreover, even though the original workbook has code, it
doesn’t give me the usual macro warning!

Do you know why this is happening and what is the
workaround?

Thanks,
Greg
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top