Access VBA CommandBarButton.OnAction doesn't work

[Sawyer]

Hello,

My objective is to add a Menu Item to the VBA Editor toolbar. I would be satisfied with having a new menu item, new toolbar or adding a menu item to an existing toolbar menu item (ie, Tools).

I've searched various forums, blogs, etc. and although I have found several examples of how to use the VBA Extensibililty library to add menu items with good information. Sadly, they come close, but don't really work. Everything I have found is quite old. The two best leads I have found are:

1. Chip Pearson's Creating Menu Items in the VBA Editor web pages: (http://www.cpearson.com/excel/vbemenus.aspx)
2. Daniel Olson's post in StackOverflow: (https://stackoverflow.com/questions/10379813/access-vba-commandbarbutton-onaction-doesnt-work)

In both cases, the button or toolbar is created and is visible and clickable in the VBA Editor Toolbar.

The problem is that in both cases, when the menu button is clicked, the subroutine or function (I'll reference these as procedures in the rest of this question) attached to the menu button isn't launched.

So, I'm nearly there.

Several leads mention that the procedure needs to be in a standard module and is a public procedure. Mine are. Of course, several references need to be added to Access, they are.

Daniel Olsen indicated that he found his solution in a Microsoft Support Article at link http://support.microsoft.com/default...;en-us;Q280607. That link doesn't work.

The code for Chip Pearson's instructions is quite long so rather than include the code in this post, please follow the link to his pages identified above. His solution is based on Excel but the process should be the same. I did need to change ThisWorkbook.Name to CurrentProject.Name in the code.

The code for Daniel Olson's solution is short and simple. I've included that code.

PublicSub 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
EndSub

If necessary, I will post the code for all of the modules in my test project, but it may be longer than desired for this forum.

I need all the help I can get in order to solve this problem.

Thank you,



Tom Sawyer

[orange]

Please expand on " Sadly, they come close, but don't really work"
...... error numbers, error message, wrong results??

[Sawyer]

What doesn't work is that when the menu button created by the code is clicked on, the procedure that the code associated with the menu button using the .OnAction statement does not run. Nothing happens when the menu button is clicked on except the normal dimming when any button is clicked on.

Sorry I wasn't clear enough. I thought I had explained this well enough. Hope this helps.

Tom

[orange]

Yes, you were quite clear. I thought if there was any additional info, it might help other readers put the issue into context. I know there are others on the forum with more experience with menu customizations and, hopefully, one of these will respond.
Good luck.

[Micron]

Your posted code probably isn't exactly as shown as some spaces seem to have been removed.
It looks to me as if you're adding the commandbar to the vbe (visual basic editor) and if you don't have it open or if it's not the active window, you won't see it.

EDIT - I clicked submit when I intended to just activate Access so that was an unintentional post.
I tried with my editor closed and your code worked for me. After reading your post again, I think you're saying the bar gets created, the button it contains does nothing.
However, you posted the code for bar creation, not the code for the button on it. I am 99.595% sure that procedures called by commandbar buttons or menu items can only be public functions, not subs. It isn't clear which you are using. To reiterate, the bar control must call a function. The function can call a sub though.

[Sawyer]

Micron,

You are correct. I pasted the code exactly as Daniel Olsen's thread on StackOverflow displayed. There are six places where a space was omitted in front of the equal sign. When I pasted it into my test project code module, VBE added the spaces, so the construction was correct even if the example code from Daniel had spaces removed. So that didn't cause the problem.

You are also correct that I'm adding a commandbar to the VBE. However, when I run the test, I run it from the VBE using the run command - so I should see the result. (The procedure "RunTests" just displays a MsgBox in order to demonstrate that the procedure was called by the newly constructed menu item. Here is the code:

Public Sub RunTests()
    MsgBox "You have reached Sub RunTests()"
End Sub

Here is the exact code to create the new toolbar:

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 Caption"
    cmdButton.OnAction = "'" & CurrentProject.Name & "'!RunTests()"
Rem ***     cmdButton.OnAction = "RunTests()"
    cmdButton.Enabled = True
    cmdButton.Style = msoButtonIconAndCaption
    cmdBar.Visible = True
End Sub

The intent is for the autoexec macro run the procedure "CreateToolbar()" when the application starts so that the new toolbar is available for use in VBE immediately. For testing purposes, I run the "CreateToolbar" procedure while in VBE using the Run->Run Sub/UserForm menu item or by pressing F5. Doing so lets me see the new toolbar get created, just like it is supposed to do. However, when I click on the menu button that is created on the toolbar, nothing happens. It's like the button isn't attached to the procedure that cmdButton.OnAction = "RunTests()" should do. Just nothing. No error messages, no error numbers. Just nothing other than seeing the menu item button depressing. (I've tried it with and without using CurrentProject.Name to expressly identify the procedure as being in the current project. Same result.)

Thank you for your help.

Tom

[Micron]

the missing spaces I was referring to are at the beginning and end ( PublicSub )
My understanding now is that you see the bar but clicking Run Tests button does not run your message box sub.

Did you miss this > I am 99.595% sure that procedures called by commandbar buttons or menu items can only be public functions, not subs.

I will add that the function called by commandbar controls has to be public in a standard module. Your commandbar code does not.
I would not intentionally use the same name for different objects by the way.

I don't seem to be able to remove the image that I no longer needed.

[Sawyer]

Micron,

I'm embarrassed. I missed seeing the missing spaces on the Public Sub and End Sub statements. Again, however, the spaces are in the code I am actually running so no harm no foul. At least for that.

I changed the subs to functions. Still the same.

"I would not intentionally use the same name for different objects by the way." If you are referring to "Run Tests" and "RunTests()", they aren't the same but they are confusing. That's the way the code was presented by Daniel Olson and I didn't change it. Because of your comment, I changed "Run Tests" to "TestButton" and got the same results.

Your comment "I will add that the function called by commandbar controls has to be public in a standard module. Your commandbar code does not." might be the key. But I don't know what you are referring to. Is it because the procedure was a sub rather than a procedure. (I changed that.) The code is in a standard module (at least I think it is. It's not a Class Module.) The function is a public function. Please explain.

The menu bar can be closed with the X button and it won't show in your VDE. However, until you close access and restart it, you can still see the toolbar when you right click on the standard access toolbar. But, again, when you close and restart access the new toolbar goes away. At least it does for me. There is a way to delete the new toolbar, I just haven't yet written the code to do so. Chip Pearson's code shows how to do it. (He does use a Class Module to trap the OnAction event. But his code also doesn't work, in the same way.)

Thank you for your help. You are going out of your way to help me and I really appreciate it.

Tom

[Micron]

Is it because the procedure was a sub rather than a procedure.

That's what I meant. You posted
Public Sub RunTests()

I don't know what "I changed that means"? You changed it to what you posted, or changed it but didn't post the update?
Your commandbar code does not. My bad. Should have said "your command bar code does not have to be a function, nor does it have to be in a public module. You can create a commandbar from anywhere.

I'll create my own function and try to call it with your commandbar button. Will get back to you.

[Micron]

I'm running out of time until maybe later tonight. FWIW, I have done this on the Access side and things worked. Have never tried to code a commandbar in the vbe itself. Can I ask why (maybe the whole exercise is unnecessary)?

[orange]

Micron,

Have you got some code you could share?
I haven't done much of this an haven't found good tutorial etc.
I used Tom's code, changed to funtion, when I got the Run Tests window it will attach to the vbe header info.
I can't get it to execute Tom's function/sub, nor 1 of my own. I can't rerun the commandbar create stuff a second time -- I get invalid procedure call -- and can't find a way to Delete the commandbar.

I can run this

Function listcommandbars()
    Dim ibar As Integer
    For ibar = 1 To Application.VBE.CommandBars.Count
        Debug.Print ibar; Application.VBE.CommandBars(ibar).Name
    Next ibar

End Function

and can see the Run Tests # 33

 1 Menu Bar
 2 Standard
 3 Edit
 4 Debug
 5 UserForm
 6 Document
 7 Code Window
 8 Toggle
 9 Project Window Insert
 10 Code Window (Break)
 11 Watch Window
 12 Immediate Window
 13 Locals Window
 14 Project Window
 15 Project Window (Break)
 16 Object Browser
 17 MSForms
 18 MSForms Control
 19 MSForms Control Group
 20 MSForms Palette
 21 MSForms Toolbox
 22 MSForms MPC
 23 MSForms DragDrop
 24 Toolbox
 25 Toolbox Group
 26 Property Browser
 27 Property Browser
 28 Docked Window
 29 Clipboard
 30 System
 31 MZ-Tools 8.0 - Main
 32 MZ-Tools 8.0 - Other Utilities
 33 Run Tests

Update:

I can delete the "Run Tests" custom commandbar in VBE with the following

' ----------------------------------------------------------------
' Procedure Name: delcmdbar
' Purpose: To delete a VBE Command bar
' Procedure Kind: Function
' Procedure Access: Public
' Author: Jack
' Date: 28-Dec-19
' ----------------------------------------------------------------
Function delcmdbar()

      Dim delbars As Integer
      Dim bar As CommandBar
10    For Each bar In Application.VBE.CommandBars
20        If (bar.BuiltIn = False) And _
          (bar.Name = "Run Tests") Then
30            bar.Delete
40            delbars = delbars + 1
50            Else
60            Debug.Print bar.Name
70        End If
80    Next bar
90    Debug.Print vbNewLine & "VBE command bars deleted : " & delbars
       
End Function

[Sawyer]

Micron and Orange,

Thank you so much. Micron, hopefully you can join in later this evening.

I too had to leave. Maybe not to work anymore. But, the older I get, the more naps I have to take. So I went to take one. I'm back, amongst the living.

Micron, the reason I'm doing this in the VBE instead of on the Access side. The short answer is that I need to run code while in I'm in the VBE creating code. As for the longer answer - well hang onto your hat. It will take a little while.

In either Excel or in Access, whenever the system throws an error code, I want the error message to be meaningful and to include where in the underlying code the error occurred. This helps the user give the developer information that helps the developer fix the problem.

For years, in order to achieve the above, I have always included the following code in every procedure I write; Class or Standard modules, Subroutines or Functions:

--------  Code is in a module, lets call it basExample -------

-------- NOTE: The variables mstrModuleName and gstrApplicationName are defined and /or set in the startup 
--------           procedures, not listed here.



Rem *****************************************************************************************
Rem ****
Rem **** Sub Sample()
Rem ****
Rem *****************************************************************************************


Sub Sample()


On Error GoTo Error_Handler


Dim strProcedureName As String
strProcedureName = "Sub Sample()"


Rem ***
Rem *** 12/15/19 -    I found a way to use code to identify the name of the Module without
Rem ***             hard coding it every time in the code Module itself
Rem ***    
Rem ***                The following is the old statement            
Rem *** mstrModuleName = "basModuleName"


Rem ***             The following is the replacement statement.
mstrModuleName = Application.VBE.ActiveCodePane.CodeModule
Rem ***


'<-- The Real Work Begins Here
    
    VBA Statements that do what the procedure needs to do
    
'<-- The Real Work Ends Here




Exit_Procedure:
    On Error Resume Next
    Exit Sub


Error_Handler:
    Select Case Err
'        Case xx -- for when a specific error number is just a way to define that the procedure takes some action
'        Case yy -- for when a specific error number is just a way to define that the procedure takes some action
        Case Else
            MsgBox Title:=gstrApplicationName, _
                    Buttons:=vbCritical, _
                    Prompt:="An error has occurred in this application.  " & vbNewLine & _
                            "Please contact your technical support person and tell them this information:" & vbNewLine & _
                            vbNewLine & _
                            "Module Name:  " & mstrModuleName & vbNewLine & _
                            "Procedure:  " & strProcedureName & vbNewLine & _
                            vbNewLine & _
                            "Error Number " & Err.Number & ", " & Err.Description & vbNewLine & _
                            vbNewLine & _
                            "         "
                            "         "
            Resume Exit_Procedure
            Resume
    End Select
    
End Sub


Rem *****************************************************************************************
Rem ****
Rem **** END of Sub Sample()
Rem ****
Rem *****************************************************************************************

(Please forgive me any typos I've made in the above code and also recognize that this code is just a snippet. The code actually works, in thousands of procedures.)

Naturally, I use a template to copy this code into a procedure. However, I still had to hard code in the name of he Module and the name of the procedure.

Not only is this tedious it interjects in the opportunity to make errors.

So, I wanted a way to set these at run time.

I found how to get the name of the Module, as noted in the code sample above.

There is no VBA supplied way to get the name of the procedure at run time.

However, Chip Pearson developed a procedure that uses the VBA Extensibililty library to insert code into each procedure that sets a variable with the name of the procedure in it. This works great, as long as you don't get to fancy with the code you want injected.

This procedure has to be run manually against each individual Module while it is open, but it sure beats manually hard coding the information into each procedure.

So, I wanted a menu button in VBE that I could use to run this procedure instead of using one of the various ways to run a procedure manually. Hence my journey into adding a menu item into the VBE toolbar.

I hope this explains my reasons well enough.

So, back to the problem.

I think that there is something in the VBE environment that is preventing the menu button from running the specified procedure as specified with .OnAction even though, as Orange demonstrated, VBE recognizes the new menu item. Maybe it's the way the .OnAction statement is constructed or maybe it's security - as in trusting macros. I just don't know.. That's why I'm reaching out to you guys, the experts. (You know of course what the definition of an expert is - its a has been spurt under pressure.)

BTW: Orange thank you for the code that deletes the user defined menu item.

Again, thanks to you guys for helping out a old programmer like me.

Tom

[orange]

Tom,

I haven't dealt much with Custom menus. What I have learned from your posts

-you must use Application.VBE.CommandBars to work with VBE objects;
-if you simply use CommandBars you are dealing within Access

- I found a link with several examples (for plain jane CommandBars) This may be adaptable for use with VBE??

I have not found tutorials/links specific to VBE command bars nor any details on OnAction method.

Good luck.

[Micron]

It's almost 11 PM here and I will have to study all of the new comments tomorrow. I can say, though, that a commandbar can be deleted thus
Application.VBE.CommandBars("run tests").Delete
If you try to delete and it's not in the collection, you'll get an error for the opposite reason. I would use a combination similar to

On Error Resume Next

Application.VBE.CommandBars("run tests").Delete

On Error GoTo 0
    Dim cmdBar As CommandBar
    Dim cmdButton As CommandBarButton
    Set cmdBar = Application.VBE.CommandBars.Add("Run Tests")
    Set cmdButton = cmdBar.Controls.Add(msoControlButton)
etc etc

[Micron]

Couldn't let it go seeing as how I've done this before (but not in the vbe). If the Set line is written to just add the commandbar to the application, it not only can be found (these things appear in the Add-Ins Ribbon tab) the called function runs. Thus I will go out on a limb and say what you want to do doesn't apply to the editor as it isn't meant to interact with the user in a GUI fashion. .

I still don't get your comments as to why you want to do this. Almost seems that it pertains to knowing what procedure (or at least, module) was running when an error occurs, but for who? Users shouldn't be in the vbe, so I can't see how your comments apply to users. A developer knows where the error occurs if in the vbe because the procedure breaks there, thus it's obvious. Thus I don't understand the goal. If you want to log/report the procedure name involved in an error, you don't need a commandbar button for that.

@Orange - if you're still looking for something from me, let me know. It seems as though you might have found what you're after, but I'm not sure.