assigning a variable from an image alt text

[john.9.williams]

I have a program that i am developing. What happens is when i click on
an inserted image i want a certain text to be input into a cell.

No problem here, but i have around 40 images that when click the macro
puts different text into a cell. I do not want to write 40 different
macros so was thinking that if i assign the image with the text in
its alt.text property when i run the macro the alt text becomes the
variable

i.e

Sub test ()

DIM text

text = image alt text (code to go here)

range("a1") = text


not sure if this is possible or is there another way i am missing

many thanks

 

[Tim Williams]

Within your Test() sub, Application.Caller will give you the name of
the image which was clicked.

So you can get a reference to the image and its "alt text" using

ActiveSheet.Shapes(Application.Caller).AlternativeText

You might find it easier to create a lookup table (maybe on a hidden
sheet) which maps image names to the text you want to display. You
can then use vlookup() to do the mapping.

Tim

 

[john.9.williams]

Within your Test() sub, Application.Caller will give you the name of
the image which was clicked.

So you can get a reference to the image and its "alt text" using

ActiveSheet.Shapes(Application.Caller).AlternativeText

You might find it easier to create a lookup table (maybe on a hidden
sheet) which maps image names to the text you want to display.  You
can then use vlookup() to do the mapping.

Tim









- Show quoted text -

Hi Thanks, could you explain a little further my vb is not great, how
would i incorperate this into my sub()

many thanks

 

[Tim Williams]

Sub Test()

On Error Resume Next
With ActiveSheet
.Range("A1").value = .Shapes(Application.Caller).AlternativeText
On Error Goto 0

End Sub

 

[Tim Williams]

Sub Test()


On Error Resume Next
With ActiveSheet
.Range("A1").value
= .Shapes(Application.Caller).AlternativeText
End With
On Error Goto 0


End Sub