OnAction-property of cbbutton in VBE editor

[Frans van Zelm]

Hi,

((VB-editor of Excel 2K, Dutch version; Windows 2K Server))

I hoped to make a custom button work by:

Sub BrandNewBarAndButton()
Dim myBar As CommandBar
Dim myControl
Set myBar = Application.VBE.CommandBars.Add("Extra", , False, True)
myBar.Visible = True
Set myControl = myBar.Controls.Add(msoControlButton, , , 1)
With myControl
'Other properties set
.OnAction = "LittleClick"
End With
End Sub

The precedure LittleClick in the same module. But ... clicking the
button has no result.

I also noticed the Click-event but I expect that then a class module ;-(
etc. is needed.

Thanks for a reply, Frans

 

[STEVE BELL]

Here's some code that I have in a standard module.
(my code was originally found on this ng)

All of the .OnAction macros are in standard modules.

This code is initiated from the ThisWorkbook module

==================================
Sub addToolbar()
Dim oCBMenuBar As CommandBar
Dim oCBCLeave As CommandBarControl
Dim iMenu As Integer
Dim i As Integer

' delete existing toolbar
' to prevent multiple toolbars from accumulating
On Error Resume Next
Application.CommandBars("GROM").Delete

' create toolbar
Set oCBMenuBar = Application.CommandBars.Add(Name:="GROM")

With oCBMenuBar
If ThisWorkbook.Name Like "*xlt" Or ThisWorkbook.Path = "" Then
' add buttom to initialze workbook (add workweek pages)
With .Controls.Add(Type:=msoControlButton)
.BeginGroup = True
.Caption = "Initialize Workbook"
.Style = msoButtonCaption
.OnAction = "WBinitialize"
End With
End If

' add space (add workweek pages)
With .Controls.Add(Type:=msoControlButton)
.Caption = " "
.Style = msoButtonCaption
.Enabled = False
End With

' add additional work weeks to workbook
With .Controls.Add(Type:=msoControlButton)
.Caption = "Add (additional) Work Weeks"
.Style = msoButtonCaption
.OnAction = "WWsAdd"
End With

' add space (add workweek pages)
With .Controls.Add(Type:=msoControlButton)
.Caption = " "
.Style = msoButtonCaption
.Enabled = False
End With

' transfer QEX data
With .Controls.Add(Type:=msoControlButton)
.Caption = "Xfer QEX Data"
.Style = msoButtonCaption
.OnAction = "xferData"
End With

' add space (add workweek pages)
With .Controls.Add(Type:=msoControlButton)
.Caption = " "
.Style = msoButtonCaption
.Enabled = False
End With

' Data Summary
With .Controls.Add(Type:=msoControlButton)
.Caption = "Data Summary"
.Style = msoButtonCaption
.OnAction = "DataSummary"
End With

' add space (add workweek pages)
With .Controls.Add(Type:=msoControlButton)
.Caption = " "
.Style = msoButtonCaption
.Enabled = False
End With

' Save workbook
With .Controls.Add(Type:=msoControlButton)
.Caption = "Save Workbook"
.Style = msoButtonCaption
.OnAction = "ShowSaveAsDialog"
End With

' add space (add workweek pages)
With .Controls.Add(Type:=msoControlButton)
.Caption = " "
.Style = msoButtonCaption
.Enabled = False
End With

' Print Report
With .Controls.Add(Type:=msoControlButton)
.Caption = "Print Active Sheet"
.Style = msoButtonCaption
.OnAction = "PrintGROM"
End With

.Position = msoBarTop
.Protection = msoBarNoMove
.Visible = True
End With
End Sub
============================================
Sub deleteToolbar()
On Error Resume Next
Application.CommandBars("GROM").Delete

End Sub
=================================================
Private Sub Workbook_Activate()
Application.EnableEvents = False
Toolbars.addToolbar
Application.EnableEvents = True
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.EnableEvents = False
Toolbars.deleteToolbar
Application.EnableEvents = True
End Sub

Private Sub Workbook_Open()
Application.EnableEvents = False
Toolbars.addToolbar
Application.EnableEvents = True
End Sub
==========================================

 

[Bob Phillips]

Are you trying to add to worksheet commandbars, or the VBA commandbars?

 

[Frans van Zelm]

Dear Bob,

I try to create a working VBA commandbar.

Frans

 

[Frans van Zelm]

Dear Steve,

This works fine on Excel toolbars. However, on a VBE-toolbar with new
buttons it fails.

Thanks for your nice sample anyway.

Frans

 

[Norman Jones]

Hi Frans,

See Chip Pearson at:

http://www.cpearson.com/excel/vbemenus.htm

 

[Bob Phillips]

Frans,

This is tricky so watch closely.

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

First create a class module, call it CBarEvents, and add this code

Option Explicit

Public WithEvents oCBControlEvents As CommandBarEvents

Private Sub oCBControlEvents_Click(ByVal cbCommandBarControl As Object, _
bHandled As Boolean, _
bCancelDefault As Boolean)

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

bHandled = True
bCancelDefault = True

End Sub

This will take the button click, and route it through whatever OnAction
macro you declared.

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

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

When you click the toolbar item now, it will run the LittleClick macro.

 

[Frans van Zelm]

Hi Bob,

As I feared: classes. But I am glad that you acknowlegde that OnAction
doesn't work in the VBE.

I'll try to apply your code.

Frans

 

[Frans van Zelm]

Hi Norman,

Thanks for your reply and the link. I remember to have been on Pearson's
site before. It really is fine.

Frans