Can't work on Word CommandButton object from within Excel

Question

I'm writing an Excel macro that opens up a Word document and looks for a CommandButton object, by Name. When it finds the object, it tries to check if it has a picture associated with it. It seems to be locating the object, but dies a "catastrophic" death when I try to reference the handle of the picture. I've done this before and looking to see if the picture's handle is zero has worked for me. Not sure what's up here, maybe someone else can see what I'm missing?

Set objWord = CreateObject("Word.Application")
Set objDoc = objWord.Documents.Open(strFileName)
objWord.Visible = True

Set cmdSignatureButton = fncGetCommandButtonByName("NameOfCommandButtonImLookingFor", objDoc)
MsgBox "h=" & cmdSignatureButton.Picture.Handle
' It dies here, giving the error: 
'   Runtime error -2147418113 (8000ffff)
'   Automation error
'   Catastrophic failure



Private Function fncGetCommandButtonByName(strName As String, objDoc As Word.Document)
    Dim obj As Object
    Dim i As Integer

    For i = objDoc.InlineShapes.Count To 1 Step -1
        With objDoc.InlineShapes(i)
            If .Type = 5 Then
                If .OLEFormat.Object.Name = strName Then
                    Set fncGetCommandButtonByName = .OLEFormat.Object
    MsgBox "Found the Command Button object"        ' Seems to find the CommandButton object here
                    Exit Function
                End If
            End If
        End With
    Next
End Function

I wonder if it is failing to load the objects completely by the time the code accesses it. Have the images gotten larger or is there more of them since this worked before? Just for testing, what if you add a small 3 second `pause` using this [pause function](http://stackoverflow.com/questions/6960434/timing-delays-in-vba) after `objWord.Visible = True` `pause(3)` — Jimmy Smith, Aug 19 '16 at 20:31
In this case, there is no picture on the Command Button object. It should return zero for the handle. — Chuck Shultz, Aug 19 '16 at 20:34
Make sure the Function has a type, e.g. `Private Function fncGetCommandButtonByName(strName As String, objDoc As Word.Document) as Object`. Also type check during the other sub to make sure the item is in fact an object. E.g. `if isObject(cmdSignatureButton) then`, see if that helps — Ryan Wildry, Aug 19 '16 at 20:46
I added in a step "If cmdSignatureButton.Picture Is Nothing Then", but it got the same "catastrophic failure" message when it hit that step. — Chuck Shultz, Aug 19 '16 at 20:48
I added the "As Object" behind the function and a test for whether the found object is an object, but no change. — Chuck Shultz, Aug 19 '16 at 20:51

score 0 · Answer 1 · answered Aug 19 '16 at 21:09

I was able to get this functioning without an issue. You may want to step through the code to see if the document is fully loaded first.

Here's the code that's working for me, edited to match the format of the original question posed.

    Dim objWord As Object: Set objWord = CreateObject("Word.Application")
    Dim objDoc  As Object: Set objDoc = objWord.Documents.Open(strFileName)

    objWord.Visible = True
    Dim cmdSignatureButton As Object

    Set cmdSignatureButton = fncGetCommandButtonByName("CommandButton1", objDoc)

    If Not cmdSignatureButton Is Nothing Then
        'Do something when it isn't nothing
        MsgBox "h=" & cmdSignatureButton.Picture.Handle
    Else
        'Something here
    End If

Private Function fncGetCommandButtonByName(strName As String, objDoc As Word.Document) As Object
    Dim i As Integer

    For i = objDoc.InlineShapes.Count To 1 Step -1
        With objDoc.InlineShapes(i)
            If .Type = 5 Then
                If .OLEFormat.Object.Name = strName Then
                    Set fncGetCommandButtonByName = .OLEFormat.Object
                    Exit Function
                End If
            End If
        End With
    Next

    Set fncGetCommandButtonByName = Nothing 'set it equal to nothing when it fails
End Function

If you are still receiving that error, I'm thinking it may have something to do with the picture not being fully loaded. If so, I'd add some error handling to catch that error and process a retry a second later to see if the picture's handle is available.

Here's what I get when I run that code:

I'm doing this and still coming up with the error. I put in a Pause command to wait for 5 seconds and that doesn't help. In fact, the command button I am looking at doesn't have a picture associated with it (at other times, it might). There seems to be more going on here. I am wondering if I need to re-install Office to fix this? (I find this annoying, if so.) — Chuck Shultz, Aug 22 '16 at 13:10
I just did a full re-install of Office, but the problem did not go away. Ugh! — Chuck Shultz, Aug 22 '16 at 13:29
In my scenario, the button begins with a label, like "Sign by Employee", then that label is removed when the employee presses the button and signs the document. If the signature isn't there (i.e. no picture associated with the button), I can MsgBox the label with no problem. Referencing the Picture property is when things break. — Chuck Shultz, Aug 22 '16 at 13:45
Another bit of info...I just copied my files to a laptop and they broke in the same manner, "catastrophically". — Chuck Shultz, Aug 22 '16 at 14:24
I just tried creating the file from scratch on the laptop and running it there. It's also dying at the spot where it tries to reference the Picture object's Handle number. Slightly different result - the error text now says "Method 'Picture' of object 'ICommandButton' failed". — Chuck Shultz, Aug 22 '16 at 17:18

score 0 · Accepted Answer · answered Aug 23 '16 at 20:23

OK, I think I have an approach, at least. I moved on to my next problem, which is very similar. In this case, I am looking for images within Command Buttons within an Excel spreadsheet, but I'm doing so from Access. Instead of trying to jump through hoops and get Access VBA to interrogate the Excel file, I put a Public Function into the Excel file that Access calls. Excel has no problem checking the button for an image, so it just returns the answer for me.

Had to figure out how to Run Public Functions, but that was easy enough. Thanks for the feedback, Ryan. Still not sure why yours worked and mine didn't, but at least I got around it.

Can't work on Word CommandButton object from within Excel

2 Answers2