Calling an image "alt text" for use in a macro?

[Cheshire]

Hello,

I'm hoping there is some way to call whatever text is stored in the "alt text" field of images placed in an excel sheet for use as a variable / string reference in a macro.

The "alt text" will not be changed in any way by the macro.

Any help on this would be greatly appreciated!
(One always gets more excitable as a project gets closer to working, sorry )

-Aaron

[Paul]

You can reference the alternative text of a shape like so:

Please Login or Register  to view this content.

You could also loop through the shapes on your worksheet to find the right one if necessary, or use the shape's index rather than name.

Please Login or Register  to view this content.

[Cheshire]

Hmm, is there a way to reference directly the shape in question that was clicked?

So if I clicked the "red cat" shape, which had been assigned a macro, the macro could pull "red cat" from the alt text, and fill it into cell A1. But if I clicked the "green bird" shape, and have the same macro assigned to it, the macro will pull "green bird" from the alt text field and fill it into cell A1?

[Paul]

You might try this...

1. Open your workbook, then open the VB Editor (ALT+F11).
2. In the VBE, click Insert -> Module
3. Paste the code shown below into that module, then close the VBE.

Please Login or Register  to view this content.

4. Right-click on each of your shapes and choose Assign Macro, then select the "Cheshire" macro from the list and click OK.

Change the Sheet name referenced in the code to your actual sheet name, of course. Hopefully that works for you!

[Cheshire]

Paul,
This is astounding!
Thank you so much!

Admittedly I havn't fully tested it yet,
But this will save me a lot of time and coding space,
Instead of writing a new macro for each variable a image needs to call,
I can (hopefully) use this to populate it on a per image basis!

Once I'm 100% sure it works flawlessly I'll come back, make one more post, and mark this as resolved!

You're fantastic!

-Aaron

[Cheshire]

Hmm, the first test work (a verbatim c&p of your code),

Trying to use it in such a way:

Please Login or Register  to view this content.

Returns: "Runtime error '-2147352571 (80020005)': The item with the specified name wasn't found"
with the 'systemSerial =...' line highlighted.

When hoving over 'Application.Caller' it reads "Error = 2023".
That's all I can figure out is the source of the problem. Why it is happening I dont know.

[Paul]

In your code, you reference

Please Login or Register  to view this content.

If you click a shape that isn't on the first sheet in the workbook tabs order, this is going to fail and generate the error you see. Try changing "Sheets(1)" to "ActiveSheet" and leave the rest the same.

[Cheshire]

Alright, I tried changing the " Sheets(1) " to " ActiveSheet ", but I'm getting the same error of " Shapes(Application.Caller) " returning error 2023.

This is all hapening within the VB debugger?

Whenever I try to use the " Sheets(1).Shapes(Application.Caller).AlternativeText " as a value or in conjunction with a string, Application.Caller errors out.

Specifically what I would be looking for is something along the lines of the following pseudo-code:

Please Login or Register  to view this content.

I hope that is even possible.

Thank you for your patience,

-Aaron

[Andy Pope]

The application.caller will only work if the shape has that macro assigned to it's OnAction property.

If you are running the routine some other way then it will not be valid.

[shg]

Just to rephrase what Andy said: the Caller property (see Help) tells you how the routine you're in got invoked.

[Cheshire]

Thank you so much for that last touch of clarification, it all works wonderfully now! (after a bit of tweaking and getting fed up that I couldn't figure out how to assign a string & concatenate a variable to the end of it, and settled for concatenating two variables)


This is the final result:

Please Login or Register  to view this content.

Paul,Andy Pope,sgh you've all been amazing!

-Aaron