Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assigned Macro with argument?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assigned Macro with argument?
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 "Ryder S" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assigned Macro with argument?
On Jul 29, 6:30*pm, OssieMac
wrote: 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 "Ryder S" wrote: 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- Hide quoted text - - Show quoted text - 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- |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assigned Macro with argument?
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.) -- Regards, OssieMac |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Use Macro To Change Which Macro Assigned To Command Button | Excel Discussion (Misc queries) | |||
Assigned macro change | Excel Programming | |||
Why can't I run an assigned macro? | Excel Programming | |||
Macro assigned to a button | Excel Discussion (Misc queries) | |||
Function (array argument, range argument, string argument) vba | Excel Programming |