Invoking a Visual Basic macro from a Custom Toolbar Button

  • Thread starter Thread starter bwprice
  • Start date Start date
B

bwprice

I've created an important Visual Basic program capable of processing
and summarizing many CSV data files (depending heavily on the Header
Row information). Although this works well on one Excel Workbook, I
wish it to be available for all Workbooks I open (or new workbooks I
create). Therefore I would like to create a Custom Toolbar Button and
assign it this VB macro.

This is similar to Acrobat Writer's ability to invoke a macro (called
ConvertToPDFA).

Please help,

Brian
 
hi,
you need to put the macro in a personal.xls workbook and place it in your
XLStart folder. each time excel is open, it will open the personal.xls file
hidden but all macros in the file would be available. I think different
versions of excel have the xlstart folder in different places. I am using xp
and the location is at...
C:\program files\microsoft office\office10\xlstart.

Regards
FSt1
 
Here is some code to add the button to the Formatting toolbar. Put this in
Personal.xls as well

Option Explicit

Private Const BUTTON_NAME As String = "MyFormat"
Private Const BUTTON_CAPTION As String = "My Formatter"
Private Const BUTTON_MACRO As String = "MyFormatMacro"


Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.CommandBars("Formatting").Controls(BUTTON_NAME).Delete
On Error GoTo 0
End Sub

Private Sub Workbook_Open()
On Error Resume Next
Application.CommandBars("Formatting").Controls(BUTTON_NAME).Delete
On Error GoTo 0

With Application.CommandBars("Formatting")
With .Controls.Add(Type:=msoControlButton, temporary:=True)
.BeginGroup = True
.Caption = BUTTON_CAPTION
.OnAction = BUTTON_MACRO
.FaceId = 108
.TooltipText = "Spercial Formatter"
End With
End With
End Sub


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Back
Top