CODEWITHSUNDEEP
excelvba
Aravind
Aravind

Reputation: 3

A Code to find out who called a macro attached to a shape( Dropdown in my case) in Excel VBA

I have a dropdown in an excel worksheet with an assigned macro which is written to return the dropdown name but in vain

Private Sub Cmb()
   'cmb is the assinged macro name

    Workbooks("Test2").Worksheets("Sheet3").Activate
    MsgBox ActiveSheet.Shapes(Application.Caller).Name

End Sub

It is returning the item with specified name wasn't found

Please help me to resolve this issue. I'm a beginner in excel .So it would be grateful if a detailed explanation is included

Upvotes: 0

Views: 1148

Answers (1)

Vikas
Vikas

Reputation: 805

Since you have mentioned that you are assigning a Macro to Combobox, it is, for sure, a Form Control Combobox and not an activeX control. So the Application.Caller would definitely return the name of the combobox you are calling the macro from. Did you try this:

MsgBox "The name of the combobox is " & Application.Caller

Edit: After receiving the spreadsheet.

So basically : there are two Errors:

 Workbooks("Test2").Worksheets("Sheet3").Activate

You don't need the above line, because when you click a Dropdown, the required sheet would always be active. The main error in your macro is coming because Test2 is not the workbook. Use Workbooks("Test2.xlsm")..........

Then we come to the next line:

You have used this:

ActiveSheet.Shapes(Application.Caller).Name

Now the Name property returns a String. So you need to store that value to a Variable. The error is coming because you are not storing it in any variable. Use this:

Dim shpName as String

shpName = ActiveSheet.Shapes(Application.Caller).Name

Error will be removed. I have also sent you back the spreadsheet. Please let us know if you have any questions.

Thanks, V

Upvotes: 1

Related Questions