Identifying the command button that called the macro

[MSingh]

Hi,

How can i identify the command button that calls a macro/initialises a userform:
eg.
If i have an activex worksheet command button as well as a command button on userform1, both of which initialises userform2 containing dtpicker1.
dtpicker1 has cmdinsert to insert the date:
if called from worksheet control then insert date in activesheet.range("A1") if called from useform1 then insert date in textbox1 on userfrom1.

Is this possible?
It would eliminate having several calendars.
Also, could you please suggest where can i read about this so that i can include same in other similar macros.

Thanks again,
Mohamed

[StuartR]

In Excel VBA you can use Application.Caller to identify the command button that called a Macro.

[HansV]

You could use a global variable to keep track of where the userform was called from.

At the top of a standard module:

Code: Select all

Public gobjCaller As Object

In the On Click code of the command buttons on the sheet and on userform1:

Code: Select all

    Set gobjCaller = Me

In the On Click code of the command button on userform2:

Code: Select all

      If TypeName(gobjCaller) = "Worksheet" Then
        gobjCaller.Range("A1") = ...
    Else
        gobjCaller.TextBox1 = ...
    End If

[HansV]

In Excel VBA you can use Application.Caller to identify the command button that called a Macro.

That'll work for Form command buttons on a worksheet but not for ActiveX command buttons...

[MSingh]

Thanks Hans & Stuart,

Your assistances is always appreciated.

Kind Regards
Mohamed