I need a code to add a custom menu to worksheet menu bar and populate it with
the sheet name in the workbook. Further, if one clicks the particular sheet
name, only that particular sheet should be visible.
I tried populating the menu with sheet name in the workbook, however, I am
unable to do the later.
Any suggestions..

you'll want the "Workbook Tabs" popup..

Normally you'd rightclick the arrows in the bottomleft
part of a window.. but you can add it to the menu bar
like:


Sub CreateMenu_Navigator()
Const MYTAG = "WBT"
With CommandBars
While Not .FindControl(Tag:=MYTAG) Is Nothing
.FindControl(Tag:=MYTAG).Delete
Wend
End With
With CommandBars("Worksheet Menu Bar")
With .Controls.Add(msoControlPopup, temporary:=True)
.Tag = MYTAG
.Caption = "Navigator"
.OnAction = "showWBT"
End With
End With

End Sub

Sub showWBT()
CommandBars("Workbook Tabs").ShowPopup
End Sub




--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Christopher Sequeira wrote :

I need a code to add a custom menu to worksheet menu bar and populate
it with the sheet name in the workbook. Further, if one clicks the
particular sheet name, only that particular sheet should be visible.
I tried populating the menu with sheet name in the workbook, however,
I am unable to do the later.
Any suggestions..

Hi keepITcool:

I think the OP wanted something slightly different, so I've modified your
code a little bit as follows:

Option Explicit

Sub CreateMenu_Navigator()
Const MYTAG = "WBT"
With CommandBars
While Not .FindControl(Tag:=MYTAG) Is Nothing
.FindControl(Tag:=MYTAG).Delete
Wend
End With
With CommandBars("Worksheet Menu Bar")
With .Controls.Add(msoControlPopup, temporary:=True)
.Tag = MYTAG
.Caption = "Navigator"
.OnAction = "ListSheets"
End With
End With
End Sub

Sub ShowSheetListMenu()
Dim ws As Worksheet
Application.ScreenUpdating = False
With Worksheets(CommandBars.ActionControl.Caption)
.Visible = True
.Activate
End With
For Each ws In Worksheets
If Not ws Is ActiveSheet Then ws.Visible = False
Next
Application.ScreenUpdating = True
End Sub

Sub ListSheets()
Dim ws As Worksheet, ctl As CommandBarControl
With CommandBars(1).Controls("Navigator")
For Each ctl In .Controls
ctl.Delete
Next
End With
For Each ws In Worksheets
With CommandBars(1).Controls("Navigator")
.Controls.Add.Caption = ws.Name
.Controls(ws.Name).OnAction = "ShowSheetListMenu"
End With
Next
End Sub


I'm sure it could be streamlined substantially, but I didn't want to spend
too much time on it if I wasn't on the right track.

Regards,

Vasant

"keepITcool" wrote in message
ft.com...


you'll want the "Workbook Tabs" popup..

Normally you'd rightclick the arrows in the bottomleft
part of a window.. but you can add it to the menu bar
like:


Sub CreateMenu_Navigator()
Const MYTAG = "WBT"
With CommandBars
While Not .FindControl(Tag:=MYTAG) Is Nothing
.FindControl(Tag:=MYTAG).Delete
Wend
End With
With CommandBars("Worksheet Menu Bar")
With .Controls.Add(msoControlPopup, temporary:=True)
.Tag = MYTAG
.Caption = "Navigator"
.OnAction = "showWBT"
End With
End With

End Sub

Sub showWBT()
CommandBars("Workbook Tabs").ShowPopup
End Sub




--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Christopher Sequeira wrote :

I need a code to add a custom menu to worksheet menu bar and populate
it with the sheet name in the workbook. Further, if one clicks the
particular sheet name, only that particular sheet should be visible.
I tried populating the menu with sheet name in the workbook, however,
I am unable to do the later.
Any suggestions..

Thanks Vasant

It worked. This was what i was looking for.
I should say that this is the best place for any begineer to get maximum
knowledge and assistance from people like you.
Thanks once again.
And is there a personal mail I could get in touch with you in future, as
there is lots to learn in excel programming and its fun too.

Regards,
Christopher


"Vasant Nanavati" wrote:

Hi keepITcool:

I think the OP wanted something slightly different, so I've modified your
code a little bit as follows:

Option Explicit

Sub CreateMenu_Navigator()
Const MYTAG = "WBT"
With CommandBars
While Not .FindControl(Tag:=MYTAG) Is Nothing
.FindControl(Tag:=MYTAG).Delete
Wend
End With
With CommandBars("Worksheet Menu Bar")
With .Controls.Add(msoControlPopup, temporary:=True)
.Tag = MYTAG
.Caption = "Navigator"
.OnAction = "ListSheets"
End With
End With
End Sub

Sub ShowSheetListMenu()
Dim ws As Worksheet
Application.ScreenUpdating = False
With Worksheets(CommandBars.ActionControl.Caption)
.Visible = True
.Activate
End With
For Each ws In Worksheets
If Not ws Is ActiveSheet Then ws.Visible = False
Next
Application.ScreenUpdating = True
End Sub

Sub ListSheets()
Dim ws As Worksheet, ctl As CommandBarControl
With CommandBars(1).Controls("Navigator")
For Each ctl In .Controls
ctl.Delete
Next
End With
For Each ws In Worksheets
With CommandBars(1).Controls("Navigator")
.Controls.Add.Caption = ws.Name
.Controls(ws.Name).OnAction = "ShowSheetListMenu"
End With
Next
End Sub


I'm sure it could be streamlined substantially, but I didn't want to spend
too much time on it if I wasn't on the right track.

Regards,

Vasant

"keepITcool" wrote in message
ft.com...


you'll want the "Workbook Tabs" popup..

Normally you'd rightclick the arrows in the bottomleft
part of a window.. but you can add it to the menu bar
like:


Sub CreateMenu_Navigator()
Const MYTAG = "WBT"
With CommandBars
While Not .FindControl(Tag:=MYTAG) Is Nothing
.FindControl(Tag:=MYTAG).Delete
Wend
End With
With CommandBars("Worksheet Menu Bar")
With .Controls.Add(msoControlPopup, temporary:=True)
.Tag = MYTAG
.Caption = "Navigator"
.OnAction = "showWBT"
End With
End With

End Sub

Sub showWBT()
CommandBars("Workbook Tabs").ShowPopup
End Sub




--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Christopher Sequeira wrote :

I need a code to add a custom menu to worksheet menu bar and populate
it with the sheet name in the workbook. Further, if one clicks the
particular sheet name, only that particular sheet should be visible.
I tried populating the menu with sheet name in the workbook, however,
I am unable to do the later.
Any suggestions..

Glad to help.

I am not able to respond to help requests via private email. In any event,
you will get much better answers by posting here, as there is such a vast
wealth of expertrise available.

--

Vasant



"Christopher Sequeira" wrote
in message ...
Thanks Vasant

It worked. This was what i was looking for.
I should say that this is the best place for any begineer to get maximum
knowledge and assistance from people like you.
Thanks once again.
And is there a personal mail I could get in touch with you in future, as
there is lots to learn in excel programming and its fun too.

Regards,
Christopher


"Vasant Nanavati" wrote:

Hi keepITcool:

I think the OP wanted something slightly different, so I've modified
your
code a little bit as follows:

Option Explicit

Sub CreateMenu_Navigator()
Const MYTAG = "WBT"
With CommandBars
While Not .FindControl(Tag:=MYTAG) Is Nothing
.FindControl(Tag:=MYTAG).Delete
Wend
End With
With CommandBars("Worksheet Menu Bar")
With .Controls.Add(msoControlPopup, temporary:=True)
.Tag = MYTAG
.Caption = "Navigator"
.OnAction = "ListSheets"
End With
End With
End Sub

Sub ShowSheetListMenu()
Dim ws As Worksheet
Application.ScreenUpdating = False
With Worksheets(CommandBars.ActionControl.Caption)
.Visible = True
.Activate
End With
For Each ws In Worksheets
If Not ws Is ActiveSheet Then ws.Visible = False
Next
Application.ScreenUpdating = True
End Sub

Sub ListSheets()
Dim ws As Worksheet, ctl As CommandBarControl
With CommandBars(1).Controls("Navigator")
For Each ctl In .Controls
ctl.Delete
Next
End With
For Each ws In Worksheets
With CommandBars(1).Controls("Navigator")
.Controls.Add.Caption = ws.Name
.Controls(ws.Name).OnAction = "ShowSheetListMenu"
End With
Next
End Sub


I'm sure it could be streamlined substantially, but I didn't want to
spend
too much time on it if I wasn't on the right track.

Regards,

Vasant

"keepITcool" wrote in message
ft.com...


you'll want the "Workbook Tabs" popup..

Normally you'd rightclick the arrows in the bottomleft
part of a window.. but you can add it to the menu bar
like:


Sub CreateMenu_Navigator()
Const MYTAG = "WBT"
With CommandBars
While Not .FindControl(Tag:=MYTAG) Is Nothing
.FindControl(Tag:=MYTAG).Delete
Wend
End With
With CommandBars("Worksheet Menu Bar")
With .Controls.Add(msoControlPopup, temporary:=True)
.Tag = MYTAG
.Caption = "Navigator"
.OnAction = "showWBT"
End With
End With

End Sub

Sub showWBT()
CommandBars("Workbook Tabs").ShowPopup
End Sub




--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Christopher Sequeira wrote :

I need a code to add a custom menu to worksheet menu bar and
populate
it with the sheet name in the workbook. Further, if one clicks the
particular sheet name, only that particular sheet should be visible.
I tried populating the menu with sheet name in the workbook,
however,
I am unable to do the later.
Any suggestions..