Access VBA CommandBarButton.OnAction doesn't work

Question

I'm trying to create my own toolbar button (commandbarbutton) in Access VBA Editor. Its a button to run a procedure in a public module called "RunTests". I made the button like so:

Public Sub CreateToolbar()
    Dim cmdBar As CommandBar
    Dim cmdButton As CommandBarButton
    Set cmdBar = Application.VBE.CommandBars.Add("Run Tests")
    Set cmdButton = cmdBar.Controls.Add(msoControlButton)
    cmdButton.FaceId = 558
    cmdButton.DescriptionText = "Run the tests"
    cmdButton.Caption = "Run Tests"
    cmdButton.OnAction = "RunTests"
    cmdButton.Enabled = True
    cmdButton.Style = msoButtonIconAndCaption
    cmdBar.Visible = True
End Sub

I ran the procedure as a test. And it created the button just fine. But the "click" action doesn't respond. The button doesn't seem to be clickable. Does anybody know why?

Edit: Found the solution: http://support.microsoft.com/default.aspx?scid=kb;en-us;Q280607 - I will answer my own question as soon as I can to close it.

score 2 · Answer 1 · edited Jan 20 '15 at 18:36

2

The key for me was to make "RunTests" a public subroutine. When it was private it didn't work.

cmdButton.OnAction = "=RunTests()"

public sub RunTests()

private sub RunTests() does not work.

edited Jan 20 '15 at 18:36

Ben

10,056
5
41
42

answered Jan 20 '15 at 18:02

Phil

21
2

score 0 · Accepted Answer · answered Feb 01 '13 at 00:00

0

Found the solution - To make it work you have to implement the workaround described here: http://support.microsoft.com/default.aspx?scid=kb;en-us;Q280607

answered Feb 01 '13 at 00:00

Daniel Olsen

1,020
2
15
27

The link is broken - if you remember how you did this, I'd be nice to know the answer. – s_a Oct 16 '15 at 18:23
1

Please find an archived version of the page here: https://web.archive.org/web/20130406222820/http://support.microsoft.com/default.aspx?scid=kb;en-us;Q280607 – Christian Junk Oct 06 '21 at 07:51

score 0 · Answer 3 · answered May 28 '15 at 22:47

Unfortunately I tried to be fancy and define my button and menus in a class. The OnAction subroutine was never executed while it was defined locally as part of the class. It would only execute when defined outside of the class definition.

When I redefined my class to be just a module containing many subs and functions, then the OnAction subroutine was executed. Might have to do with scope and visibility or that I didn't have the OnAction semantics correct for a call to a class method. Bottom line is to define the button/menu/widget in a module and it will be much easier. (I was using Excel VBA but I think Access and Excel VBA are the same.)

Access VBA CommandBarButton.OnAction doesn't work

3 Answers3