Access VBA CommandBarButton.OnAction doesn't work

[Sawyer]

Micron and Orange,



Orange, your comment "you must use Application.VBE.CommandBars to work with VBE objects;" got me to thinking even though my (our) code used Application.VBE.CommandBars. Any way, I changed my search term on Google to ",OnAction VBE". This search led me to the link http://www.vbaexpress.com/forum/arch...p/t-11748.html.

One comment the responder, xld, made was "First, the bad news is that the OnAction property is ignored in the VBE. (Ephasis added by me.) The good news is that we have a specific commandbars event handler that we can use.", which pretty much explains everything about why we were having such a problem making our code work. It also explained the whole thing to me in a way that I can understand.

So, now it works. Here is the code:

--------  Standard Code Module Named "Create VBE Menu Items"  --------


Option Compare Database
Option Explicit


Dim mcolBarEvents As New Collection 'collection to store menu item click event handlers


Sub BrandNewBarAndButton()
Dim CBE As CBarEvents
Dim myBar As CommandBar
Dim myControl


On Error Resume Next
Application.VBE.CommandBars("NewToolBar").Delete
On Error GoTo 0


Set myBar = Application.VBE.CommandBars.Add("NewToolBar", , False, True)
myBar.Visible = True
Set myControl = myBar.Controls.Add(msoControlButton, , , 1)
With myControl
.Caption = "myVBEButton"
.DescriptionText = "Run the LittlClick Subroutine"
.Style = msoButtonIconAndCaption
.FaceId = 558
.OnAction = "LittleClick"
End With


'Create a new instance of our button event-handling class
Set CBE = New CBarEvents


'Tell the class to hook into the events for this button
Set CBE.oCBControlEvents = Application.VBE.Events.CommandBarEvents(myControl)


'And add the event handler to our collection of handlers
mcolBarEvents.Add CBE


End Sub


Sub LittleClick()
    MsgBox "You have reached LittleClick()"
End Sub


--------  End of Standard Code Module "Create VBE Menu Items"  --------


--------


--------  Class Module Named "CBarEvents"  --------


Option Compare Database


Option Explicit


Public WithEvents oCBControlEvents As CommandBarEvents


Private Sub oCBControlEvents_Click(ByVal cbCommandBarControl As Object, _
Handled As Boolean, _
CancelDefault As Boolean)


On Error Resume Next
'Run the routine given by the commandbar control's OnAction property
Application.Run cbCommandBarControl.OnAction


Handled = True
CancelDefault = True


End Sub


-------  End of Class Module "CbarEvents"  --------

This code works and has the advantage that it deletes the newly create "NewToolBar" before it creates it. This eliminates the error message we were getting saying "Invalid procedure call or argument".

This approach is the same that Chip Pearson was taking, but Chip's approach was centered around Excel and had considerable differences.

As to your question as to why I want to do this:

First, my on error routine listing the name of the procedure and module displays the information on the Access side, not on the VBE side. It's very useful when testing. And if, heaven forbid, a error occurs while a user is using the project in production, the error message displays information the user can jot down to give to the developer.

Second, the command button/bar in the VBE is not to log/report the procedure name involved in an error. It's for the developer to automatically inject some code into procedure(s) in a batch run fashion. The user is never intended to see or use this button, only the developer. In my example, the inject code is to facilitate a standard error handling process. It could as easily be used to inject code into a procedure(s) for some other purpose.

In any case, it was good to work with you guys. Together we solved the problem. I've been working this problem for over a week, and together we solved it in one day.

Thanks again,

Tom

[orange]

Tom,

I have it working as well. I have tried setting the temporary parameter TRUE/FALSE, but it seems when the database is saved, closed, and reopened, the new button is present but doesn't invoke the OnAction.
Good luck with your project.


Other info re error handling:

MZTools commercial product

vbWatchDog from Wayne Phillips

Paul Kelly Youtube videos (Excel oriented but vba)
1
2