Problem with CommandBar width

[ricaforrica]

Hello people!

I created a custom command bar and I want to add a variable number of buttons (it depends on the number of database registers fetched and placed in a .txt file).

It all works great, but the buttons are inserted side by side, so that my command bar gets to large!!!

I tried to set the width and heigth for my commandbar but nothing changes...

I'm using the following code:

Sub AutoNew()

'Initialize variables
Const ForReading = 1, ForWriting = 2, ForAppending = 3
Dim fs, f
Dim linha As String
Dim cmd As CommandBar
Dim rel1 As CommandBarButton

'Create command bar
Set cmd = CommandBars.Add("Relatórios Pendentes", MsoBarPosition.msoBarFloating, False, True)
cmd.Visible = True

With cmd
.Left = 800
.Top = 200
End With

'Open var_file.txt
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.OpenTextFile("var_file.txt", ForReading, TristateFalse)
While Not f.atEndOfStream
'Create buttons
Set rel1 = cmd.Controls.Add(msoControlButton)
linha = f.ReadLine
rel1.TooltipText = "Path\" & linha & ".doc"
linha2 = f.ReadLine
rel1.DescriptionText = "Relatório de " & linha2
rel1.ShortcutText = "Relatório de " & linha2
rel1.Caption = linha2
rel1.Tag = "Rel" & linha2
rel1.HyperlinkType = msoCommandBarButtonHyperlinkOpen
rel1.Style = msoButtonIconAndCaption
rel1.FaceId = 31
rel1.BeginGroup = True
Wend
f.Close
End Sub

Can anyone help me out?
Thanks in advance!

Ricardo Pinto

 

[rmikesmith]

Ricardo,

Set your CommandBar's width property after all the buttons are created. This worked for me.


Regards,
Mike

 

[TonyJollans]

Hi Ricardo,

Mike is correct, You cannot set the size of a command bar to larger than necessary to accomodate the buttons - your settings get overridden, and the commandbar gets resized when new buttons are added, according to built-in rules - there is no way to express a preference and all you can do is resize after the event.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[ricaforrica]

Hey guys!

Thanks a lot for your help! It works properly now...
My template is rocking!

Maybe it will be nicer with some better style definitions: is it possible to change the colors, fonts and stuff of the command bars?

Regards,

Ricardo