Error removing Excel toolbar button in macro

[cg084]

Hello,

I have added a button on to a toolbar in excel through the use of a macro. I would like to remove just this one button from the curtom toolbar. I have used the following code to create the button on the toolbar and the second sub is to remove the button when clicked.

Sub CloseValidationPauseToolbar()

Dim NewBar As Object
Set NewBar = CommandBars("Inbound Attendance Sheets")
With NewBar
.Controls.Add Type:=msoControlButton, ID:=1, before:=3
With .Controls(3)
.Style = msoButtonCaption
.Caption = "Continue Processing"
.OnAction = "ValidationComplete"
End With
End With

End Sub
-----------------------------------------------------------
Sub ValidationComplete()

Application.CommandBars("Inbound Attendance Sheets").Controls(3).Delete
Call RunAllMacros

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

When i run the ValidationComplete sub i receive this error message.

Run-time error '-2147467259(80004005)':
Method 'Delete' of ofbject '_CommandBarButton' failed.

It is strange that at this point if i end the sub. The run through line by line it appears to work fine.

Does anyone have any ideas on this.
Thank you
CG084

 

[macropod]

Hi CG,

Actually, I'm surprised that it doesn't fail at:
.Controls.Add Type:=msoControlButton, ID:=1, before:=3
since there don't appear to be anything at 1 & 2 to put this one before.

Cheers

 

[cg084]

macropod,

You spotted that. I forgot to mention that this toolbar allready has 2 buttons on it. So when the above subs run it should add it after them.

Cheers CG084