troubleshooting Question

VBA Created toolbar in Excel

Avatar of chayes050498
chayes050498 asked on
Microsoft Office
7 Comments1 Solution1135 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
Brian Mulder
Freelance
Join our community to see this answer!
Unlock 1 Answer and 7 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros