Creating a Toolbar to add-in

M

Mike Finister

Hi there

I'm doing some VBA work on a spreadsheet and have got the code to read
through, validate and reformat. Now I want to do the following:

a) Write out the data as a text file (can use open etc. so no issues)
b) Have a new toolbar for the macro to run from (OK pretty easy I suppose)

However, I've seen something at my work where someone wrote something with a
..xla extension and double clicking on this meant that it auto-added the
toolbar into Excel and the object seemed to include macro stuff.

Is this done with VB6 and the Office SDK?

I'm also a VB programmer, but never turned my hand to do too much VBA
programming...only the simple stuff.

Any pointers would be great!

Thanks

Mike
 
B

Bob Phillips

It doesn't need VB6 or the SDK to add a toolbar from code.

Here is some sample code to create a toolbar button on the Formatting as
suggested. This code would
go in the ThisWorkbok code module of the addin.

I would also add my usual corollary that to see what FaceIds are available,
visit John Walkenbach's site at http://j-walk.com/ss/excel/tips/tip67.htm

Option Explicit

Dim sMenu As String

Private Sub Workbook_BeforeClose(Cancel As Boolean)

sMenu = "myButton"

On Error Resume Next
Application.CommandBars("Formatting").Controls(sMenu).Delete
On Error GoTo 0
End Sub

Private Sub Workbook_Open()
Dim oCB As CommandBar
Dim oCtl As CommandBarControl
Dim newMenu As Object 'CommandBarControl
Dim ctrlButton As Object 'CommandBarControl

sMenu = "Margin Calculator"

On Error Resume Next
Application.CommandBars("Formatting").Controls(sMenu).Delete
On Error GoTo 0

Set oCB = Application.CommandBars("Formatting")
Set oCtl = oCB.Controls.Add(Type:=msoControlButton, temporary:=True)

With oCtl
.BeginGroup = True
.Caption = sMenu
.FaceId = 197
.Style = msoButtonIconAndCaption
.OnAction = "myMacro"
End With

End Sub


--

HTH

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