Toolbar should not be on its own row!

[Craigm]

I create a toolbar that has three icons with the code below. When I
open the worksheet it appears at the bottom of the sheet on its own row
below the drawing tolbar.

Is it possible to have this toolbar open beside the drawing toolbar?
There is plenty of room and would give me a larger worksheet viewing
area.

Thanks,

Craig


Sub CreateAToolbar()
Const tBarName As String = "Service Maintenance"
'Delete CommandBar if it exists
On Error Resume Next
CommandBars(tBarName).Delete
On Error GoTo 0

'create CommandBar
CommandBars.Add Name:=tBarName

'define an object variable to refer to the CommandBar
With CommandBars(tBarName)
'add button use 1 to specify a blank custom face
With .Controls.Add(ID:=1)
..OnAction = "Inven1Import"
..FaceId = 9946
..Caption = "Inventory Import"
End With

With .Controls.Add(ID:=1)
..OnAction = "Mile1Import"
..FaceId = 9942
..Caption = "Mileage Import"
End With

With .Controls.Add(ID:=1)
..OnAction = "Master_Control"
..FaceId = 1763
..Caption = "Master Control"
..BeginGroup = True 'this adds the separator
bar
End With

'.Position = msoBarTop
..Position = msoBarBottom
..Visible = True 'display the toolbar
End With
End Sub

 

[Gary L Brown]

This is untested but I think will work.
First, we need to find out some information about the Drawing toolbar -
where it's left edge is, how wide it is, what row position it is at. Then,
we can place your toolbar next to it.

Step 1) Declare some variables. This coding goes right after the Procedure
name "Sub CreateAToolbar()"...

Dim iLeft As Integer 'left edge of Drawing Toolbar
Dim iWidth As Integer 'width of Drawing Toolbar
Dim iRowIndex As Integer 'Row # of Drawing Toolbar

Step 2) Check if the Drawing Toolbar is visible. This coding goes right
after the variables in step 1. If it is, get the information needed...

If CommandBars("Drawing").Visible = True Then
With CommandBars("Drawing")
iLeft = .Left 'left edge of Drawing Toolbar
iWidth = .Width 'width of Drawing Toolbar
iRowIndex = .RowIndex 'Row # of Drawing Toolbar
End With
End If

Step 3) Place your toolbar to the right of the Drawing toolbar if the
Drawing toolbar is visible. This coding goes right after the ".Visible =
True" line near the end of your procedure...

If CommandBars("Drawing").Visible = True Then
.Left = iLeft + iWidth 'Right edge of Drawing Toolbar...refering
to tBarName
.RowIndex = iRowIndex 'Same row as Drawing Toolbar...refering to
tBarName
End If

HTH,

 

[Jim Rech]

Works for me:

..Position = msoBarBottom
..Left = CommandBars("Drawing").Left + CommandBars("Drawing").Width + 10
..RowIndex = 1


--
Jim
message |
| I create a toolbar that has three icons with the code below. When I
| open the worksheet it appears at the bottom of the sheet on its own row
| below the drawing tolbar.
|
| Is it possible to have this toolbar open beside the drawing toolbar?
| There is plenty of room and would give me a larger worksheet viewing
| area.
|
| Thanks,
|
| Craig
|
|
| Sub CreateAToolbar()
| Const tBarName As String = "Service Maintenance"
| 'Delete CommandBar if it exists
| On Error Resume Next
| CommandBars(tBarName).Delete
| On Error GoTo 0
|
| 'create CommandBar
| CommandBars.Add Name:=tBarName
|
| 'define an object variable to refer to the CommandBar
| With CommandBars(tBarName)
| 'add button use 1 to specify a blank custom face
| With .Controls.Add(ID:=1)
| OnAction = "Inven1Import"
| FaceId = 9946
| Caption = "Inventory Import"
| End With
|
| With .Controls.Add(ID:=1)
| OnAction = "Mile1Import"
| FaceId = 9942
| Caption = "Mileage Import"
| End With
|
| With .Controls.Add(ID:=1)
| OnAction = "Master_Control"
| FaceId = 1763
| Caption = "Master Control"
| BeginGroup = True 'this adds the separator
| bar
| End With
|
| '.Position = msoBarTop
| Position = msoBarBottom
| Visible = True 'display the toolbar
| End With
| End Sub
|
|
| --
| Craigm
| ------------------------------------------------------------------------
| Craigm's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=24381
| View this thread: http://www.excelforum.com/showthread.php?threadid=499513
|

 

[Gary L Brown]

Boy!!!
Jim's sure is a lot more elegant than mine, but I would still test for the
Drawing toolbar being open. Something like...

If CommandBars("Drawing").Visible = True Then
.Left = CommandBars("Drawing").Left + CommandBars("Drawing").Width + 1

.RowIndex = CommandBars("Drawing").RowIndex
End If

HTH,

 

[Craigm]

Thank you both! I could not have figured this out.

I had to make two subtitutions...iLeft instead of .left and iRowIndex
for .RowIndex when checking to see if the Drawing toolbar is visible.

This works great! Thank you again.

Craigm

iLeft = CommandBars("Drawing").Left + CommandBars("Drawing").Width + 1
iRowIndex = CommandBars("Drawing").RowIndex