VBA Excel Toolbar Editing

Energizer

Hello All,

I'm a novice at excel macro's and vba scripting and i'm trying to setup an excel addin for my work.

The main function of the addin works but i got most of the coding off and site and do not know how to edit it to do what i want.

Here is the code:

Option Explicit

Public Const ToolBarName As String = "Parentheses Brackets"
'===========================================
Sub Auto_Open()
Call CreateMenubar
End Sub

'===========================================
Sub Auto_Close()
Call RemoveMenubar
End Sub

'===========================================
Sub RemoveMenubar()
On Error Resume Next
Application.CommandBars(ToolBarName).Delete
On Error GoTo 0
End Sub

'===========================================
Sub CreateMenubar()

Dim iCtr As Long

Dim MacNames As Variant
Dim CapNamess As Variant
Dim TipText As Variant

Call RemoveMenubar

MacNames = Array("Brackets", _
"Dashbrackets")

CapNamess = Array("Brackets", _
"Dashbrackets")

TipText = Array("Brackets tip", _
"Dashbrackets tip")

With Application.CommandBars.Add
.Name = ToolBarName
.Left = 200
.Top = 200
.Protection = msoBarNoProtection
.Visible = True
.Position = msoBarFloating

For iCtr = LBound(MacNames) To UBound(MacNames)
With .Controls.Add(Type:=msoControlButton)
.OnAction = "'" & ThisWorkbook.Name & "'!" & MacNames(iCtr)
.Caption = CapNamess(iCtr)
.Style = msoButtonIconAndCaption
.FaceId = 71 + iCtr
.TooltipText = TipText(iCtr)
End With
Next iCtr
End With
End Sub

'===========================================
Sub Brackets()

' Format negative numbers so that they are shown in parentheses

Selection.NumberFormat = "#,##0_); (#,##0)"

End Sub

'===========================================
Sub Dashbrackets()

' Format negative numbers so that they are shown in parentheses wiv zeros as negatives

Selection.NumberFormat = "_(* #,##0_);_(* (#,##0);_(* -_);_(@_)"

End Sub


I need it too add into the menubar instead of floating in excel when i open it.

I changed "msoBarFloating" to "msoBarMenuBar" but it just threw up errors.

Also i want to put icons instead of text into the boxes for users to click on. I have the icons but do not know how to add them into the code.

I have looking on a bunch of sites for this information but cannot find it.

Energizer

anyone got any ideas? would be appreciated.

Bishy

Not sure if this is any help, probably not but try

.Position = msoBarLeft

"msoBarMenuBar" is only used with Macs.

Recently using VSTO in Excel 2007 in which its a lot easier to add ribbon menu bars etc. but thats probably not relevant, as f*** all people are using 2007 at this stage