CommandBar docking with VBA

[Clif McIrvin]

Excel 2003

I'm having trouble getting command bars to appear where I want them.

I have one command bar ("Break Data") that is always visible; but for
some functions it is docked on the right instead of at the top. This
has been working just fine until I created a new command bar ("Reports")
to use in conjuction with it.

When Break Data (10 buttons) is docked on the right side of the screen,
I want Reports (2 buttons) also docked on the right side. When Break
Data is docked at the top of the screen, Reports is not visible.

When i run the code below, Excel makes a two column wide dock; even
though both command bars fit one below the other. I can drag either
command bar and Excel will change the dock to a single column; but they
always revert to a two-wide format when I re-run the macro.

The code to bring up Reports:

CommandBars("Break Data").Position = msoBarRight
CommandBars("Break Data").Top = 296
' CommandBars("Break Data").Left = 0
' CommandBars("Reports").Top = 90
' CommandBars("Reports").Left = 0
CommandBars("Reports").Visible = True

(I have tried various combination of the above without success.)

To Hide Reports and return Break Data to the top of the screen:

With CommandBars("Break Data")
.Position = msoBarTop
.RowIndex = 20
.Left = 790
.Top = 51
End With
CommandBars("Reports").Visible = False

What am I missing here?

 

[Jim Cone]

Application.CommandBars("Reports").Position = msoBarRight
Application.CommandBars("Reports").RowIndex = _
Application.CommandBars("Break Data").RowIndex
--
Jim Cone
Portland, Oregon USA



"Clif McIrvin" <[email protected]>
wrote in message
Excel 2003
I'm having trouble getting command bars to appear where I want them.

I have one command bar ("Break Data") that is always visible; but for
some functions it is docked on the right instead of at the top. This
has been working just fine until I created a new command bar ("Reports")
to use in conjuction with it.

When Break Data (10 buttons) is docked on the right side of the screen,
I want Reports (2 buttons) also docked on the right side. When Break
Data is docked at the top of the screen, Reports is not visible.

When i run the code below, Excel makes a two column wide dock; even
though both command bars fit one below the other. I can drag either
command bar and Excel will change the dock to a single column; but they
always revert to a two-wide format when I re-run the macro.

The code to bring up Reports:

CommandBars("Break Data").Position = msoBarRight
CommandBars("Break Data").Top = 296
' CommandBars("Break Data").Left = 0
' CommandBars("Reports").Top = 90
' CommandBars("Reports").Left = 0
CommandBars("Reports").Visible = True

(I have tried various combination of the above without success.)
To Hide Reports and return Break Data to the top of the screen:

With CommandBars("Break Data")
.Position = msoBarTop
.RowIndex = 20
.Left = 790
.Top = 51
End With
CommandBars("Reports").Visible = False
What am I missing here?

 

[Clif McIrvin]

That works!

Thanks, Jim.

--
Clif

Application.CommandBars("Reports").Position = msoBarRight
Application.CommandBars("Reports").RowIndex = _
Application.CommandBars("Break Data").RowIndex
--
Jim Cone
Portland, Oregon USA



"Clif McIrvin" <[email protected]>
wrote in message
Excel 2003
I'm having trouble getting command bars to appear where I want them.

I have one command bar ("Break Data") that is always visible; but for
some functions it is docked on the right instead of at the top. This
has been working just fine until I created a new command bar
("Reports")
to use in conjuction with it.

When Break Data (10 buttons) is docked on the right side of the
screen,
I want Reports (2 buttons) also docked on the right side. When Break
Data is docked at the top of the screen, Reports is not visible.

When i run the code below, Excel makes a two column wide dock; even
though both command bars fit one below the other. I can drag either
command bar and Excel will change the dock to a single column; but
they
always revert to a two-wide format when I re-run the macro.

The code to bring up Reports:

CommandBars("Break Data").Position = msoBarRight
CommandBars("Break Data").Top = 296
' CommandBars("Break Data").Left = 0
' CommandBars("Reports").Top = 90
' CommandBars("Reports").Left = 0
CommandBars("Reports").Visible = True

(I have tried various combination of the above without success.)
To Hide Reports and return Break Data to the top of the screen:

With CommandBars("Break Data")
.Position = msoBarTop
.RowIndex = 20
.Left = 790
.Top = 51
End With
CommandBars("Reports").Visible = False
What am I missing here?