removing custom toolbars when closing workbook

[Tony]

I am adding custome toolbars to the workbook on open to
give access to macros. I would like to remove those
toolbars when workbook is closed. How I can do it.

My module looks like:

'********************************

Sub Workbook_Open()

Dim c As CommandBar
Dim cb As CommandBarButton
Dim cp As CommandBarPopup

On Error Resume Next
Set c = Application.CommandBars("Worksheet Menu Bar")
If Not c Is Nothing Then

Set cb = c.Controls.Add(msoControlButton, 2,
temporary:=True)
cb.Style = msoButtonCaption
cb.Caption = "Subtotal"
cb.OnAction = "Sub_total"

Set cb = c.Controls.Add(msoControlButton, 2,
temporary:=True)
cb.Style = msoButtonCaption
cb.Caption = "Insert"
cb.OnAction = "Insert"

Set cb = c.Controls.Add(msoControlButton, 2,
temporary:=True)
cb.Style = msoButtonCaption
cb.Caption = "Update"
cb.OnAction = "Update"

Set cb = c.Controls.Add(msoControlButton, 2,
temporary:=True)
cb.Style = msoButtonCaption
cb.Caption = "Print Master Summary"
cb.OnAction = "PrintMasterSummary"

Set cb = c.Controls.Add(msoControlButton, 2,
temporary:=True)
cb.Style = msoButtonCaption
cb.Caption = "Print Summary"
cb.OnAction = "PrintSummary"

Set cb = c.Controls.Add(msoControlButton, 2,
temporary:=True)
cb.Style = msoButtonCaption
cb.Caption = "Print Entries"
cb.OnAction = "PrintEntries"

End If

End Sub

'************************************

Sub Workbook_BeforeClose(Cancel As Boolean)

?????

End Sub

'************************************

Thanks for advice.

Tony

 

[Soo Cheon Jheong]

Hi,

Solution(1)
--------------------------------------------------------------------------
Option Explicit
Dim C As CommandBar
Dim CB As CommandBarButton
Dim CT As CommandBarControl

Sub Workbook_Open()

Set C = Application.CommandBars("Worksheet Menu Bar")

For Each CT In C.Controls
If CT.Tag = "Korea" Then CT.Delete
Next

Set CB = C.Controls.Add(msoControlButton, 2, temporary:=True)
CB.Style = msoButtonCaption
CB.Caption = "Subtotal"
CB.OnAction = "Sub_total"
CB.Tag = "Korea"

Set CB = C.Controls.Add(msoControlButton, 2, temporary:=True)
CB.Style = msoButtonCaption
CB.Caption = "Insert"
CB.OnAction = "Insert"
CB.Tag = "Korea"

Set CB = C.Controls.Add(msoControlButton, 2, temporary:=True)
CB.Style = msoButtonCaption
CB.Caption = "Update"
CB.OnAction = "Update"
CB.Tag = "Korea"

Set CB = C.Controls.Add(msoControlButton, 2, temporary:=True)
CB.Style = msoButtonCaption
CB.Caption = "Print Master Summary"
CB.OnAction = "PrintMasterSummary"
CB.Tag = "Korea"

Set CB = C.Controls.Add(msoControlButton, 2, temporary:=True)
CB.Style = msoButtonCaption
CB.Caption = "Print Summary"
CB.OnAction = "PrintSummary"
CB.Tag = "Korea"

Set CB = C.Controls.Add(msoControlButton, 2, temporary:=True)
CB.Style = msoButtonCaption
CB.Caption = "Print Entries"
CB.OnAction = "PrintEntries"
CB.Tag = "Korea"

End Sub

Sub Workbook_BeforeClose(Cancel As Boolean)

Set C = Application.CommandBars("Worksheet Menu Bar")
For Each CT In C.Controls
If CT.Tag = "Korea" Then CT.Delete
Next

End Sub
--------------------------------------------------------------------------


--
Regards,
Soo Cheon Jheong
_ _
^¢¯^
--

 

[Soo Cheon Jheong]

Hi,

Solution(2)
--------------------------------------------------------------------------
Option Explicit
Private Sub Workbook_Open()

Dim i As Byte
Dim M(0 To 5, 0 To 1) As String

M(0, 0) = "Subtotal": M(0, 1) = "Sub_total"
M(1, 0) = "Insert": M(1, 1) = "Insert":
M(2, 0) = "Update": M(2, 1) = "Update"
M(3, 0) = "Print Master Summary": M(3, 1) = "PrintMasterSummary":
M(4, 0) = "Print Summary": M(4, 1) = "PrintSummary"
M(5, 0) = "Print Entries": M(5, 1) = "PrintEntries"

Call Delete_Controls

With Application.CommandBars(1).Controls
For i = 0 To 5
With .Add(Type:=msoControlButton, ID:=2, Temporary:=True)
.Style = msoButtonCaption
.Caption = M(i, 0)
.OnAction = M(i, 1)
.Tag = "Korea"
End With
Next
End With

End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Call Delete_Controls

End Sub

Private Sub Delete_Controls()

Dim CT As CommandBarControl
For Each CT In Application.CommandBars(1).Controls
If CT.Tag = "Korea" Then CT.Delete
Next

End Sub
--------------------------------------------------------------------------


--
Regards,
Soo Cheon Jheong
_ _
^¢¯^
--

 

[Soo Cheon Jheong]

Hi,

Solution(2)
--------------------------------------------------------------------------
Option Explicit
Private Sub Workbook_Open()

Dim i As Byte
Dim M(0 To 5, 0 To 1) As String

M(0, 0) = "Subtotal": M(0, 1) = "Sub_total"
M(1, 0) = "Insert": M(1, 1) = "Insert":
M(2, 0) = "Update": M(2, 1) = "Update"
M(3, 0) = "Print Master Summary": M(3, 1) = "PrintMasterSummary":
M(4, 0) = "Print Summary": M(4, 1) = "PrintSummary"
M(5, 0) = "Print Entries": M(5, 1) = "PrintEntries"

Call Delete_Controls

With Application.CommandBars(1).Controls
For i = 0 To 5
With .Add(Type:=msoControlButton, ID:=2, Temporary:=True)
.Style = msoButtonCaption
.Caption = M(i, 0)
.OnAction = M(i, 1)
.Tag = "Korea"
End With
Next
End With

End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Call Delete_Controls

End Sub

Private Sub Delete_Controls()

Dim CT As CommandBarControl
For Each CT In Application.CommandBars(1).Controls
If CT.Tag = "Korea" Then CT.Delete
Next

End Sub
--------------------------------------------------------------------------


--
Regards,
Soo Cheon Jheong
_ _
^¢¯^
--

 

[Tony]

Thanks for help. I have found the answer in the meantime.
I have used:

Application.CommandBars("Worksheet Menu Bar").Controls
("Subtotal").Delete

repeated for each control.

Tony