macro works on F8 but not auto

[Neil]

Macro written to delete an addition to commandbar(1):

When it is invoked through the .onaction property that is
defined when the menu item is added, the delete item
macro fails. The error message is -2147467259(80004005)
Method 'Delete' of object 'CommandBarPopup' failed.

Strangely though, if I step through the macro
incrementally (F8 F8 F8...) it works fine.

I can't figure this one out.

Thanks for any help!

Neil

 

[Bernie Deitrick]

Neil,

Post your code.

HTH,
Bernie
MS Excel MVP

 

[Guest]

Here's the code...

Sub addtomenu()

Dim dubam As CommandBarControl

Set dubam = CommandBars(1).Controls.Add
(Type:=msoControlPopup)
dubam.Caption = "Return to DUBAM"
With dubam
.OnAction = "deletefrommenu"
End With


End Sub


Sub deletefrommenu()
Dim menuitem As CommandBarControl
Dim count As Integer

For Each menuitem In CommandBars(1).Controls
count = count + 1
Set menuitem = CommandBars(1).Controls(count)
If menuitem.BuiltIn = False Then
menuitem.Delete
End If
Next

Call go


End Sub

 

[Neil]

Bernie - thanks, but it still worked the same way...step
through is fine, but regular execution same error.

Did this code work for you? Perhaps it is a XP or Excel
2002 bug...

I can just change the type to floating...but I dislike
unresolved problems...

Neil

 

[Neil]

I ran a couple of tests...

If there were two menu items with builtin=false, the
first one deletes, the second one errors out. It seems
that if there is only one custom item, there is a problem
deleting it.

Placement does not seem to matter - I had thought that
maybe there was a problem with the "last" menu item.

I'm relatively new to VB programming (6 weeks) so I don't
know where to go with this info.

Neil

 

[Bernie Deitrick]

Neil,

How about trying to step through the controls:

Sub DeleteCustomButtons()
Dim CmdBar As CommandBar
Dim myCont As CommandBarControl
Set CmdBar = Application.CommandBars(1)
For Each myCont In CmdBar.Controls
If myCont.BuiltIn = False Then
myCont.Delete
End If
Next myCont
End Sub

HTH,
Bernie
MS Excel MVP

 

[Neil]

Bernie - no luck there either. However, I did find a
less elegant way...

commandbars(1).reset

I stumbled across this as I was running some tests and
accidentally deleted all the built in items (which I
thought you were not supposed to be able to do...). I
paniced for a second but then figured there must be a way
to reset...found that, restored the built ins and then
realized....

Thanks for the help.

Neil