troubleshooting Question

VBA MsgBox command is dumping object variables?

Avatar of Bryce Bassett
Bryce BassettFlag for United States of America asked on
Microsoft OfficeVBAMicrosoft PowerPoint
8 Comments3 Solutions296 ViewsLast Modified:
Programming in PowerPoint 2010 VBA, an automatic bio slide maker.  The user selects up to 6 bios from a picklist, then the macro inserts photos and text elements.  Name, title, and bio text for each person are kept in separate Word documents (for ease of maintenance), and I use VBA, for each list chosen, to open the document, parse out the pieces, and stick them on the slide in the proper Text Placeholders.  

Because PowerPoint assigns new shape names when a slide is created from a layout, I use a complicated part of the macro (not shown here) to iterate all the Text Placeholders and use their position on the slide to assign them to a shape array that I can fill later.

Everything is working great as long as the macro finds all the supporting files.  For testing purposes, I renamed one of the word docs deliberately so it does not match the name on the pick list.  In that case, I want to warn the user, then continue on to fill the remaining placeholders.   As expected, when it runs into the missing doc, it warns, but then it loses track all of the remaining shape array objects and gives me an "Object Required" error.  I verified in the Immediate Pane that all of the biotextshapes have been dumped.   It doesn't matter whether it's the first name or somewhere in the middle, it works perfectly up until the msgbox, then the remaining code fails.  

When I try commenting out the msgbox, it skips the name it can't find, but then finishes the remaining ones correctly.  So I'm left to conclude there is something about the MsgBox line itself that is causing VBA to lose track of the shape array I already assigned.  Has anybody ever run into this behavior and have a workaround?  I could always hold the msgbox until after the For/Next loop completes, but I'm really stumped why it is doing this.
 
For x = 0 To Me.ListBox2.ListCount - 1
    doc2open = contentlibraryfolder & "\Staff\BIO Text\" & Me.ListBox2.List(x, 0) & " " & Me.ListBox2.List(x, 1) & ".docx"
    If Dir(doc2open) = "" Then
        MsgBox "bio text not found for " & Me.ListBox2.List(x, 0) & " " & Me.ListBox2.List(x, 1)
    Else
        Set wdDoc = wdApp.Documents.Open(FileName:=doc2open)
       ' NEXT LINE IS WHERE IT THROWS AN 'OBJECT REQUIRED' ERROR IN THE NEXT LOOP AFTER SHOWING THE MSGBOX
        biotextshape(x + 1, 1).TextFrame.TextRange.text = left(wdDoc.Paragraphs(1).Range, Len(wdDoc.Paragraphs(1).Range) - 1) ' name
        biotextshape(x + 1, 2).TextFrame.TextRange.text = left(wdDoc.Paragraphs(2).Range, Len(wdDoc.Paragraphs(2).Range) - 1) ' title
        biotextshape(x + 1, 3).TextFrame.TextRange.text = wdDoc.Paragraphs(3).Range
        wdDoc.Close
    End If
Next x
Thanks for any suggestions.
SOLUTION
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and Programming

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Top Expert 2015

The Distinguished Expert awards are presented to the top veteran and rookie experts to earn the most points in the top 50 topics.

Join our community to see this answer!
Unlock 3 Answers and 8 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 3 Answers and 8 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros