add an item to a vbe toolbar

[lior03]

hello
i am trying to add an item to the vbe edit toolbar.
[VBA]
Sub cBox()
With Application.VBE.CommandBars("edit").Controls.Add(before:=2)
.Caption = "goto last module"
.OnAction = "entervba"
.BeginGroup = True
End With
End Sub

[/VBA]
what am i doing wrong?

[Bob Phillips]

What is the problem as far as you see it? Does the called macro not work?

[lior03]

hello
i do not get an error message but the onaction method do not seem to work

[Bob Phillips]

That is what I thought.

This is tricky so watch closely.

First, the bad news is that the OnAction property is ignored in the VBE. The good news is that we have a specific commandbars event handler that we can use.

To utilise this functionlity, you will need to set a reference to the "Microsoft Visual Basic For Applications Extensibility" library in Tools>references.

Start by creating a class module, call it CBarEvents, and add this code.

[vba]

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
[/vba]
This will take the button click, and route it through whatever OnAction macro you declared.

When you create your menu item, you need to attach it to the class, creating a event c ollection item to manage it. In your standard module use this adapted version of your code

[vba]

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("Extra").Delete
On Error GoTo 0

Set myBar = Application.VBE.CommandBars.Add("Extra", , False, True)
myBar.Visible = True
Set myControl = myBar.Controls.Add(msoControlButton, , , 1)
With myControl
.Caption = "myVBEButton"
'Other properties set
.FaceId = 29
.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
[/vba]
When you click the toolbar item now, it will run the LittleClick macro.

[GTO]

...First, the bad news is that the OnAction property is ignored in the VBE. The good news is that we have a specific commandbars event handler that we can use....

Just a quick thank-you Bob. I was going daffy (well... I may still be daffy, but that's not at point) trying to beat OnAction into submission.
This was a nice find!

Mark

[Bob Phillips]

Glad it was helpful Mark.

I have noticed that attachments get deleted after a while, so posting the code inline seems to be the way to go, it makes the archives more useful, as it did for you here.