Macro Help on Toolbar

[dok112]

Ok, so I figured out how to create a macro that will creat a new toolba
for me, but when I run it, it creates the toolbar but none of th
buttons are correct. The toolbar is supposed to hold buttons that ru
other macros in the workbook. Am I doing something wrong?

From looking in VBA, it is only recording the below, however I a
entering more info during the recording of the macro. Any help will b
greatly appreciated!!


Sub TLD_Week_1()
'
' TLD_Week_1 Macro
' Macro recorded 6/13/2004 by Thomas
'

'
Application.CommandBars.Add(Name:="TLD Week 1 Performance").Visibl
= True
Application.CommandBars("TLD Week 1 Performance").Controls.Ad
Type:= _
msoControlButton, ID:=2950, Before:=1
Application.CommandBars("Stop Recording").Visible = False
Application.CommandBars("TLD Week 1 Performance").Visible = False
Application.WindowState = xlMinimized
End Su

 

[Dave Peterson]

Here's a shell that I keep when I want to add a custom menubar:

In a general module:

Option Explicit
Sub create_menubar()

Dim i As Long

Dim mac_names As Variant
Dim cap_names As Variant
Dim tip_text As Variant

Call remove_menubar

mac_names = Array("mac1", _
"mac2", _
"mac3")

cap_names = Array("caption 1", _
"caption 2", _
"caption 3")

tip_text = Array("tip 1", _
"tip 2", _
"tip 3")

With Application.CommandBars.Add
.Name = "Test99"
.Left = 200
.Top = 200
.Protection = msoBarNoProtection
.Visible = True
.Position = msoBarFloating

For i = LBound(mac_names) To UBound(mac_names)
With .Controls.Add(Type:=msoControlButton)
.OnAction = ThisWorkbook.Name & "!" & mac_names(i)
.Caption = cap_names(i)
.Style = msoButtonIconAndCaption
.FaceId = 71 + i
.TooltipText = tip_text(i)
End With
Next i
End With
End Sub

Sub remove_menubar()
On Error Resume Next
Application.CommandBars("Test99").Delete
On Error GoTo 0
End Sub


Under Thisworkbook:

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call remove_menubar
End Sub

Private Sub Workbook_Open()
Call create_menubar
End Sub

====
The Mac_names, cap_names, and tip_text are set up for 3 elements. But just
delete/add from each of these and the code will loop through them (even if
there's just one) to add buttons to a temporary toolbar.

(make sure you have the same number of elements for each array.)

 

[dok112]

Ok, I see how to do this now. But I do have a quick question. Th
.Position is set to floating. What would be the code to set it to b
attached to the toolbar?


With Application.CommandBars.Add
.Name = "Transition Lab Week 1 Productivity"
.Left = 200
.Top = 200
.Protection = msoBarNoProtection
.Visible = True
.Position = msoBarFloatin

 

[Dave Peterson]

Highlight .position in the code and hit F1.

You'll see this:
Returns or sets the position of a command bar. Read/write MsoBarPosition.

Click on MsoBarPosition and you'll see these options.

msoBarBottom
msoBarFloating
msoBarLeft
msoBarMenuBar
msoBarPopup
msoBarRight
msoBarTop


I like the floating to make sure that the user (including me!) sees this new
toolbar.