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" <xrrcvgpbby@puryyb.ay> wrote in message
news:xn0e3epv8e5ggvg001keepitcoolnl@msnews.microsoft.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
cool1503@gmail.com
"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" <xrrcvgpbby@puryyb.ay> wrote in message
> news:xn0e3epv8e5ggvg001keepitcoolnl@msnews.microsoft.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" <ChristopherSequeira@discussions.microsoft.com> wrote
in message news:EABBF642-F850-4BFA-9F28-DAE7A7EF2EBB@microsoft.com...
> 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
> cool1503@gmail.com
>
> "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" <xrrcvgpbby@puryyb.ay> wrote in message
> > news:xn0e3epv8e5ggvg001keepitcoolnl@msnews.microsoft.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..
> >
> >
> >
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks