PDA

View Full Version : [SOLVED] Assigning a Macro to a VBE Toolbar Button



Cyberdude
05-08-2005, 12:20 PM
I have added a number of buttons to my VBE toolbar, but they are all taken from the customization list. Now I would like to add a button and assign a macro to it, but there doesn't seem to be an option that will do the assignment like there is for the Excel toolbar. Am I missing soomething, or is it impossible to do? :dunno

Bob Phillips
05-08-2005, 01:05 PM
Hey Dude,

Impossible, what does that mean? The VBE is a bit different as you don't assign the macro to the OnAction property (well you can, but it is pointless), instead you have to use CommandbarEvents, and its Click method, and you have to tie the button to that event's object method.

Hopefully, this will get you started,


Private WithEvents vbeCtl As CommandBarEvents

Private Sub vbeCtl_Click(ByVal CommandBarControl As Object, handled As Boolean, CancelDefault As Boolean)
MsgBox "hello"
End Sub

Private Sub Workbook_Open()
Dim oCtl

On Error Resume Next
Application.VBE.CommandBars("Debug").Controls("test").Delete
On Error GoTo 0
Set oCtl = Application.VBE.CommandBars("Debug").Controls.Add( _
Type:=msoControlButton, _
temporary:=True)
With oCtl
.FaceId = 29
.Caption = "test"
End With
Set oCtl = Application.VBE.CommandBars("Debug").Controls("test")
Set vbeCtl = Application.VBE.Events.CommandBarEvents(oCtl)
End Sub

As you can see, I drop this in the Thisworkbook code module

Cyberdude
05-08-2005, 02:26 PM
Much thanx, xld. Looks like it's NOT impossible after all. It'll take me awhile to study this and come up with the grand plan I want, but you've given me a great starting point, and I appreciate it! :friends:

Emily
05-08-2005, 09:07 PM
Here attached an example written by a Chinese Excel MVP Chijanzen.
Hope it is what you look for.

Cyberdude
05-09-2005, 10:13 AM
Very nice coding by your Chinese friend, Emily. Easy to understand, and very helpful. Now all I have to do is adapt it to my needs. Thanks again.