Shape Onaction macro not found on first call

[JSmithCol]

I have a really weird one with a Shape that has it's onaction set to a macro. I have a shape where the parameter I want to use is stored in the AlternativeText of the shape. the line below is setting the OnAction command to call a macro named "getApplicationTab" passing the alternativetext as a paramater.

sh.OnAction = "'getApplicationTab """ & sh.AlternativeText & """'"

This code works perfectly except for the first time it is invoked when you open the workbook. When I first open the workbook and click on the shape I get the error Cannot run the macro "getApplicationTab "Online Banking"". The macro may not be available in this workbook or all macros may be disabled.

The funny this is when I click the shape a second time it works perfectly and continues to work.

 

[RoryA]

Unfortunately, since this is undocumented syntax anyway, it's extremely hard to classify any behaviour as a bug. (For example, if you try and use it in an .xlsb file, you will have real problems)

Anyway, given that you are using a property of the shape as the parameter, why don't you just have the called macro read that directly rather than passing it as an argument?

 

[JSmithCol]

Unfortunately, since this is undocumented syntax anyway, it's extremely hard to classify any behaviour as a bug. (For example, if you try and use it in an .xlsb file, you will have real problems)

Anyway, given that you are using a property of the shape as the parameter, why don't you just have the called macro read that directly rather than passing it as an argument?

I would be fine with that. I have multiple shapes on the sheet with the same Macro assigned. How do you determine which shape was clicked on when the macro gets invoked?

 

[RoryA]

You'd use something like:

Dim sParameter as String
sParameter = Activesheet.Shapes(Application.Caller).AlternativeText

 

[JSmithCol]

You'd use something like:

Dim sParameter as String
sParameter = Activesheet.Shapes(Application.Caller).AlternativeText

Thank you. That's what I needed. Worked like a charm.

 

[RoryA]

Glad to help - welcome to the forum too!