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

**Cheshire** · 08-19-2009, 01:21 PM

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** · 08-19-2009, 01:33 PM

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

myString = ActiveSheet.Shapes("Picture 1").AlternativeText

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.

myString = ActiveSheet.Shapes(4).AlternativeText

**Cheshire** · 08-19-2009, 01:42 PM

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** · 08-19-2009, 02:10 PM

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.

Sub Cheshire()
  Sheets("Sheet1").Range("A1").Value = Sheets("Sheet1").Shapes(Application.Caller).AlternativeText
End Sub

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** · 08-19-2009, 02:23 PM

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** · 08-19-2009, 02:47 PM

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

Trying to use it in such a way:

Sub setName()
    Dim item_name As String
    item_name = Sheets(1).Shapes(Application.Caller).AlternativeText
End Sub

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** · 08-19-2009, 02:54 PM

In your code, you reference

Sheets(1).Shapes(Application.Caller).AlternativeText

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** · 08-21-2009, 01:05 PM

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:

Declare testString to be a String variable
Set the value of testString to " ActiveSheet.Shapes(Application.Caller).AlternativeText "
Print out in cell C6:
 "The serial number of this item is [whatever the value of testString is] have a nice day."

I hope that is even possible.

Thank you for your patience,

-Aaron