VB and VBA Users Source Code: Adding a command bar button that calls a parameterised function
[
Home
|
Contents
|
Search
|
Reply
| Previous | Next ]
VB/VBA Source Code
Adding a command bar button that calls a parameterised function
By:
Andrew Baker
Email (spam proof):
Email the originator of this post
Date:
Friday, July 09, 2004
Hits:
2943
Category:
Unspecified
Article:
The OnAction property of an Excel CommandbarButton actually allows you to specify a lot more than just the macro to call. Below is a function which creates a CommandbarButton that can call a parametised function in a specified workbook. Option Explicit 'Purpose : Adds and returns a command bar button (that can call a parameterised macro) to an existing commandbar. 'Inputs : oParentCommandBar The command bar to add the button to. ' oMacroWrk The workbook containing the macro to call from the button click. ' sMacroName The name of the macro to call. ' sToolTip The tooltip of the button. ' lFaceID The faceID of the button icon. ' [args] A list of the arguments to pass to the sMacroName function 'Outputs : N/A 'Author : Andrew Baker 'Date : 25/03/2000 'Notes : 'Example : ' 'Setup command bar ' Set oCmdBar = CommandBars.Add ' 'Add show this file history ' Call CommandBarAdd(oCmdBar, ThisWorkbook, "TestClick", "Show A File", 2520, "The name of the file", "Another Parameter") ' 'Header of macro called looks like this ' Sub TestClick(Optional sParam1 As String, Optional sParam2 As String) ' ... ' ... Function CommandBarAdd(oParentCommandBar As CommandBar, oMacroWrk As Excel.Workbook, sMacroName As String, sToolTip As String, lFaceID As Long, ParamArray args() As Variant) As CommandBarButton Dim oBtn As CommandBarButton Dim sOnAction As String Dim vArg As Variant Dim sSeperator As String Set oBtn = oParentCommandBar.Controls.Add oBtn.FaceId = lFaceID oBtn.TooltipText = sToolTip sOnAction = "'" & oMacroWrk.Name & "'!'" & sMacroName If IsArray(args) Then For Each vArg In args sOnAction = sOnAction & sSeperator & """" & CStr(vArg) & """" sSeperator = "," Next End If sOnAction = sOnAction & "'" oBtn.OnAction = sOnAction End Function 'Demo code Sub Test() Dim oCmdBar As CommandBar 'Setup command bar Set oCmdBar = CommandBars.Add 'Add show this file history CommandBarAdd oCmdBar, ThisWorkbook, "TestClick", "Show A File", 2520, "Value1", "Value2" 'Add a name here 'oCmdBar.Name = "Test Bar 1" oCmdBar.Visible = True End Sub 'Routine called by click Sub TestClick(Optional sParam1 As String, Optional sParam2 As String) MsgBox "Param1: " & sParam2 & ". Param2: " & sParam1 End Sub
Terms and Conditions
Support this site
Download a trial version of the Excel Workbook Rebuilder
