ExcelBanter - Menu of sheet names

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Menu of sheet names (https://www.excelbanter.com/excel-programming/344395-menu-sheet-names.html)

Menu of sheet names

I am trying to write some code which would construct a menu item of all
the sheets in the workbook. I am new to VBA and I know it will take me
hours.
Has anyone done something like this before?
Even better, does anyone know how to increase the number of sheets
shown when you left click on the arrow buttons at the left of the sheet
tabs?
Des

--
DesM
------------------------------------------------------------------------
DesM's Profile: http://www.excelforum.com/member.php...o&userid=24121
View this thread: http://www.excelforum.com/showthread...hreadid=480871

Menu of sheet names

This may help you:

Sub ShowSheetList()
On Error Resume Next
Err = 0
'Try to show dialog for 15 sheet workbooks
CommandBars("Workbook Tabs").Controls("More sheets...").Execute
'If an error there are fewer than 16 sheets so show the "short list"
If Err < 0 Then CommandBars("Workbook Tabs").ShowPopup
End Sub

--
Jim
"DesM" wrote in message
...
|
| I am trying to write some code which would construct a menu item of all
| the sheets in the workbook. I am new to VBA and I know it will take me
| hours.
| Has anyone done something like this before?
| Even better, does anyone know how to increase the number of sheets
| shown when you left click on the arrow buttons at the left of the sheet
| tabs?
| Des
|
|
| --
| DesM
| ------------------------------------------------------------------------
| DesM's Profile:
http://www.excelforum.com/member.php...o&userid=24121
| View this thread: http://www.excelforum.com/showthread...hreadid=480871
|

Menu of sheet names

Take a look at David McRitchie's BuildTOC:

http://www.mvps.org/dmcritchie/excel/buildtoc.htm

In article ,
DesM wrote:

I am trying to write some code which would construct a menu item of all
the sheets in the workbook. I am new to VBA and I know it will take me
hours.
Has anyone done something like this before?
Even better, does anyone know how to increase the number of sheets
shown when you left click on the arrow buttons at the left of the sheet
tabs?
Des

Menu of sheet names

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

Menu of sheet names

to: Leith Ross
Thanks indeed for this.
Is it possible to expand the size of the pulldown window to avoi
having to scroll?
Regards, Des Murph

--
Des
-----------------------------------------------------------------------
DesM's Profile: http://www.excelforum.com/member.php...fo&userid=2412
View this thread: http://www.excelforum.com/showthread.php?threadid=48087

Menu of sheet names

Hello DesM,

There maybe a way to increase the size, but I haven't discovered i
yet.

Sincerely,
Leith Ris

--
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

Menu of sheet names

Hello DesM,

There maybe a way to increase the size, but I haven't discovered i
yet.

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

Menu of sheet names

To:Leith Ross
Sorry, but I've made a mess. I ran the macro several times and now
have many "Go to Workbook" pull down controls on my right clic
window.
Even when I close Excel and open a clean workbook they are there.
Can I clear them?
Des Murph

--
Des
-----------------------------------------------------------------------
DesM's Profile: http://www.excelforum.com/member.php...fo&userid=2412
View this thread: http://www.excelforum.com/showthread.php?threadid=48087

Menu of sheet names

Hi Des

Ammend the [portion of the code:

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

to read:

For Each Wks In ThisWorkbook.Worksheets
*cbCtl.AddItem ""*
Next Wks

Davi

--
david
-----------------------------------------------------------------------
davidm's Profile: http://www.excelforum.com/member.php...fo&userid=2064
View this thread: http://www.excelforum.com/showthread.php?threadid=48087

Menu of sheet names

Should this change be made to both subs or just the first?
Des Murph

--
Des
-----------------------------------------------------------------------
DesM's Profile: http://www.excelforum.com/member.php...fo&userid=2412
View this thread: http://www.excelforum.com/showthread.php?threadid=48087

Menu of sheet names

Hello DesM,

Sorry about that, I should have been more clear. You need only run th
macro once. The new Popup menu control will still be there the nex
time you run Excel.

Add this macro code to the module. Run it from the Macro list (ALT+F8)
Each time it is run it will remove the Drop Down list from the Popu
menu, until there aren't more.

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

Sub RemoveWorksheetList()

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

End Sub

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

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