Hello:

I'm looking for a way to carry a variable with an
assigned value into a called macro.

I have 10 CheckBoxes on a worksheet. When box 1 is
checked, I want to assign a variable (call it X) the
value equal to the checkbox number. Example: when box 1
is checked, I want X = 1, when box 2 is checked, I want X
= 2, etc.

I have a macro which I want to "Call" and execute for
each box. The same macro is called for each box; the
only difference is the value of the variable X, which the
macro uses to do its thing.

When I set it up like this:
Private Sub CheckBox 1_Click()
X = 1
Call {macro}
End Sub

I get an error which tells me that X is not recognized by
the macro. When I hardcode X=1 in the macro, it works.

Is there a way to carry X into the macro? I'm trying to
avoid having 10 nearly identical macros in my spreadsheet.

I could settle for modifying the macro by adding 10
lines:
If [CheckBox 1 is clicked] then X = 1
If [CheckBox 2 is clicked] then X = 2, etc.

But I don't know the correct syntax for the code between
the []'s.

First option (carrying the value) is preferred, but 2nd
option (modifying the macro) will be OK with me.

Help on either is appreciated.

Thanks,
MARTY

Pass X as a macro argument

Call myMacro(X)

and in the macro

Sub myMacro(XVal)

MsgBox XVal
'etc.

End SUb

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Marty" wrote in message
...
Hello:

I'm looking for a way to carry a variable with an
assigned value into a called macro.

I have 10 CheckBoxes on a worksheet. When box 1 is
checked, I want to assign a variable (call it X) the
value equal to the checkbox number. Example: when box 1
is checked, I want X = 1, when box 2 is checked, I want X
= 2, etc.

I have a macro which I want to "Call" and execute for
each box. The same macro is called for each box; the
only difference is the value of the variable X, which the
macro uses to do its thing.

When I set it up like this:
Private Sub CheckBox 1_Click()
X = 1
Call {macro}
End Sub

I get an error which tells me that X is not recognized by
the macro. When I hardcode X=1 in the macro, it works.

Is there a way to carry X into the macro? I'm trying to
avoid having 10 nearly identical macros in my spreadsheet.

I could settle for modifying the macro by adding 10
lines:
If [CheckBox 1 is clicked] then X = 1
If [CheckBox 2 is clicked] then X = 2, etc.

But I don't know the correct syntax for the code between
the []'s.

First option (carrying the value) is preferred, but 2nd
option (modifying the macro) will be OK with me.

Help on either is appreciated.

Thanks,
MARTY

Perfect Bob! Thank you vey much.
-----Original Message-----
Pass X as a macro argument

Call myMacro(X)

and in the macro

Sub myMacro(XVal)

MsgBox XVal
'etc.

End SUb

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Marty" wrote in
message
...
Hello:

I'm looking for a way to carry a variable with an
assigned value into a called macro.

I have 10 CheckBoxes on a worksheet. When box 1 is
checked, I want to assign a variable (call it X) the
value equal to the checkbox number. Example: when
box 1
is checked, I want X = 1, when box 2 is checked, I
want X
= 2, etc.

I have a macro which I want to "Call" and execute for
each box. The same macro is called for each box; the
only difference is the value of the variable X, which
the
macro uses to do its thing.

When I set it up like this:
Private Sub CheckBox 1_Click()
X = 1
Call {macro}
End Sub

I get an error which tells me that X is not recognized
by
the macro. When I hardcode X=1 in the macro, it works.

Is there a way to carry X into the macro? I'm trying
to
avoid having 10 nearly identical macros in my
spreadsheet.

I could settle for modifying the macro by adding 10
lines:
If [CheckBox 1 is clicked] then X = 1
If [CheckBox 2 is clicked] then X = 2, etc.

But I don't know the correct syntax for the code
between
the []'s.

First option (carrying the value) is preferred, but 2nd
option (modifying the macro) will be OK with me.

Help on either is appreciated.

Thanks,
MARTY


.