Command Bar layout 1

[misubud]

Hello,
Am writing code to temporarily create a custom command bar on load of an excel worksheet.
Command bar is then populated with a command button relating to each of the workbooks sheet tabs.

-----------


Set cbar = Application.CommandBars.Add(Name:="WorkSheet Navigator", Position:=msoBarFloating)
cbar.Visible = True

For Each wsheet In Workbooks("command bars.xls").Worksheets
Set cbCtl = cbar.Controls.Add(Type:=msoControlButton)
cbCtl.Visible = True
cbCtl.Style = msoButtonIconAndCaption
cbCtl.FaceId = 2105
cbCtl.Caption = wsheet.Name
cbCtl.OnAction = "Worksheet" & wsheet.Name
Next
-------------

My problem is, each button is added to the right hand side of the command bar, which just becomes a long strip.
I want to limit the length of the command bar and have the various buttons layer down in two or three lines on the command bar.

I have tried limiting the command bar width, to no avail.

Can someone point me in the direction to set the command bar to multi line??

 

[combo]

You can add:

If cBar.Width > 300 Then cBar.Width = 300

Alternatively, you can create menu:

Set cBar = Application.CommandBars.Add(Name:="WorkSheet Navigator", Position:=msoBarFloating)
cBar.Visible = True
Set cbMenu = cBar.Controls.Add(Type:=msoControlPopup)
cbMenu.Caption = Workbooks("command bars.xls").Name
For Each wsheet In Workbooks("command bars.xls").Worksheets
Set cbCtl = cbMenu.Controls.Add(Type:=msoControlButton)
cbCtl.Visible = True
cbCtl.Style = msoButtonIconAndCaption
cbCtl.FaceId = 2105
cbCtl.Caption = wsheet.Name
cbCtl.OnAction = "Worksheet" & wsheet.Name
Next

combo

 

[SkipVought]

Hi,

"... command button relating to each of the workbooks sheet tabs..."

How many sheet tabs do you have? More tabs than you can see in the window?

Right-Click in the Sheet Navigation buttons (bottom Right Hand) and you get a list of all Sheets. Does this functionality help?

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}