+ Reply to Thread
Results 1 to 11 of 11

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

  1. #1
    Registered User
    Join Date
    12-18-2008
    Location
    White Rock
    Posts
    76

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

    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
    Last edited by Cheshire; 08-21-2009 at 03:56 PM.

  2. #2
    Forum Moderator Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,799

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

    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.

  3. #3
    Registered User
    Join Date
    12-18-2008
    Location
    White Rock
    Posts
    76

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

    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?

  4. #4
    Forum Moderator Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,799

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

    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!

  5. #5
    Registered User
    Join Date
    12-18-2008
    Location
    White Rock
    Posts
    76

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

    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

  6. #6
    Registered User
    Join Date
    12-18-2008
    Location
    White Rock
    Posts
    76

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

    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.

  7. #7
    Forum Moderator Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,799

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

    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.

  8. #8
    Registered User
    Join Date
    12-18-2008
    Location
    White Rock
    Posts
    76

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

    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
    Last edited by Cheshire; 08-21-2009 at 01:43 PM.

  9. #9
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    19,740

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

    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.
    Cheers
    Andy
    www.andypope.info

  10. #10
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,655

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

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

  11. #11
    Registered User
    Join Date
    12-18-2008
    Location
    White Rock
    Posts
    76

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

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1