Results 1 to 11 of 11
  1. #1
    Lounger
    Join Date
    Nov 2003
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Update User from Word Macro (Word 2007)

    I have a macro application which can take some time to run (3-4 minutes). For some users, depending on their default settings) they simply get a blank white document until the macro has completed.

    I want to display a periodic update message (e.g. "Reading Database", "Producing Basic Merge Document, "Collapsing Categories"....), but I don't want the macro to stop waiting for a user to input a response, so usual MsgBox approaches won't work . I just want to let them know that things are progressing.

    I can't find a way to do this. Anyone have some ideas how to do this?

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Update User from Word Macro (Word 2007)

    Would the Status Bar be too unnoticeable? Can you write to the Status Bar in Word 2007??

    Another options would be a non-modal UserForm. Here's an example of how you could call the form (the Sleep function is just to create a delay, you won't need it):

    <code>Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

    Sub ShowStatus(strMessage)
    ' Display strMessage in UserForm
    If frmStatus.Visible = False Then
    Load frmStatus
    frmStatus.Show vbModeless
    End If
    If strMessage = "UNLOADFORM" Then
    Unload frmStatus
    End If
    With frmStatus
    .Label1.Caption = strMessage
    .Repaint
    End With
    End Sub

    Sub TEST_ShowStatus()
    ShowStatus "Hello."
    Sleep 3000
    ShowStatus "Please wait another 3 seconds."
    Sleep 3000
    ShowStatus "Thank you for playing."
    Sleep 3000
    ShowStatus "UNLOADFORM"
    End Sub</code>
    Attached Files Attached Files

  3. #3
    Lounger
    Join Date
    Nov 2003
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update User from Word Macro (Word 2007)

    Thanks jsher, the Status Bar would have been good enough, except that I don't think you can write to it in Word 2007. Least ways, the methods I looked at were "discontinued". I've run into quite a few changes with writing this in Word 2007.

    I think the code you've provided may well do the trick. I follow the code, just need to load it up and play with it for a bit to make sure I get it. (This may need to wait until tomorrow now). I'll let you you know how it goes.

    thanks

  4. #4
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Update User from Word Macro (Word 2007)

    You may want to consider using a progress bar as demonstrated in the attached document.

    H.T.H.
    Attached Files Attached Files
    Regards
    Don

  5. #5
    Lounger
    Join Date
    Nov 2003
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update User from Word Macro (Word 2007)

    Thanks Don, I've looked at your code and will keep it in mind - sure it will come in handy for another job.

    For now, the code from Jscher2000 fits the bill very nicely and was very easy to implement - thank you jscher.

    Thanks to both of you for responding to my request. I've learned some useful tips/tricks from both solutions - always nice to learn new ways of tackling things.

  6. #6
    Lounger
    Join Date
    Nov 2003
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update User from Word Macro (Word 2007)

    OK, I spoke a little too soon. I have one more problem. The code as provided works great on a standalone test document. However, the production item is producing a mailmerge document and then making changes to it. So the merged document sits in the foreground and hides the userform. Any ideas on how to make the userform sit on top of the activewindow.

    jsher2000?

    thanks

  7. #7
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Update User from Word Macro (Word 2007)

    The trick would be to find a method or API function that makes places the modeless UserForm the top-most Windows within the Word application, but does not get in the way of any other applications. There must be a way to do this, but... I Googled this a bit, and did not see an immediate solution that I could recommend without a whole lot of testing.

  8. #8
    Lounger
    Join Date
    Nov 2003
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update User from Word Macro (Word 2007)

    OK, thanks. I too googled it and got lost in some of the possible solutions so was just hoping that perhaps you had a quick method that would work. I'll play some more and report back (for others) if I find a solution.

  9. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update User from Word Macro (Word 2007)

    Would you mind terribly? if I<pre> If Len(strMessage) = 0 Then
    Unload frmStatus
    End If</pre>

    Regardless, Thanks for the nifty bit of Word2003/Excel2003 code

  10. #10
    Lounger
    Join Date
    Nov 2003
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update User from Word Macro (Word 2007)

    An update to the problem relating to visibility of the userform.

    Background: To clarify my problem and how I solved it. I have a Word Macro which performs a mailmerge from a database and then performs some significant work on the resulting document to create a 100+ page directory, with images etc etc. When it runs it can take up to 3-4 minutes on a fast PC. Depending on all sorts of conditions (perhaps including the weather ), sometimes the merge document is not visible while the processing is taking place. jsher2000 kindly provided a solution to my request to display a status update so that the users could see that something was still happening. Unfortunately, the userform created by this code was not visible after the merge document was created, thus defeating its purpose. I searched for easy ways to keep the form on top, but even when I could force that, it didn't show the updated output...

    My Solution: It seems that the form is associated with the document which is active at the time that you load it. So if you load it before the merge it remains associated with the source document and does not become visible when the merge document becomes the active document. The solution is simple. Unload the form just before the merge. Perform the merge and then re-load the form. The form is then associated with the new active document. Too easy (but it took me ages to figure this out). So hopefully this will be useful for someone else in the future.

    Thanks all for you input.

  11. #11
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update User from Word Macro (Word 2007)

    >My Solution: It seems that the form is associated with the document which is active at the time that you load it
    Thanks for this follow up.
    I'm in Excel2003, and the display sometimes freezes while the program runs in the background; end result, the job runs, but the display is not updated. I might explore various refresh/repaint/activate options next week to see if I can jiggle it loose.

    >(perhaps including the weather <img src=/S/smile.gif border=0 alt=smile width=15 height=15> )
    OTOH it might be the weather after all: Jan 23 is the warmest day so far this month, Phillips said. On Jan. 13, the thermometer reached 1.1C for about 10 minutes, marking the only other time this month the mercury climbed above 0C.

Posting Permissions

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