docking a custom toolbar

[JNW]

Can a toolbar be docked?

I've created an add-in that creates a toolbar then it adds two buttons. All
this works perfect. I would like for the toolbar to automatically dock, or
once docked the first time by the user it will always remain there when Excel
is opened (like all the rest of the toolbars.

Any ideas? (see my code below)

Private Sub Workbook_Open()
Application.CommandBars("CN Tools").Delete

Set cmdCN = Application.CommandBars.Add("CN Tools", , , False)

With cmdCN
.Visible = True

'CreateRedUnderlineButton
With .Controls.Add(Type:=msoControlButton)
.TooltipText = "Underlines number in red"
.Picture = LoadPicture("H:\Tax return underline icon.bmp")
.OnAction = "NumberForTaxReturn"
End With

'Create button to number sheets
With .Controls.Add(Type:=msoControlButton)
.TooltipText = "Numbers all sheets in workbook"
.OnAction = "NumberAllSheetsInWorkbook"
.Picture = LoadPicture("H:\Number Sheets icon.bmp")
End With
End With
End Sub

 

[Jim Cone]

Use the Position property...
..Position = msoBarTop

-and the RowIndex property...
..RowIndex = Application.CommandBars("Formatting").RowIndex

Also, you may have to set the .Left property.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"JNW" <[email protected]>
wrote in message
Can a toolbar be docked?
I've created an add-in that creates a toolbar then it adds two buttons. All
this works perfect. I would like for the toolbar to automatically dock, or
once docked the first time by the user it will always remain there when Excel
is opened (like all the rest of the toolbars.
Any ideas? (see my code below)

Private Sub Workbook_Open()
Application.CommandBars("CN Tools").Delete

Set cmdCN = Application.CommandBars.Add("CN Tools", , , False)

With cmdCN
.Visible = True

'CreateRedUnderlineButton
With .Controls.Add(Type:=msoControlButton)
.TooltipText = "Underlines number in red"
.Picture = LoadPicture("H:\Tax return underline icon.bmp")
.OnAction = "NumberForTaxReturn"
End With

'Create button to number sheets
With .Controls.Add(Type:=msoControlButton)
.TooltipText = "Numbers all sheets in workbook"
.OnAction = "NumberAllSheetsInWorkbook"
.Picture = LoadPicture("H:\Number Sheets icon.bmp")
End With
End With
End Sub

 

[JNW]

That did the trick. I added the position along with rowIndex. And to top it
off a Left = 1 to set it after the formatting bar.

Thanks!

 

[Wei]

JNW,
I met the similar problem, the only different is that I need to dock the
toolbar at the right bottom of Excel. I am a newbie of Excel VBA, can you
elaborate how did you make it work?
Thanks!
Wei