How Do you Delete 2 custom Icons From Formatting Toolbar with VBA?

[Dennis]

I have the following add-in VBA code to create 2 Icons on the "Formatting"
toolbar.

However, when I uncheck the add-in, only one of the icons is deleted from the
Formatting toolbar. How do you get both Icons to be deleted on "BeforeClose"
routine?

TIA

Dennis

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
'Delete Icons on Formatting Toolbar
Application.CommandBars("Formatting").Controls(MenuItemName").Delete
Application.CommandBars("Formatting").Controls("MakeVisible").Deleter
End Sub

Private Sub Workbook_Open()
Dim oCtl1 As CommandBarControl
Dim oCtl2 As CommandBarControl

On Error Resume Next
'just in case they are still on Formatting Toolbar
Application.CommandBars("Formatting").Controls(MenuItemName).Delete
Application.CommandBars("Formatting").Controls("MakeVisible").Delete
On Error GoTo 0

With Application.CommandBars("Formatting")
Set oCtl1 = .Controls.Add(Type:=msoControlButton, temporary:=True)
With oCtl1
.BeginGroup = True
.Caption = "PFLRVar Filter"
.OnAction = MenuItemMacro
.FaceId = 264
End With
Set oCtl2 = .Controls.Add(Type:=msoControlButton, temporary:=True)
With oCtl2
.BeginGroup = True
.Caption = "Reset"
.OnAction = "MakeVisible"
.FaceId = 330
End With
End With
End Sub

 

[Dave Peterson]

Watch your typing.

Application.CommandBars("Formatting").Controls("MakeVisible").Deleter

There's an extra R at the end of .delete.

And if the MakeVisible is the one still shown, I think that's it!

 

[Dennis]

Hi Dave,

Nope that didn't do it. With Deleter on both, no icons were deleted. With
deleter on the MakeVisible and delete on the MenuItemName only the MenuItemName
icon disappears. With delete on MenuItemName and MakeVisible only the
MenuItemName icon id deleted and disappears. With the add-in unchecked, if I
close excel and then reopen Excel the undeleted icon then disappears.

I can't get the second icon to go bye-bye. I tried reversing the order of the
icon deletes, deleting the last icon first and then the first icon, but no luck
same results. I just can't seem the delete two icons in the Formatting toolbar.

Of course I can manually do it by clicking on Tools->Customize and dragging the
stubborn icon into that window, but I want to delete it with code.


Dennis

 

[Dave Peterson]

..Deleter is an error. It should be .delete

But there were a couple of other problems, too.

Option Explicit

Private Sub Workbook_BeforeClose() '(Cancel As Boolean)
On Error Resume Next
'Delete Icons on Formatting Toolbar
Application.CommandBars("Formatting").Controls("PFLRVar Filter").Delete
Application.CommandBars("Formatting").Controls("Reset").Delete
End Sub

Private Sub Workbook_Open()
Dim oCtl1 As CommandBarControl
Dim oCtl2 As CommandBarControl

On Error Resume Next
'just in case they are still on Formatting Toolbar
Application.CommandBars("Formatting").Controls("PFLRVar Filter").Delete
Application.CommandBars("Formatting").Controls("Reset").Delete
On Error GoTo 0

With Application.CommandBars("Formatting")
Set oCtl1 = .Controls.Add(Type:=msoControlButton, temporary:=True)
With oCtl1
.BeginGroup = True
.Caption = "PFLRVar Filter"
.OnAction = "MenuItemMacro"
.FaceId = 264
End With
Set oCtl2 = .Controls.Add(Type:=msoControlButton, temporary:=True)
With oCtl2
.BeginGroup = True
.Caption = "Reset"
.OnAction = "MakeVisible"
.FaceId = 330
End With
End With
End Sub

Notice in these statements:

Application.CommandBars("Formatting").Controls("PFLRVar Filter").Delete
Application.CommandBars("Formatting").Controls("Reset").Delete

You'll want to use the .caption for that control.

(maybe I should have looked closer--but after I saw that extra R, I thought I
was done <bg>.)

 

[Dennis]

Thanks Dave that did it!

I didn't see that I made the typo "deleter" on my earlier post. I thought your
response was to change delete to deleter. My error.

Also in examining Bob Phillips past posts for this ng and particular the post on
2004-10-20 using Google groups, I did catch that the .controls had to use the
..caption in order to delete the icon. I don't know how I missed that.

The funny thing is that even though I had an error one of the icons was
deleted.

Anyway all works fine now.

Many thanks again to you for pointing out my error.

Dennis

 

[Dave Peterson]

Glad you got it working.

Thanks Dave that did it!

I didn't see that I made the typo "deleter" on my earlier post. I thought your
response was to change delete to deleter. My error.

Also in examining Bob Phillips past posts for this ng and particular the post on
2004-10-20 using Google groups, I did catch that the .controls had to use the
.caption in order to delete the icon. I don't know how I missed that.

The funny thing is that even though I had an error one of the icons was
deleted.

Anyway all works fine now.

Many thanks again to you for pointing out my error.

Dennis