K
ker_01
I'm creating a custom toolbar, and for convenience, I plan to have it only
show up when my workbook is open (via an add-in) and only when my workbook
is active (will code that later).
To make sure it is always easy to find the toolbar, I want to check the
location of all visible toolbars at the top of the Excel window, and dock
this new custom toolbar on a new row below the lowest pre-existing docked
toolbar.
I've been playing with different characteristics of the visible toolbars,
but haven't found a consistent way to identify the lowest active toolbar and
create mine just below it. for example:
Sub testbarlocations()
For Each ctlCBarControl In Application.CommandBars
If ctlCBarControl.Visible = True Then
MsgBox ctlCBarControl.Name & " " & ctlCBarControl.RowIndex &
ctlCBarControl.position
End If
Next
End Sub
I get the following results:
rowindex position
Menubar 2 1
Standard 3 1
Formatting 20 1 <--high rowindex, but docked in the top
group of toolbars
VB 21 1<--high rowindex, but docked in the top
group of toolbars
Control tbx 21 1 <--same rowindex and position as VB, but
docked to the right of the VB toolbar
Drawing 1 3 <-- drawing is docked at the bottom of
the window
MyCustom 22 1 <-- manually docked below the VB/Control tbx
at top of screen
A few problems: This also shows toolbars at the bottom of the screen, so
I'll need to create a max value to pay attention to- if a toolbar returns a
row/position below halfway down the screen, then it isn't one I need to
worry about.
So these parameters don't mean what I'd intuitively think they mean; I did
see msoBarRowFirst but that moves the toolbar to the top of the toolbar
group, which isn't my desired outcome. Notably, when I did test that, then
moved the MyCustom back to the target position, it's rowindex changed to 23.
Does position 1 mean top of screen, 2 means something else (maybe side
docked, or floating) and 3 means bottom? If so, I can limit my evaluation to
the position=1 toolbars.
Can anyone shed some light on rowindex for me? Is there a different property
I should be looking at? Should I just set the rowindex of my new toolbar to
a value of one higher than any other position 1 toolbar?
Thanks!!
Keith
show up when my workbook is open (via an add-in) and only when my workbook
is active (will code that later).
To make sure it is always easy to find the toolbar, I want to check the
location of all visible toolbars at the top of the Excel window, and dock
this new custom toolbar on a new row below the lowest pre-existing docked
toolbar.
I've been playing with different characteristics of the visible toolbars,
but haven't found a consistent way to identify the lowest active toolbar and
create mine just below it. for example:
Sub testbarlocations()
For Each ctlCBarControl In Application.CommandBars
If ctlCBarControl.Visible = True Then
MsgBox ctlCBarControl.Name & " " & ctlCBarControl.RowIndex &
ctlCBarControl.position
End If
Next
End Sub
I get the following results:
rowindex position
Menubar 2 1
Standard 3 1
Formatting 20 1 <--high rowindex, but docked in the top
group of toolbars
VB 21 1<--high rowindex, but docked in the top
group of toolbars
Control tbx 21 1 <--same rowindex and position as VB, but
docked to the right of the VB toolbar
Drawing 1 3 <-- drawing is docked at the bottom of
the window
MyCustom 22 1 <-- manually docked below the VB/Control tbx
at top of screen
A few problems: This also shows toolbars at the bottom of the screen, so
I'll need to create a max value to pay attention to- if a toolbar returns a
row/position below halfway down the screen, then it isn't one I need to
worry about.
So these parameters don't mean what I'd intuitively think they mean; I did
see msoBarRowFirst but that moves the toolbar to the top of the toolbar
group, which isn't my desired outcome. Notably, when I did test that, then
moved the MyCustom back to the target position, it's rowindex changed to 23.
Does position 1 mean top of screen, 2 means something else (maybe side
docked, or floating) and 3 means bottom? If so, I can limit my evaluation to
the position=1 toolbars.
Can anyone shed some light on rowindex for me? Is there a different property
I should be looking at? Should I just set the rowindex of my new toolbar to
a value of one higher than any other position 1 toolbar?
Thanks!!
Keith