CommandBars.Add problem

[Billy B]

I am trying to create a toolbar with a button on it in the Workbook.Activate
event. Below is the code so far. I error out at the With TBar .Name line so I
don't know if anything else is wrong. Help would be greately appreciated.

Sub MakeToolbarBtn()
'
' Create new toolbar and button
Dim TBar As CommandBar
Dim NewBtn As CommandBarButton

Set TBar = CommandBars.Add
With TBar
.Name = "WWEnviro"
.Top = 0
.Left = 0
.Visible = True
End With

Set NewBtn = CommandBars("WWEnviro").Controls.Add _
(Type:=msoControlButton)
With NewBtn
.FaceId = 300
.OnAction = "FixIt"
.Caption = "FixFormatting"
End With
On Error Resume Next
'On Error GoTo 0

End Sub

Thank you

 

[Carim]

Hi Billy,

Something along these lines :

Set MyBar = CommandBars.Add(Name:="WWEnviro", _
Position:=msoBarFloating, temporary:=True)

With MyBar
.Top = 175
.Left = 650

etc ....

HTH
Cheers
Carim

 

[John]

Billy,

Check if the commandbar is already there and you're not trying to add a
commandbar with the same name? Run this procedure and check the result in
the Immediate window:

Public Sub CheckCommandBars()
Dim cmdBar As CommandBar
For Each cmdBar In Application.CommandBars
If cmdBar.Name = "WWEnviro" Then
Debug.Print "********************" & _
cmdBar.Name & "********************"
Else
Debug.Print cmdBar.Name
End If
Next cmdBar
End Sub

Have you got any code to remove the commandbar in a Workbook_Close event?
If not add this:

Public Sub RemoveCustomToolbar()
On Error Resume Next
CommandBars.("WWEnviro").Delete
End Sub


It's also worth adding a check in the MakeToolBar procedure to delete any
old instances left by a crash, so add:

Sub MakeToolbarBtn()

' Create new toolbar and button
Dim TBar As CommandBar
Dim NewBtn As CommandBarButton

On Error Resume Next
CommandBars.("WWEnviro").Delete
On Error GoTo 0


Hope that helps

Best regards

John