Creating a CommandBar - what's wrong?

[Eager2Learn]

I am working on creating a commandbar and buttons upon the workshee
opening, and then delete it when the worksheet closes. I am new a
this, and running into a problem.

Here is my code so far, it doesn't work. In the Workbook_Open I put
Call to this:


Private Sub CreateCommandBar()
Dim TBar As CommandBar
Set TBar = CommandBars.Add
With TBar
.Name = "OPLToolBar"
.Top = 0
.Left = 0
.Visible = True
End With

Set NewBtn1 = CommandBars("OPLToolBar").Controls.Add _
(Type:=msoControlButton)
With NewBtn1
.FaceId = 481
.OnAction = "Sort"
.Caption = "Main Sort"
.Style = msoButtonIconAndCaption
End With

End Sub

Thanks in advance for the help.
Jonatha

 

[Greg Wilson]

Try fully qualifying the CommandBars property. I suggest
making the tool bar Temporary to avoid having to delete
them. I havn't found a problem with this. Correct for
word wrap:-

Private Sub CreateCommandBar()
Dim TBar As CommandBar
Set TBar = Application.CommandBars.Add(Temporary:=True)
With TBar
..Name = "OPLToolBar"
..Top = 0
..Left = 0
..Visible = True
End With

Set NewBtn1 = Application.CommandBars
("OPLToolBar").Controls.Add _
(Type:=msoControlButton)
With NewBtn1
..FaceId = 481
..OnAction = "Sort"
..Caption = "Main Sort"
..Style = msoButtonIconAndCaption
End With

End Sub

 

[Bob Phillips]

The only thing I can find wrong with it is that all of the properties are
not dot qualified. Doing that it worked for me

Private Sub CreateCommandBar()
Dim TBar As CommandBar, NewBtna
Set TBar = CommandBars.Add
With TBar
..name = "OPLToolBar"
..top = 0
..left = 0
..Visible = True
End With

Set newBtn1 = CommandBars("OPLToolBar").Controls.Add _
(Type:=msoControlButton, temporary:=True)
With newBtn1
..FaceId = 481
..OnAction = "Sort"
..Caption = "Main Sort"
..Style = msoButtonIconAndCaption
End With

End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Eager2Learn]

So... are two dots needed? I am getting an error when I use two dot
for the properties

 

[Greg Wilson]

The two leading dots are corruption of both Bob's and my
code. There should only one leading period.

Your code works for me assuming that the code is located
in the ThisWorkbook module. If it is located in another
module then the Private statement will make it unavailable.

I always fully qualify the CommandBars property during the
Workbook_Open event because I have found that it often
fails otherwise. When not qualified, it normally defaults
to the Application object. However, it seems that during
the Workbook_Open event this default behaviour has not yet
taken effect. Just an amateur's perspective.

So if your code already resides in the ThisWorkbook module
and it still doesn't work then I suggest fully qualifying
the CommandBars statements as "Application.CommandBars"

Regards,
Greg

 

[Eager2Learn]

Your advice worked perfect!! I needed to qualify the application.
Thanks,