How to hide toolbar automatically when I switch to other worksheet

[Guest]

I have five worksheets in one workbook. I try to make a tool bar just for the
one worksheet(name is class) and is only visible when the "class" sheet is
active?
Thanks!

 

[Guest]

I suspect you'll need to do this with VBA. You'd turn on the toolbar when
the worksheet is activated and turn if off when it's not. I've linked some
code that may work.

http://www.bygsoftware.com/Excel/VBA/popup_menu_demo.htm

 

[Guest]

Barb,
Thanks so much, is there any way to add the custom popup menu to the
default menu list instead disable the default menu? I just begin to learn VBA.
Wei

 

[Guest]

Put this code in the "ThisWorkbook" Module

Private Sub Workbook_Open()

Call CreatePopup


End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Call DeleteMenus

End Sub


Create a new module and put this in:

Dim cbpop As CommandBarControl
Dim cbctl As CommandBarControl
Dim cbMilestoneSortSubMenu As CommandBarControl

Sub CreatePopup()
'These set the popup menus to be the same for Worksheets and charts.
They could be different
Call createPopupMenus("Worksheet menu bar")
Call createPopupMenus("Chart menu bar")

End Sub

Private Sub createPopupMenus(menuBarName As String)

Set cbpop =
Application.CommandBars(menuBarName).FindControl(Type:=msoControlPopup, _
Tag:="ProjectTrackingPopupTag")
If Not cbpop Is Nothing Then cbpop.Delete

' Create a popup control on the a menu bar

Set cbpop = Application.CommandBars(menuBarName). _
Controls.Add(Type:=msoControlPopup, Temporary:=True)
cbpop.Caption = "&My Macros" '<~~~~Change to what you want to display
cbpop.Tag = "PopupTag"
cbpop.Visible = True

' Add a menu item

Set cbctl = cbpop.Controls.Add(Type:=msoControlButton, Temporary:=True)
cbctl.Visible = True
cbctl.Style = msoButtonCaption
cbctl.Caption = "MyMacro" '<~~~change to show what you want to do.
cbctl.OnAction = " MyMacro" '<~~~This is the macro you want to run.
'You'll need to create
that.




End Sub

Sub DeleteMenus()

Dim cbpop As CommandBarControl

Set cbpop = Application.CommandBars("Worksheet menu
bar").FindControl(Type:=msoControlPopup, _
Tag:="PopupTag")
If Not cbpop Is Nothing Then cbpop.Delete

Set cbpop = Application.CommandBars("Chart menu
bar").FindControl(Type:=msoControlPopup, _
Tag:="PopupTag")
If Not cbpop Is Nothing Then cbpop.Delete

End Sub


HTH,
Barb Reinhardt

 

[Guest]

Thank you so much!
Happy July 4th!
Wei

Put this code in the "ThisWorkbook" Module

Private Sub Workbook_Open()

Call CreatePopup


End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Call DeleteMenus

End Sub


Create a new module and put this in:

Dim cbpop As CommandBarControl
Dim cbctl As CommandBarControl
Dim cbMilestoneSortSubMenu As CommandBarControl

Sub CreatePopup()
'These set the popup menus to be the same for Worksheets and charts.
They could be different
Call createPopupMenus("Worksheet menu bar")
Call createPopupMenus("Chart menu bar")

End Sub

Private Sub createPopupMenus(menuBarName As String)

Set cbpop =
Application.CommandBars(menuBarName).FindControl(Type:=msoControlPopup, _
Tag:="ProjectTrackingPopupTag")
If Not cbpop Is Nothing Then cbpop.Delete

' Create a popup control on the a menu bar

Set cbpop = Application.CommandBars(menuBarName). _
Controls.Add(Type:=msoControlPopup, Temporary:=True)
cbpop.Caption = "&My Macros" '<~~~~Change to what you want to display
cbpop.Tag = "PopupTag"
cbpop.Visible = True

' Add a menu item

Set cbctl = cbpop.Controls.Add(Type:=msoControlButton, Temporary:=True)
cbctl.Visible = True
cbctl.Style = msoButtonCaption
cbctl.Caption = "MyMacro" '<~~~change to show what you want to do.
cbctl.OnAction = " MyMacro" '<~~~This is the macro you want to run.
'You'll need to create
that.




End Sub

Sub DeleteMenus()

Dim cbpop As CommandBarControl

Set cbpop = Application.CommandBars("Worksheet menu
bar").FindControl(Type:=msoControlPopup, _
Tag:="PopupTag")
If Not cbpop Is Nothing Then cbpop.Delete

Set cbpop = Application.CommandBars("Chart menu
bar").FindControl(Type:=msoControlPopup, _
Tag:="PopupTag")
If Not cbpop Is Nothing Then cbpop.Delete

End Sub


HTH,
Barb Reinhardt