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.

Ben · Accepted Answer · 2015-01-20 18:36:39Z

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, 2015 at 18:36

Ben

10.1k5 gold badges43 silver badges42 bronze badges

answered Jan 20, 2015 at 18:02

Phil

212 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Daniel Olsen · Accepted Answer · 2013-02-01 00:00:10Z

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 1, 2013 at 0:00

Daniel Olsen

1,0503 gold badges16 silver badges29 bronze badges

2 Comments

s_a Over a year ago

The link is broken - if you remember how you did this, I'd be nice to know the answer.

Christian Over a year ago

Please find an archived version of the page here: web.archive.org/web/20130406222820/http://support.microsoft.com/…

Nelda.techspiress · Accepted Answer · 2015-05-28 22:47:44Z

0

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.)

answered May 28, 2015 at 22:47

Nelda.techspiress

65112 silver badges32 bronze badges

Collectives™ on Stack Overflow

Access VBA CommandBarButton.OnAction doesn't work

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related