Excel Macro with parameter

[Guest]

how do I specify an excel macro that takes a parameter? I need to attach it to a custom menu option.

 

[moshea70]

I am building a command bar to aid in navigation through a workbook. Navigating between certain sheets is one thing that I want the user to be able to do. I think the combo box routing will work, but I am frustrated/fixated on this passing a parameter problem. It seems like it should be a very simple and common thing to do. The only (minor) thing I don't like about the Combo box solution you propose is the need to hit "Go". Granted, it's not that big of a deal, but it would be a bit nicer if I could get what I'm doing to work.

Here is another site that gives a more straightforward example of the passing a parameter issue. I can't get it to work either.

http://www.dicks-blog.com/excel/2004/06/passing_argumen.html

The code from that site:

Sub SetButton()

Dim sArg As String

sArg = "This is a test"

Sheet1.Shapes(1).OnAction = "'" & "ShowAnArgument """ & sArg & """'"

End Sub

Sub ShowAnArgument(sArg As String)

MsgBox sArg

End Sub

 

[moshea70]

This method seems to have solved my problem:

http://www.microsoft.com/worddev/articles/AOV98B11.htm

It uses the ActionControl property to pass a parameter. Seems like a round-about way of doing it, but it works...

From the site:

This creates the command bar:

Private Sub Document_Open()

CustText(0) = "click Enter "
CustText(1) = "click OK "
CustText(2) = "click Next "
CustText(3) = "To do so, "
CustText(4) = "In this article, "
CustText(5) = "First, select "

Dim cmbControl As CommandBarControl
Dim cmbSubControl As CommandBarControl
Dim X As Integer

Application.CommandBars("text").Reset
Set cmbControl = Application.CommandBars("Text") _
.Controls.Add(Type:=msoControlPopup,Temporary:=True)
cmbControl.Caption = "Custom Text"
For X = 0 To Ubound(CustText)
Set cmbSubControl = cmbControl.Controls.Add _
(Type:=msoControlButton, Parameter:=X, Temporary:=True)
With cmbSubControl
.Caption = CustText(X)
.OnAction = "InsertText"
End With
Next X
End Sub


This function utilized the parameter that is passed when the user uses the command bar:

Function InsertText()
Dim TextParam As Integer
TextParam = CommandBars.ActionControl.Parameter
Selection = CustText(TextParam)
Selection.Collapse (wdCollapseEnd)
End Function