Hello DesM,

Here is the code for a macro that adds a Drop Down List to Excel's Cel
Popup Menu. The menu you see whenever you Right Click a cell on
Worksheet. The macro updates the list whenever a new Worksheet is adde
or deleted.

Selecting a Worksheet from the Drop Down List brings that Worksheet t
the top. You can switch Worksheets quickly and easily using this Popu
control.


Code
-------------------

Public Sub AddWorksheetList()
'This macro installs the Drop Down List control on the Cell Popup Menu

Dim cbCell As CommandBar
Dim ctlDropDown As CommandBarControl
Dim Wks

On Error Resume Next
Excel.CommandBars("Cell").Controls("Go To Worksheet").Delete
Err = 0

Set cbCell = Excel.CommandBars("Cell")
Set ctlDropDown = cbCell.Controls.Add(msoControlDropdown)

For Each Wks In ThisWorkbook.Worksheets
ctlDropDown.AddItem Wks.Name
Next Wks

With ctlDropDown
.Caption = "Go To Worksheet"
.OnAction = "GoToWorksheet"
.BeginGroup = True
End With

End Sub

Sub GoToWorksheet()
'This macro Activate the selected Worksheet and updates the list
Dim cbCtl As CommandBarControl
Dim Wks

Set cbCtl = Excel.CommandBars("Cell").Controls("Go To Worksheet")
If ThisWorkbook.Worksheets.Count < cbCtl.ListCount Then
cbCtl.Clear
For Each Wks In ThisWorkbook.Worksheets
cbCtl.AddItem Wks.Name
Next Wks
MsgBox "Worksheet List has been Updated" & vbCrLf & "Please Re-select a Worksheet"
Exit Sub
End If

Wks = cbCtl.Text
If Wks < "" Then
ThisWorkbook.Worksheets(Wks).Activate
End If

End Sub

-------------------


INSTALLING THE MACROS

-Copying the Macros... -
1) Go to the Code Window above and Scroll to the Top of the code.
2) Place the mouse pointer to the Left of the First Lne.
3) Left Click and Hold the Mouse Button Down.
4) Move the mouse to Last Line of code and Release the Left Mous
Button.
5) The Code should all be highlighted.
6) Press and Hold the *CTRL* key and then press the *C* key. Thi
copies the code.

-Inserting a Project Module... -
1) With an Excel Workbook Open, press and hold the *ALT* key and the
the *F11* Key. This starts the VBA Editor.
2) Press and hold the *ALT* key and then the *I* key. This activate
the Insert Menu.
3) Press the *M* key. This inserts a project module into you
Workbook.
4) Press and hold the *CTRL* key and then press the *V* key. This wil
paste the macro code into the module.
5) Press and hold the *CTRL* key and then press the *S* key. This wil
Save the code.

-Adding the Macro to the Popup Menu... -
1) Press and hold the *ALT* key and then press the *Q* key. This take
you back to Excel.
2) Press and hold the *ALT* key and then press the *F8* key. Thi
brings up the Macro list.
3) Find -AddWorksheetList-.
4) Move the mouse pointer to it and Left Click to select it.
5) Press the *Enter* key to Run the macro. This installs it.
6) Press and hold the *CTRL* key and then press the *S* key. This Save
the menu change.

That all there is to it. You're done.

Sincerely,
Leith Ros

--
Leith Ros
-----------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...fo&userid=1846
View this thread: http://www.excelforum.com/showthread.php?threadid=48087