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

"STEVE BELL" wrote in message
news:lQfze.20823$H64.14853@trnddc07...
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
==========================================
--
steveB

Remove "AYN" from email to respond

"Frans van Zelm" wrote in message
...
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