Need help with adjusting width of VBA-created toolbars

Hello,
I am using VBA to create a series of toolbars, which are then assigned
to macros. The problem I am having is:
If I create a toolbar manually, it automatically adjusts its width to
whatever caption is specified. However, when I create a toolbar with
VBA, the width does not adjust itself to the caption, so the toolbar
is wider than neccessary. Here is an example of the code I am using:

Sub newButton3()
On Error Resume Next
CommandBars("C").Delete
Dim cBar, ccBar
CommandBars.Add.Name = "C"
Set cBar = CommandBars("C")
Set ccBar = CommandBars("C").Controls.Add(Type:=msoControlButton)

With cBar
.Visible = True
.Left = 945
.Top = 260
.Controls.Add Type:=msoControlButton
.Controls(1).Caption = "UserSetIT"
.Controls(1).OnAction = _
"'C:\IFR Macros\MacroBank.xls'!UserSetit"
End With

With ccBar
.Style = msoButtonCaption
.TooltipText = "Allows User to Set CF and Span"
End With

End Sub

Any pointers/suggestions would be greatly appreciated. Thanks for any
help.

Mark

Mark,

Your code worked fine for me.... Changing the caption text resulted in
different width toolbars each time the code was run.

HTH,
Bernie
MS Excel MVP


"Mark Reynolds" <mrey18@hotmail.com> wrote in message
news:mh1i205rvd7ep4dej5nejkdueq6l8r602c@4ax.com...
> Hello,
> I am using VBA to create a series of toolbars, which are then assigned
> to macros. The problem I am having is:
> If I create a toolbar manually, it automatically adjusts its width to
> whatever caption is specified. However, when I create a toolbar with
> VBA, the width does not adjust itself to the caption, so the toolbar
> is wider than neccessary. Here is an example of the code I am using:
>
> Sub newButton3()
> On Error Resume Next
> CommandBars("C").Delete
> Dim cBar, ccBar
> CommandBars.Add.Name = "C"
> Set cBar = CommandBars("C")
> Set ccBar = CommandBars("C").Controls.Add(Type:=msoControlButton)
>
> With cBar
> .Visible = True
> .Left = 945
> .Top = 260
> .Controls.Add Type:=msoControlButton
> .Controls(1).Caption = "UserSetIT"
> .Controls(1).OnAction = _
> "'C:\IFR Macros\MacroBank.xls'!UserSetit"
> End With
>
> With ccBar
> .Style = msoButtonCaption
> .TooltipText = "Allows User to Set CF and Span"
> End With
>
> End Sub
>
> Any pointers/suggestions would be greatly appreciated. Thanks for any
> help.
>
> Mark

Thanks Bernie,
After reading your response I tried my macro again and discovered you
were right: The toolbar width was automatically adjusting to the
caption. What had fooled me was the fact that the toolbar was coming
out a little wider than neccessary, causing extra space after the
caption. I fooled around some more and took care of this problem by
removing the following line of code:

.Controls.Add Type:=msoControlButton

Thanks for helping lead me to this fix.

Mark