Runtime error 800040050 when dynamically creating a new toolbar

[Simon]

I have a handful of macros that I distribute to my coleagues as an addin...

As the number of Macros has grown, I'm trying to use a toolbar to access the
macro's, however I'm getting the above runtime error when I try and generate
the bar dynamically.

code looks like this (mind the wrap)-

*****************************************************************************
Set CBSBar = CommandBars.Add(Name:="CBSBar", Position:=msoBarLeft,
temporary:=True)

With CBSBar
.Visible = True
End With

For i = 1 To 6
Select Case i
Case 1
CBSBar.Controls.Add(Type:=msoControlButton, ID:=i).Caption =
"caption 1"
With CBSBar.Controls(i)
.Style = msoButtonIcon
.FaceId = 2147
.OnAction = "xxx"
.TooltipText = "Blah blah blah"
End With
Case 2
CBSBar.Controls.Add(Type:=msoControlButton, ID:=i).Caption =
"Caption 2"
With CBSBar.Controls(i)
.Style = msoButtonIcon
.OnAction = "xxx"
.FaceId = 2150
.TooltipText = "Blah Blah Blah"
End With
Case 3
CBSBar.Controls.Add(Type:=msoControlButton, ID:=i).Caption = "caption
3"
With CBSBar.Controls(i)
.Style = msoButtonIcon
.OnAction = "xxx"
.FaceId = 2149
.TooltipText = "blah blah blah"
End With
Case 4
CBSBar.Controls.Add(Type:=msoControlButton, ID:=i).Caption = "caption
4"
With CBSBar.Controls(i)
.FaceId = 233
.Style = msoButtonIcon
.OnAction = "xxx"
.TooltipText = "blah blah blah"
.BeginGroup = True
End With
Case 5
CBSBar.Controls.Add(Type:=msoControlButton, ID:=i).Caption = "caption
5"
With CBSBar.Controls(i)
.Style = msoButtonIcon
.FaceId = 659
.OnAction = "xxx"
.TooltipText = "blah blah blah"
End With
Case 6
CBSBar.Controls.Add(Type:=msoControlButton, ID:=i).Caption =
"caption 6"
With CBSBar.Controls(i)
.Style = msoButtonIcon
.FaceId = 684
.OnAction = "xxx"
.TooltipText = "blah blah blah"
End With
end select
*****************************************************************************

code works fine (ie it adds a new toolbar, and starts to populate it with
buttons) until the 5th increment where it generates a runtime error.

I note from the MS site that this is an acknowledged 'bug', but I can't make
sense of their 'fix' in this context (ie the nominated example uses shortcut
menu's and I can't work out how that translates to a toolbar). Google
returns thousands of hits on this error however they mainly appear to be in
relation to access/sql or ASP errors (no help whatsoever...)

Any/all assistance gratefully received

TIA

S

 

[Vasant Nanavati]

I note from the MS site that this is an acknowledged 'bug', but I can't make
sense of their 'fix' in this context (ie the nominated example uses shortcut
menu's and I can't work out how that translates to a toolbar).

Curious ... where did you find the acknowledgment and the fix?

 

[Jim Cone]

Simon,

Remove the ",ID=i" from each segment.
There is no ID 5 or ID 6.

Regards,
Jim Cone
San Francisco, USA

 

[Simon]

Remove the ",ID=i" from each segment.
There is no ID 5 or ID 6.

Thanks Jim, works a treat!!

seeing as you volunteered this tip, can you expand on the why's for me a
little - I think I understand what you're getting at, however I don't quite
understand why the same code worked on the first 4 increments (there were no
ID's for those objects either...) yet failed on the 5th....

BTW, the sample code was abridged, in the full version there are 14 buttons

S

 

[Jim Cone]

Simon,

The ID tells Excel that you want to add a button that is a copy of
an existing built-in button (with the same functionality).
1 is the ID for all custom buttons
there is no built-in button with the ID of 5 or 6 (that I can find).

I add built-in buttons with something like the following...
Dim cmdNewItem as CommandBarButton
Set cmdNewItem = CBSBar.Add(ID:=762)
cmdNewItem.Caption = "Phrase"

Your code, as it appeared to me, was adding built-in buttons to the
commandbar and then overriding all of the built-in features with
your own custom properties and methods.
It was throwing the error when it couldn't find the non-existent button.

Hope this helps a little.

Regards,
Jim Cone
San Francisco, USA

 

[Simon]

Simon,

The ID tells Excel that you want to add a button that is a copy of
an existing built-in button (with the same functionality).
1 is the ID for all custom buttons
there is no built-in button with the ID of 5 or 6 (that I can find).

I add built-in buttons with something like the following...
Dim cmdNewItem as CommandBarButton
Set cmdNewItem = CBSBar.Add(ID:=762)
cmdNewItem.Caption = "Phrase"

Your code, as it appeared to me, was adding built-in buttons to the
commandbar and then overriding all of the built-in features with
your own custom properties and methods.
It was throwing the error when it couldn't find the non-existent button.

Hope this helps a little.

Regards,
Jim Cone
San Francisco, USA

that makes sense

I was working on the basis that the ID designated the position, not an actual
object (this also explains why the first 4 buttons came up with different
icons before I changed them...)

it's all so easy when you know how...

many, many thanks

S

 

[Simon]

Curious ... where did you find the acknowledgment and the fix?

this from the MS site
<http://support.microsoft.com/default.aspx?scid=kb;en-us;213696>

<quote>

This problem occurs if both of the following conditions are true:
• You view the workbook in Microsoft Internet Explorer.

-and-
• The macro that creates the custom menu item on the worksheet shortcut
menu uses the CommandBars object model to create the menu item.
For example, the following code results in an error message, under the
conditions listed above:

Sub Custom_Menu_Error()
Dim menuoption As CommandBarControl
Set menuoption = _
CommandBars("cell").Controls.Add(Type:=msoControlButton, before:=1)
menuoption.Caption = "my menu"
End Sub


RESOLUTION
To create a custom menu item on the worksheet shortcut menu, use the syntax
for the Add method of the Menuitems object. For example, the following sample
code does not result in an error message:

Sub Add_Custom_Menu()
ShortcutMenus(xlWorksheetCell).MenuItems.Add Caption:="my menu"
End Sub


STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that
are listed at the beginning of this article.

</quote>

regards

S