Input Parameters

Calling the Same Macro

You might want to include a button that can be used as a toggle.
For example the following button can be used to toggle between sorting ascending and descending.

objbutton = objcommandbar.controls.Add (Type:=msoControlType.msoControlButton, _ 
objbutton.OnAction = "SortList"
objbutton.Caption = "Sort Ascending"

objbutton = objcommandbar.controls.Add (Type:=msoControlType.msoControlButton, _
objbutton.OnAction = "SortList"
objbutton.Caption = "Sort Descending"

Public Sub SortList 
Dim iascdsc As Integer
   Select Case CommandBars.ActionControl.Parameter
      Case "Asc" : iascdsc = xlAscending
      Case "Dsc" : iascdsc = xlDescending
   End Select
   Range("A1:B20").Sort Key1:=Range("A1"), Order:=iascdsc, Header:=xlNo
End Sub

Passing Parameter Values

For simple VBA macros you can use the Parameter and Tag properties to store information to be passed to the OnAction macro.
If you have more than two items of information to pass it is more convenient to use a macro procedure that has input parameters.
For example if you wanted to pass a product name, cost and selling price to the following macro.

Public Sub ShowProductName(ByVal sProductName As String, _ 
                           ByVal dCost As Double, _
                           ByVal dPrice As Double)

End Sub

To execute this macro from a commandbar you need to assign the following code to the OnAction property of the control.

'ShowProduct "Product", 10, 20'

The entire expression is enclosed in single quotes.
Any string parameter values within the expression are enclosed in double quotes

objbutton.OnAction = "'ShowProduct ""Product"", 10, 20'" 

© 2021 Better Solutions Limited. All Rights Reserved. © 2021 Better Solutions Limited TopPrevNext