The code below is supposed to create a toolbar with one tool on it which
runs a macro which is in a separate module (same workbook)

Public Const sCreateTribTR As String = "CreateTribTr"

Sub Auto_Open()
Call CreateMenubar
With CommandBars(sCreateTribTR)
.Enabled = False
.Visible = False
End With

End Sub

Sub Auto_Close()
Call RemoveMenubar
End Sub

Sub RemoveMenubar()
On Error Resume Next
Application.CommandBars(sCreateTribTR).Delete
On Error GoTo 0
End Sub

Sub CreateMenubar()

Call RemoveMenubar

With Application.CommandBars.Add
.Name = sCreateTribTR
.Left = 200
.Top = 200
.Protection = msoBarNoProtection
.Visible = True
.Position = msoBarTop
.Left = CommandBars(sCreateTribTR).Left +
CommandBars(sCreateTribTR).Width
.RowIndex = CommandBars(sCreateTribTR).RowIndex

With CommandBars(sCreateTribTR).Controls.Add(Type:=msoC ontrolButton)
.OnAction = "'" & ThisWorkbook.Name & "'!" & "CreateTR"
.Caption = "CreateTR"
.Style = msoButtonCaption
.TooltipText = "Create TR"
End With

With CommandBars(sCreateTribTR).Controls.Add(Type:=msoC ontrolButton, ID _
:=2950, Befo=2)
End With
End With
End Sub

Public Sub CreateTR()
Call CreateTribalSheet
End Sub

It compiles and runs, just nothing happnes. No toolbar. Anybody see what
I'm missing?

On Tue, 7 Apr 2009 13:35:15 -0600, salgud wrote:

The code below is supposed to create a toolbar with one tool on it which
runs a macro which is in a separate module (same workbook)

Public Const sCreateTribTR As String = "CreateTribTr"

Sub Auto_Open()
Call CreateMenubar
With CommandBars(sCreateTribTR)
.Enabled = False
.Visible = False
End With

End Sub

Sub Auto_Close()
Call RemoveMenubar
End Sub

Sub RemoveMenubar()
On Error Resume Next
Application.CommandBars(sCreateTribTR).Delete
On Error GoTo 0
End Sub

Sub CreateMenubar()

Call RemoveMenubar

With Application.CommandBars.Add
.Name = sCreateTribTR
.Left = 200
.Top = 200
.Protection = msoBarNoProtection
.Visible = True
.Position = msoBarTop
.Left = CommandBars(sCreateTribTR).Left +
CommandBars(sCreateTribTR).Width
.RowIndex = CommandBars(sCreateTribTR).RowIndex

With CommandBars(sCreateTribTR).Controls.Add(Type:=msoC ontrolButton)
.OnAction = "'" & ThisWorkbook.Name & "'!" & "CreateTR"
.Caption = "CreateTR"
.Style = msoButtonCaption
.TooltipText = "Create TR"
End With

With CommandBars(sCreateTribTR).Controls.Add(Type:=msoC ontrolButton, ID _
:=2950, Befo=2)
End With
End With
End Sub

Public Sub CreateTR()
Call CreateTribalSheet
End Sub

It compiles and runs, just nothing happnes. No toolbar. Anybody see what
I'm missing?

Thanks, I figured it out!

I will be honest i didn't read your entire post. However, as far a creating
custom menus or tool bars a table driven method is the easiest way to go.
Have a look here

http://spreadsheetpage.com/index.php..._custom_menus/

"salgud" wrote:

The code below is supposed to create a toolbar with one tool on it which
runs a macro which is in a separate module (same workbook)

Public Const sCreateTribTR As String = "CreateTribTr"

Sub Auto_Open()
Call CreateMenubar
With CommandBars(sCreateTribTR)
.Enabled = False
.Visible = False
End With

End Sub

Sub Auto_Close()
Call RemoveMenubar
End Sub

Sub RemoveMenubar()
On Error Resume Next
Application.CommandBars(sCreateTribTR).Delete
On Error GoTo 0
End Sub

Sub CreateMenubar()

Call RemoveMenubar

With Application.CommandBars.Add
.Name = sCreateTribTR
.Left = 200
.Top = 200
.Protection = msoBarNoProtection
.Visible = True
.Position = msoBarTop
.Left = CommandBars(sCreateTribTR).Left +
CommandBars(sCreateTribTR).Width
.RowIndex = CommandBars(sCreateTribTR).RowIndex

With CommandBars(sCreateTribTR).Controls.Add(Type:=msoC ontrolButton)
.OnAction = "'" & ThisWorkbook.Name & "'!" & "CreateTR"
.Caption = "CreateTR"
.Style = msoButtonCaption
.TooltipText = "Create TR"
End With

With CommandBars(sCreateTribTR).Controls.Add(Type:=msoC ontrolButton, ID _
:=2950, Befo=2)
End With
End With
End Sub

Public Sub CreateTR()
Call CreateTribalSheet
End Sub

It compiles and runs, just nothing happnes. No toolbar. Anybody see what
I'm missing?

On Tue, 7 Apr 2009 12:56:05 -0700, Jeff wrote:

I will be honest i didn't read your entire post. However, as far a creating
custom menus or tool bars a table driven method is the easiest way to go.
Have a look here

http://spreadsheetpage.com/index.php..._custom_menus/

"salgud" wrote:

The code below is supposed to create a toolbar with one tool on it which
runs a macro which is in a separate module (same workbook)

Public Const sCreateTribTR As String = "CreateTribTr"

Sub Auto_Open()
Call CreateMenubar
With CommandBars(sCreateTribTR)
.Enabled = False
.Visible = False
End With

End Sub

Sub Auto_Close()
Call RemoveMenubar
End Sub

Sub RemoveMenubar()
On Error Resume Next
Application.CommandBars(sCreateTribTR).Delete
On Error GoTo 0
End Sub

Sub CreateMenubar()

Call RemoveMenubar

With Application.CommandBars.Add
.Name = sCreateTribTR
.Left = 200
.Top = 200
.Protection = msoBarNoProtection
.Visible = True
.Position = msoBarTop
.Left = CommandBars(sCreateTribTR).Left +
CommandBars(sCreateTribTR).Width
.RowIndex = CommandBars(sCreateTribTR).RowIndex

With CommandBars(sCreateTribTR).Controls.Add(Type:=msoC ontrolButton)
.OnAction = "'" & ThisWorkbook.Name & "'!" & "CreateTR"
.Caption = "CreateTR"
.Style = msoButtonCaption
.TooltipText = "Create TR"
End With

With CommandBars(sCreateTribTR).Controls.Add(Type:=msoC ontrolButton, ID _
:=2950, Befo=2)
End With
End With
End Sub

Public Sub CreateTR()
Call CreateTribalSheet
End Sub

It compiles and runs, just nothing happnes. No toolbar. Anybody see what
I'm missing?


Thanks!