CODEWITHSUNDEEP
excelvbaobjectbuttonarguments
ericauv
ericauv

Reputation: 170

Pass worksheet and button object from Command Button

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

Upvotes: 0

Views: 553

Answers (1)

ericauv
ericauv

Reputation: 170

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

Upvotes: 0

Related Questions