Assigned Macro with argument?

  • Thread starter Thread starter Ryder S
  • Start date Start date
R

Ryder S

Hi all,

When I assign macros to my worksheet buttons, there are times that I
would like to pass on an argument along with that macro call. Is
there a way to do this?

Thank you!

Ryder
 
Hello Ryder,

If you are going to click a button then somewhere you have to type the
argument. Where are you going to do this?

If in a cell on a worksheet or control on a userform then you can pick up
the cell's/control's value in the code.

You can also pickup the ActiveCell value.

Alternatively an inputbox at the start of the code.
 
Hello Ryder,

If you are going to click a button then somewhere you have to type the
argument. Where are you going to do this?

If in a cell on a worksheet or control on a userform then you can pick up
the cell's/control's value in the code.

You can also pickup the ActiveCell value.

Alternatively an inputbox at the start of the code.

--
Regards,

OssieMac








- Show quoted text -
Click to expand...

Well, not really... the argument would be provided by what calls it...
not the person.

In this particular case, I want different buttons on different
worksheets to be able to call the same function or sub, but I want the
function or sub to know something about what button was used to call
it.

So my thinking is this: when you "Assign Macro" to a button on a
worksheet, there may be a syntax one can take advantage of.

If this is not the case, then how can buttons be assigned to launch
macros that require an argument be supplied?

-RS-
 
hello again Ryder,

I understand better now.

Parent.Caller returns the button name like the following:

Sub MyRoutine()
MsgBox Parent.Caller
End Sub

I am assuming that you are using Forms buttons from the Forms Toolbar and
not ActiveX controls from the Control Toolbox Toolbar. With the Forms buttons
you can have the same button name on different worksheets so you may also
need to identify the active worksheet in your code.

Personally I would call a separate subroutine with each button and then have
that sub routine call the main processing routine with the required
parameters like the following:

Note that a space and underscore at the end of a line is a line break in an
otherwise single line of code (including the sub name).

Sub Sheet1_Button1_Click()
Dim ws As Worksheet
Dim strWhatever As String
Dim intNumber As Integer

Set ws = ActiveSheet
strWhatever = Parent.Caller & " and My message"
intNumber = Range("A10")

Call MyRoutine(ws, strWhatever, intNumber)
End Sub

Sub Sheet2_Button1_Click()
Dim ws As Worksheet
Dim strWhatever As String
Dim intNumber As Integer

Set ws = ActiveSheet
strWhatever = Parent.Caller & " and Your message"
intNumber = Range("B6")

Call MyRoutine(ws, strWhatever, intNumber)
End Sub

Sub MyRoutine(ws As Worksheet, _
strWhatever As String, _
intNumber As Integer)

MsgBox ws.Name & vbCrLf & _
strWhatever & vbCrLf & _
intNumber

End Sub

My real preference is to use ActiveX controls from the Control Toolbox
toolbar that have their own event sub and then call the main routine from
there similar to the above example but from event subs. By using the Design
button (a button that looks like a set square, ruler and pencil) you can
customize the button name and the formatting etc through the properties.)
 
Back
Top