VBA code for a toolbar button

[Thrava]

Hi group.

What I like is that whenever this one particular workbook
is launched (opened) that there would be a command button
(icon) appear somewhere on the toolbar with a macro (or
VBA code) associated with it.

is this even possible ?

Thanks
Thrava

 

[Bob Phillips]

Thrava,

Of course it can, here is an example. Call this from workbook_Open event

Public Sub AddButton()
Dim oMenus As Range

Set oMenus = Range("menu_names").Cells(1, 1)


On Error Resume Next
Application.CommandBars("Standard").Controls("SUMPRODUCT Wizard").Delete
On Error GoTo 0

If SP_Wizard = 0 Or SP_Wizard = 1 Then
With
Application.CommandBars("Standard").Controls.Add(temporary:=True)
.BeginGroup = True
.Caption = "SUMPRODUCT Wizard"
If SP_Wizard = 0 Then
.TooltipText = oMenus.Offset(11, i_Language)
.OnAction = "ShowClassicWizard"
Else
.TooltipText = oMenus.Offset(12, i_Language)
.OnAction = "ShowadvancedWizard"
End If
wsData.Shapes("wizard").CopyPicture
.PasteFace
End With
End If

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

Slight mod

Public Sub AddButton()
Dim oMenus As Range

Set oMenus = Range("menu_names").Cells(1, 1)


On Error Resume Next
Application.CommandBars("Standard").Controls("SUMPRODUCT Wizard").Delete
On Error GoTo 0

If SP_Wizard = 0 Or SP_Wizard = 1 Then
With Application.CommandBars("Standard") _
.Controls.Add(temporary:=True)
.BeginGroup = True
.Caption = "Example Button"
.FaceId = 29
.OnAction = "myMacro"
End With
End If

End Sub



--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Thrava]

Thank you sir,
I haven't tried it yet, but I'll try it tonight.
I'm amazed everytime at what can be done with excel.


Thanks again