Sheets to a menu
eradicator
menu in the command bar
Thanks
Eradicator
Tom Ogilvy
need to add for a single sheet).
No you can add a loop around the appropriate code that adds the menu item
which will have the sheet name.
for each sh in thisworkbook.Worksheets
with .Add . . .
.Caption = sh.name
.OnAction = "GoToSheet"
End with
Next
In gotosheet macro, you can use the ActiveControl.Caption to get the name
of the sheet to go to.
Regards,
Tom Ogilvy
"eradicator" <fh...@zlpbpx.gb> wrote in message
news:Oo_H5.2847$iY1....@sodalite.nbnet.nb.ca...
David McRitchie
since there is no implementation.
If you just want to be able to see a list of sheets and select from that
Right-Click on any of the sheet navigation buttons in lower left
corner. The sheetnames will not be sorted, but you can sort
the sheet tabs and then they will be same list, more information at.
http://www.geocities.com/davemcritchie/excel/excel.htm
HTH,
David McRitchie, Microsoft MVP - Excel (site changed 2000-04-15)
My Excel Macros: http://www.geocities.com/davemcritchie/excel/excel.htm
Chip Pearson
Sub CreateMenu()
Dim WB As Workbook
Dim WS As Worksheet
Const cTag = "__TempTag__"
With Application.CommandBars
If Not .FindControl(Tag:=cTag) Is Nothing Then
.FindControl(Tag:=cTag).Delete
End If
End With
With Application.CommandBars.ActiveMenuBar.Controls.Add( _
Type:=msoControlPopup, temporary:=True)
.Caption = "Sheets"
For Each WB In Workbooks
If WB.Windows(1).Visible = True Then
With .Controls.Add(Type:=msoControlPopup, _
temporary:=True)
.Caption = WB.Name
For Each WS In WB.Worksheets
With .Controls.Add
.Caption = WS.Name
.OnAction = "'" & ThisWorkbook.Name &
"'!ActivateSheet"
.Tag = WS.Range("A1").Address(True, True, xlA1,
True)
End With
Next WS
End With
End If
Next WB
End With
End Sub
Sub ActivateSheet()
Dim Rng As Range
Set Rng = Range(Application.CommandBars.ActionControl.Tag)
Rng.Parent.Parent.Activate
Rng.Parent.Select
End Sub
You'll have to re-run the macro when worksheets and/or workbooks are added,
deleted, or renamed.
--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com ch...@cpearson.com
"eradicator" <fh...@zlpbpx.gb> wrote in message
news:Oo_H5.2847$iY1....@sodalite.nbnet.nb.ca...
> Is there and easy way to take all current sheets and place them in a
custom
> menu in the command bar
>
> Thanks
>
> Eradicator
>
>
>
>
eradicator
CHip your method is one I was looking for
You guys have saved me hours of programming difficulties
Thanks again
Eradicator
Chip Pearson
I noticed one problem in the code.
After the line
.Caption = "Sheets"
you need
.Tag = cTag
Otherwise, the menu won't get deleted before it is recreated.
> You guys have saved me hours of programming difficulties
We'll send you a bill.
--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com ch...@cpearson.com
"eradicator" <fh...@zlpbpx.gb> wrote in message
news:f30I5.2867$iY1....@sodalite.nbnet.nb.ca...
eradicator
I am only using one workbook and I have several sheets which are not visible
Just in case anyone wanted this
Eradicator
Thanks again everyone for your help
Sub CreateMenu()
Dim WB As Workbook
Dim WS As Worksheet
Const cTag = "__TempTag__"
With Application.CommandBars
If Not .FindControl(Tag:=cTag) Is Nothing Then
.FindControl(Tag:=cTag).Delete
End If
End With
With Application.CommandBars.ActiveMenuBar.Controls.Add( _
Type:=msoControlPopup, temporary:=True)
.Caption = "Sheets"
For Each WB In Workbooks
' If WB.Windows(1).Visible = True Then
' With .Controls.Add(Type:=msoControlPopup, _temporary:=True)
.Caption = WB.Name
For Each WS In WB.Worksheets
If WS.Visible = True Then
With .Controls.Add
.Caption = WS.Name
.OnAction = "'" & ThisWorkbook.Name &
"'!ActivateSheet"
.Tag = WS.Range("A1").Address(True, True, xlA1,
True)
End With
End If
David McRitchie
Observations: The beginning of this thread can be located at
http://www.deja.com/=dnc/getdoc.xp?AN=683847198
1) advantage of the menu solution is that several workbooks
(open workbooks) are included as opposed to just the current
workbook by right-clicking on any sheet tab navigation button.
Neither method sorts the entries. If you want them sorted you
must sort the sheet tabs yourself using any of the sheet tab sorts.
2) VBA will fail on instruction with mso... if the object library routines
are not included from the VBE, Tools, References menu.
For Excel 2000 that would be: Microsoft Office 9.0 Object Library
3) Did not follow John's reply, am glad Chip elaborated.
4) Activate sheet will fail if sheet was deleted or the workbook is no
longer open (see #1 above) as Chip said you have to rerun macro
if you add or delete sheets. Perhaps a better change could be
done to ActivateSheet macro but my modification includes error
test. Actually would rename macros: CreateSheetsMenu and
CreateSheetsMenu_ActivateSheet the latter is internal to the
generated menu item anyway.
5) My suggestion was to RClick on navigation button the equivalent
VBA code (Jim Rech) was included below on failure:
CommandBars("Workbook tabs").ShowPopup
6) If rewritting code would have first menu item be the macro to
recreate the menu with the other same level menu items
being the per workbook menus below it.
Sub CreateSheetsMenu_ActivateSheet()
Dim Rng As Range
On Error Resume Next
Err.Number = 0
Set Rng = Range(Application.CommandBars.ActionControl.Tag)
If Err.Number <> 0 Then
MsgBox Err.Number & " " & Err.Description & Chr(10) & _
"* 1004 The workbook is not open or does not match menu" & Chr(10) & _
"Rerun the CreateSheetsMenu with desired WorkBooks open"
CommandBars("Workbook tabs").ShowPopup 'Jim Rech posting
Exit Sub
End If
Rng.Parent.Parent.Activate
Rng.Parent.Select
End Sub
HTH,
David McRitchie, Microsoft MVP - Excel (site changed 2000-04-15)
My Excel Macros: http://www.geocities.com/davemcritchie/excel/excel.htm
Related Material: http://www.geocities.com/davemcritchie/excel/buildtoc.htm
Chip Pearson <ch...@cpearson.com> wrote in message
news:ukjxVQsOAHA.249@cppssbbsa05...