0

Is there a way to pass the ActiveSheet object and an object as arguments to a sub that is called by a Command Button on a worksheet?

I know you can pass arguments to a macro from a command button from the following:

How to call a macro from a button and pass arguments

However how would you send the following arguments to a sub when Button_1 is clicked? (without using a commandButton_click event)

argument 1: ws as worksheet (the worksheet that contains the button that was pressed) argument 2: button as object (the button that was pressed)

I've tried the following in the "Assign Macro" input box for the button.

'SubToBeCalledByButton(ActiveSheet, this)'

Tried /u/Nathan_Sav suggestion to put call like: 'SubToBeCalledByButton(activesheet, activesheet.shapes("Button Name"))' but got the following error:

error

Here is input I used in assign macro:

Assign Macro

Here is the sub being called by the button:

Public Sub DetailButton(ws As Excel.Worksheet, but As Object)

    Debug.Print "ws:" & vbTab & ws.Name
    Debug.Print "but:" & vbTab & but.Name
    Debug.Print "cal:" & vbTab & Application.Caller

End Sub
ericauv
  • 160
  • 16
  • `public sub somesub(ws as excel.worksheet, objCommandButton as CommandButton)` call like so `somesub(activesheet,activesheet.shapes("commandbutton1")` along these lines, `application.caller` may assist you also, no need for the calling button argument – Nathan_Sav May 16 '18 at 14:40
  • Tried your call, please see editted post, came up with an error. What you wrote would work if I was calling the sub within another sub, but I am trying to call the sub from a command button by assigning the Sub as the macro for the button – ericauv May 16 '18 at 14:57
  • Solved my own need for this feature using Nathan_Sav's suggestion to use `Application.Caller` within the sub that is called by the button, and using `activesheet` within that same sub. However my question has not been answered on how to pass these as arguments to a macro called by a button on a sheet. – ericauv May 16 '18 at 15:04
  • What is your logic then? What ever the sub is, when called Activesheet will be the active sheet, no need to pass either..... Just assign MyMacro and then activesheet will be the one you want.. – Nathan_Sav May 16 '18 at 15:04

1 Answers1

0

From Nathan_Sav 's commment on my question:

There is no need for ActiveSheet and the CommandButton to be sent as arguments.

ActiveSheet can be obtained just by using ActiveSheet within the called sub, since the button that calls the sub must be on the ActiveSheet in order to be pressed.

Button (the button that called the sub) can be obtained using Application.Caller

ericauv
  • 160
  • 16