Commandbar Position

[jkend69315]

I asked this question earlier but got no reply. I want my commandbar
(a toolbar attached to a workbook) to always appear as the bottom-most
commandbar at the top of the screen, centered horizontally, when the
workbook opens. Seems like this shouldn't be all that difficult, but
apparently it is a problem in Excel (2002). Thanks! James

 

[Bernie Deitrick]

Try something like this macro below.

Note that attaching commandbars to worksbooks is a bad thing for your end users - much better to
create and delete the commandbar on the fly - lots of code available to show how.

HTH,
Bernie
MS Excel MVP

Sub PositionCommandbar()
Dim cbBar As CommandBar

Set cbBar = Application.CommandBars("Test")

With cbBar
.Visible = True
.Position = msoBarTop
.Left = 2000 'Put it way to the right
.Left = .Left / 2 ' adjust it to the center
End With

End Sub

 

[jkend69315]

I'll be chewing on your reply for a while. Thank you.

 

[jkend69315]

Say I already have a commandbar for which I have created macro calls
and buttons with faces. Is there a way to store and nab this toolbar
and create (install) it instead of having to create the macro calls and
faces everytime? Thanks

 

[Bernie Deitrick]

Try attaching it to the workbook, and use the workbook open event to show it:

Application.CommandBars("Test").Visible = True

And use the workbook close event to delete it.

Application.CommandBars("Test").Visible = False

(Or delete it - it should stay attached to the workbook)

HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

Ooops,

And use the workbook close event to delete it.

should be

And use the workbook close event to HIDE it.

Sorry,

HTH,
Bernie
MS Excel MVP