Need help with adjusting width of VBA-created toolbars

M

Mark Reynolds

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
 
B

Bernie Deitrick

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
 
M

Mark Reynolds

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
 
Top