Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    USA
    Posts
    223
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Find assigned macro for a picture (2000 sp3)

    I have a sheet with various shapes and buttons on it. In VBA I'm trying to make a list of those shapes and, if they have one, also list the assigned macro or procedure name. This isn't too hard for some types of shapes but I'm stuck on the Picture type. I used objShp.OnAction for msoAutoshapes and msoFormControls. I used objShp.OLEFormat.Object.Object.Caption for msoOLEControlObject. Neither of these return the name of the macro assigned to a msoPicture type shape. Can this be done?
    Thanks for any help you can give.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Find assigned macro for a picture (2000 sp3)

    As a test, I inserted a picture on a worksheet and assigned it a macro.
    The following code correctly reports the name of the assigned macro in the Immediate window:

    Sub ShapeTest()
    Dim shp As Shape
    For Each shp In ActiveSheet.Shapes
    If shp.Type = msoPicture Then
    Debug.Print "Picture " & shp.Name & ": " & shp.OnAction
    Else
    Debug.Print "Other type: " & shp.Name
    End If
    Next shp
    End Sub

    What happens if you run it against your sheet?

  3. #3
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    USA
    Posts
    223
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Find assigned macro for a picture (2000 sp3)

    I get nothing back. I just tried selecting the picture and then using selection.onaction and it worked! And, with it selected, objshp.OnAction works too. Maybe I had something else wrong in the code. I'll use your sample to check my code. Thanks again!
    Thanks for the quick reply!

  4. #4
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    USA
    Posts
    223
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Find assigned macro for a picture (2000 sp3)

    Oops! My bad. The sheet had way more pictures on it than I realized and many of them do not have macros assigned. I just happened to be hitting on all ones with no macros in the beginning of my loop. Sorry to waste your time on this one.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Find assigned macro for a picture (2000 sp3)

    Don't worry, it can happen to all of us.

Posting Permissions

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