How to hide toolbar automatically when I switch to other worksheet

  • Thread starter Thread starter Guest
  • Start date Start date
G

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!
 
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
 
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
 
Thank you so much!
Happy July 4th!
Wei

Barb Reinhardt said:
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
Click to expand...
 
Back
Top