Custom buton for macro in an Addin file.

[Karthik Bhat - Bangalore]

Hi All

I have created a Addin file (Helper.xla)with a macro called SumData in
it. I want to have this macro linked to a button on the menu bar. I
need to send this addin to various users and they should be able to use
the macro by pressing this custom button once they have added the
helper.xla.

Can you give me some code that I can add to the addin file for serving
the above purpose.

Thanks
Karthik Bhat
Bangalore

 

[Bob Phillips]

Hi Karthik,

Here is a simple example that adds it to the Tools menu

Option Explicit

Private Sub Workbook_Open()
Dim oCb As CommandBar
Dim oCtl As CommandBarPopup
Dim oCtlBtn As CommandBarButton

Set oCb = Application.CommandBars("Worksheet Menu Bar")
With oCb
Set oCtl = .Controls("Tools").Controls.Add( _
Type:=msoControlPopup, _
temporary:=True)
oCtl.Caption = "myButton"
With oCtl
Set oCtlBtn = .Controls.Add( _
Type:=msoControlButton, _
temporary:=True)
oCtlBtn.Caption = "myMacroButton"
oCtlBtn.FaceId = 161
oCtlBtn.OnAction = "myMacro"
End With
'etc.
End With
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim oCb As CommandBar

Set oCb = Application.CommandBars("Worksheet Menu Bar")
oCb.Controls("Tools").Controls("myButton").Delete
End Sub


'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code



--

HTH

RP
(remove nothere from the email address if mailing direct)