Excel Macro with parameter

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

[Damon Ostrander]

You have several options:

1. if you need to assign a different value to the argument each time you call it (from the menu) you will need to write a short interface macro that prompts you for the desired input, then calls the other macro passing it the value you have entered.

2. if you always call it from the menu with the same value assigned to its input argument, you can make it an optional argument (using the Optional keyword), and even assign it a default value right in the argument list. Example:

Sub ZipState( Optional ZipCode As String = "80120" ) As String

so that if you call ZipState without providing a value for ZipCode, it will automatically enter a value of "80120".

3. If the macro is in a DLL or add-in, you will have to go with option 1 since you can't modify the code to make the argument optional.

thanks Damon

yes I need to keep passing it different values..I am using the onAction command within my VBA code..the things is I cannot have it prompt me everytime I click the menu option, its supposed to know what to do and with what value

thanks

thanks Damon

yes I need to keep passing it different values..I am using the onAction command within my VBA code..the things is I cannot have it prompt me everytime I click the menu option, its supposed to know what to do and with what value

thanks

[Damon Ostrander]

Hi again,

Unfortunately the Telepathy option that Microsoft has been working on for so long is still not available in Excel, so there is no way for the routine to read you mind to find out what value you intend for it to use each time you click on the menu item. Would you like it to obtain a value from a worksheet cell, a file, or some other place (perhaps a random number generator?

If you want it to read the value from a cell, simply set the value of the parameter inside the routine. You can use the IsMissing function to determine whether the argument was provided or not, and if not to get it from the worksheet. For example, inside your procedure:

If IsMissing(ZipCode) Then
ZipCode = Worksheets("Codes").[B4]
End If

which gets the zip code from cell B4 on worksheet "Codes" if it is called without any zipcode being provided as an argument.

[moshea70]

Dredging up an old post here...

I have the same question as in the original post, which was never quite answered.

I have the following code where I am adding items to a toolbar submenu:

Set mi = m.Controls.Add(msoControlButton, , , , True)
With mi
.Caption = "Activity 1: " & Range("Activity1Desc")
.OnAction = "NavigateToActivity"
.FaceId = 734
.Style = msoButtonIconAndCaption
' .Style = msoButtonCaption ' caption only, no icon, .FaceId not necessary
End With


The problem is that I want to send a parameter to the NavigateToActivity macro. The parameter would be an integer like 1,2,3, etc. But when I change the OnAction line to read something like .OnAction = "NavigateToActivity(1)" I get an error that the macro is not found. When I take out the (1) I get an error that the parameter is required. What is the syntax for passing a paramter in this case???

Thanks!

[moshea70]

FYI - I've done some research and haven't found any way to do this without using a workaround like calling a macro that then gets the parameter from somewhere else, like from a cell on the worksheet, or setting up a bunch of macros (in my case, one for each activity) rather than one macro with a parameter to specify the activity.

Bummer.

[Norie]

Why not add a combobox to the toolbar as well?

It could contain all possible values for parameter.

The user chooses which one they want from the combobox and then clicks an Activity button which runs the NavigateToActivity macro.

Change the code of NavigateToActivity to look at the value of the combobox.

[moshea70]

Still thinking about the combo box idea, but just came accross this:

http://www.exceltip.com/at-424|Menus%2C_Toolbars%2C_Status_bar_in_VBA-Pass_arguments_to_macros_from_buttons_and_menus_using_VBA_in_Microsoft_Excel

Which give the key line of code to pass a parameter (in this case, a caption name) as:

.OnAction = "'MyMacroName2 """ & .Caption & """'"

Just what I need! Only, I can't get it to work.

This is my code... any ideas? When I run it I don't get an error, but the macro doesn't run either.


Sub AddSupportSheetsSubMenu(mm As CommandBarPopup, blnBeginGroup As Boolean)
Dim sh As Worksheet
Dim m As CommandBarPopup, mi As CommandBarButton
If mm Is Nothing Then Exit Sub
' create the submenu
Set m = mm.Controls.Add(msoControlPopup, , , , True)
With m
.BeginGroup = blnBeginGroup
.Caption = "Support and scratch sheets..."
End With

For Each sh In Worksheets
' add a menu item
Set mi = m.Controls.Add(msoControlButton, , , , True)
With mi
.Caption = sh.Name
.OnAction = "'MyMacroName2 """ & .Caption & """'"
.FaceId = 66
.Style = msoButtonIconAndCaption
End With
End If
Next sh
Set mi = Nothing
Set m = Nothing
End Sub

....
Sub MyMacroName2(SheetName as String)
Sheets(SheetName).Select
End Sub

[Norie]

What is it you are trying to acheive?

Is it some sort of menu so you can jump from sheet to sheet?

If so I have code (at work) that on opening creates a combobox containing all the sheets in the workbook.

It also creates a 'Go' button, that when the user presses it Excel jumps to the sheet chosen in the combobox.

Is that what you're trying?