Getting custom commandbar to show on Add-ins tab

  • Thread starter Thread starter JGPatrick
  • Start date Start date
J

JGPatrick

I've created a custom toolbar for Access 2007 with the following code:

Sub CreateToolbar()

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

Dim cbar As Office.CommandBar 'I have the needed reference to Office library
Dim cntrl As Office.CommandBarControl

Set cbar = Application.CommandBars.Add("PracticeCommandBar", msoBarTop, _
Temporary:=True)
Set cntrl = Application.CommandBars("PracticeCommandBar").Controls.Add( _
msoControlButton, Temporary:=True)

cntrl.Caption = "Hello"
cntrl.OnAction = "=MsgBox('Hello')"

cbar.Enabled = True
cbar.Visible = True

Set cbar = Nothing
Set cntrl = Nothing

End Sub

When the code runs, the Add-ins tab appears. It has one group, called
"custom toolbars". My toolbar (with my one control button) does not show in
the
group.

How can I get my toolbar to actually show up?
 
It turns out that the toolbar is in fact added to the custom toolbars group
of the add-ins tab, but that the button is invisible unless you mouse over it.

Don't know how to make the button visible.
Adding cntrl.visible = True to the code does not solve the problem.

I notice that there is no FaceID property for the control button, as
there is in early Office versions. Perhaps that is the problem?
 
OK, managed to find a reasonable fix.

If you read through this thread, the problem is that the caption does not
get placed on the control button. Since there is also no FaceID for the
button, it appears invisible until you mouse over it.

I tried a large number of variations of the code, to no avail. However, I
found that if you attach the control button to a menu, you can then attach a
caption to it.
Don't ask me why this is so, I just know I got it to work.

So, you have to place all your control buttons under menus in custom
toolbars, at least from my attempts to create them in Access VBA. Below is
the code that got it to work:

Sub CreateCustomCBar0()

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

Dim cbar As Office.CommandBar
Dim cntrl As Office.CommandBarControl

Set cbar = Application.CommandBars.Add(Name:="PracticeCommandBar", _
Position:=msoBarTop, Temporary:=True)

Set cntrl = cbar.Controls.Add(Type:=msoControlPopup, Temporary:=True)
cntrl.Caption = "&Menu"

Set cntrl = cbar.Controls(1).Controls.Add( _
Type:=msoControlButton, Temporary:=True)
cntrl.Caption = "&Hello"
cntrl.OnAction = "=MsgBox('Hello')"

cbar.Visible = True

Set cbar = Nothing
Set cntrl = Nothing

End Sub
 
Back
Top