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

 

[Bernie Deitrick]

Neil,

As soon as you delete a control, that throws off your upper limit on the Do
loop. You need to go in reverse order. I'm not sure why it works by
stepping through, but I'm sure why it doesn't work in regular execution:

For Count = CommandBars(1).Controls.Count To 1 Step -1
Set menuitem = CommandBars(1).Controls(Count)
If menuitem.BuiltIn = False Then menuitem.Delete
Next Count

HTH,
Bernie
MS Excel MVP

 

[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