asked on

VBA Created toolbar in Excel

Environment: Win2K, SP2, Office 2000 SP2, VB 6 SP5

I am trying to create a toolbar for Excel using VB 6 not the Excel VB Editor. Research through MSDN, Experts Exchange, and some Excel Programming books has led me to create the following code. As you can see the second button (.Tag = "Button2") has its .OnAction Property set to "=HandleButtons()" and HandleButtons is a Sub defined below. According to MSDN and some of the answers to other questions on Experts Exchange, this should work, but when I click the second button, I get an error message stating "The macro '=HandleButtons()' cannot be found". Can anyone tell me why this isn't working and/or help me fix it? Thanks.

Public Const TLBR_NAME As String = "MyToolbar"
Private p_tlbr As Office.CommandBar

Private Sub IDTExtensibility2_OnConnection(ByVal Application As Object, _
ByVal ConnectMode As AddInDesignerObjects.ext_ConnectMode, _
ByVal AddInInst As Object, custom() As Variant)

CreateToolbar Application

End Sub

Function CreateToolbar(ByVal Application As Object)

Dim p_ctl As CommandBarControl
On Error Resume Next

Set p_tlbr = Application.CommandBars(TLBR_NAME)
If p_tlbr Is Nothing Then
Set p_tlbr = Application.CommandBars.Add(TLBR_NAME)
Set p_ctl = p_tlbr.Controls.Add(msoControlButton)
With p_ctl
.BeginGroup = True
.Style = msoButtonCaption
.Caption = "MyButton"
.Enabled = False
.Tag = "Button1"
End With

Set p_ctl = p_tlbr.Controls.Add(msoControlButton)
With p_ctl
.Style = msoButtonIcon
.FaceId = 1951
.Enabled = True
.Tag = "Button2"
.OnAction = "=HandleButtons()"
End With

Set p_ctl = p_tlbr.Controls.Add(msoControlButton)
With p_ctl
.Style = msoButtonIcon
.FaceId = 548
.Enabled = False
.Tag = "Button3"
End With

Set p_ctl = p_tlbr.Controls.Add(msoControlButton)
With p_ctl
.Style = msoButtonIcon
.FaceId = 265
.Enabled = False
.Tag = "Button4"
End With

Set p_ctl = p_tlbr.Controls.Add(msoControlButton)
With p_ctl
.Style = msoButtonIcon
.FaceId = 33
.Enabled = True
.Tag = "Button5"
End With

Set p_ctl = p_tlbr.Controls.Add(msoControlButton)
With p_ctl
.Style = msoButtonIcon
.FaceId = 325
.Enabled = False
.Tag = "Button6"
End With

Set p_ctl = p_tlbr.Controls.Add(msoControlButton)
With p_ctl
.Style = msoButtonIcon
.FaceId = 345
.Enabled = True
.Tag = "MAOT7"
.ToolTipText = "Help"
End With
End If

p_tlbr.Visible = True
End Function

Public Sub HandleButtons()
MsgBox "HandleButtons Here", vbApplicationModal
End Sub

sebastienm

chayes,
Instead of setting .OnAction to
"=HandleButtons()"
try
"HandleButtons"

Regards,
Sébastien

chayes050498

ASKER

Have tried that. Same result only without the equal sign.

sebastienm

Where is the HandleButtons sub located? In an Excel
workbook module? in a COM add-in? elsewhere ?

When it is in a workbook module, i use
something like
Dim wkb as Workbook
Set wkb = ... 'workbook containing the code for
' the HandleButtons sub

.onAction = "'" & wkb.Name & "'!" & wkb.VBProject.Name _
& ".HandleButtons"

Regards,
Sébastien

chayes050498

ASKER

Sébastien,

It's in a COM Add-in. IDTExtensibility2_OnConnection is in the AddInDesigner file. CreateToolbar and HandleButtons are in a VB Module.

Thanks,

Chris

ASKER CERTIFIED SOLUTION

Brian Mulder

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

chayes050498

ASKER

Bruintje,

That helps significantly, can't believe I didn't come across that article in MSDN in my searches. Now the problem is that if I add a second button, the first button does not perform any actions, but the second calls the p_ctl_click method as it should.

Do I need to create an array of the buttons (if so how?) or is there someother way I can get multiple buttons to work on my new toolbar?

Thanks - Chris

Public gobjAppInstance As Object
Dim WithEvents p_ctl As Office.CommandBarButton

Private Sub IDTExtensibility2_OnStartupComplete(custom() As Variant)
'Debug.Print "OnStartupComplete"
Dim p_tlbr As Office.CommandBar

On Error Resume Next

Set p_tlbr = gobjAppInstance.CommandBars("MyToolbar")
If p_tlbr Is Nothing Then
Set p_tlbr = gobjAppInstance.CommandBars.Add("MyToolbar")

Set p_ctl = p_tlbr.Controls.Add(msoControlButton)
With p_ctl
.BeginGroup = True
.Style = msoButtonCaption
.Caption = "MyButton1"
.Enabled = True
.Tag = "Button1"
.OnAction = "!<MyAddIn.Connect>"
.Parameter = .Tag
.Visible = True
End With

Set p_ctl = p_tlbr.Controls.Add(msoControlButton)
With p_ctl
.Style = msoButtonIcon
.FaceId = 1951
.Enabled = True
.Tag = "MyButton2"
.ToolTipText = "MyButton2"
.OnAction = "!<MyAddIn.Connect>"
.Parameter = .Tag
End With

p_tlbr.Visible = True

End If

End Sub

Private Sub p_ctl_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
MsgBox "Our CommandBar button was pressed!"
End Sub

chayes050498

ASKER

I had to create each button as a different Office.CommandBarButton, but it works.

Thanks,