Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Atlanta
    Posts
    568
    Thanks
    7
    Thanked 1 Time in 1 Post

    Three Macro Questions (97)

    I have a very long macro called MicroTest for processing database files. It typically takes about 10 minutes to run. I have three questions:

    1. The tooltip for this macro is "Micro Test". Is there a way to change this to something more meaningful without renaming the macro?

    2. The macro mostly performs long search and replaces. Is there a way to inform the user of its progress?

    3. Is there a way to have the macro ask about one section and skip it if the user says no? That is, make one section of the macro optional.

    Thanks
    Ronny
    Ronny Richardson

  2. #2
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: Three Macro Questions (97)

    Three answers <img src=/S/smile.gif border=0 alt=smile width=15 height=15>


    1. <LI>If you search this board for the text tooltip you will find post 139718
      <LI>The easiest way to inform a user of progress is by means of some text in the Word status bar, e.g.
      <pre> StatusBar = "Replacing instance " & int(number) & " of " & "int(othernumber)"
      </pre>

      <LI>You could display a message box and then execute your code depending on which button the user preses. e.g.
      <pre> If vbYes = MsgBox("Do you want to do it?", vbYesNo) Then
      <font color=448800> ' Do whatever you asked about</font color=448800>
      Else
      <font color=448800> ' Do something different</font color=448800>
      End If
      </pre>
    StuartR

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Atlanta
    Posts
    568
    Thanks
    7
    Thanked 1 Time in 1 Post

    Re: Three Macro Questions (97)

    > 2.The easiest way to inform a user of progress is by means of some text in the Word status bar, e.g.
    >
    > StatusBar = "Replacing instance " & int(number) & " of " & "int(othernumber)"

    This does not work. Doing a search-and-replace causes Word to display the message "Word is replacing" at the bottom. (It does not say what it is replacing, just this phrase.) I'm guessing this overwrites the message the macro is trying to show.

    > 3.You could display a message box and then execute your code depending on which button the user preses. e.g.

    How would I display the dialog box and get the results into vbYes?

    Ronny
    Ronny Richardson

  4. #4
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: Three Macro Questions (97)

    1 out of 3 is a good start <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    I need to think about 2 a bit, maybe we can do something with a modeless dialog box, unless someone comes up with a better idea.

    I assume you didn't actually try 3. It does work. Here is some more detail to help explain...

    The MsgBox call will display a box and wait for the user to click a button. It takes a number of parameters, including the Title for the box (which I didn't specify in the example), the buttons to display (which I set to be Yes and No) and the text in the box (which I set to be "Do you want to do it?"). It returns a value depending on the button that was pressed, you can compare this return value to constants such as vbYes or vbCancel.

    VBA defines a number of useful constants that you can use with the message box, for example vbYesNo is the constant that says "display yes and no buttons only". We could have done something similar using vbOKCancel to display an OK button and a Cancel button.

    In the example, I displayed a Message box and compared the return status with the constant vbYes. I could have made the code a bit clearer by using a variable to store the return status like this.
    <pre>Dim iReply As Integer

    iReply = MsgBox( _
    Prompt:="Do you want to do it?", _
    Buttons:=vbYesNo, _
    Title:="My Question")
    If iReply = vbYes Then
    ' Do whatever you asked about
    Else
    ' Do something different
    End If
    </pre>

    This does exactly the same as the much shorter code that I posted.

    StuartR

  5. #5
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Re: Three Macro Questions (97)

    There is a way to keep the user updated by using a userform - modeless is not an option in Word 97 but it still can be done. There was a thread about this I think on the previous version of the Lounge.

    The basics are that you have the main procedure in the code module behind a userform. The userform does not get hidden or unloaded until the last of the code has run. The main procedure calls out to subprocedures that are in a standard code module. Before each call to the next procedure, there's code to make sure the text message in a userform label gets updated with a progress message, and the userform gets repainted. You can have a message like "Processing x% complete" with x updating with each repaint. Or you can simulate a progress bar. Or simulate an animation...

    Not something for a beginner, but not that hard either.

    Gary

  6. #6
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: Three Macro Questions (97)

    I had a play with a modeless dialog box for providing status updates on Word 2000 and it was much easier than I thought. I know it won't help the original poster, running Word 97, but it seems worth posting, since it was so easy to do...

    Insert a UserForm in the project. Call it frmProgress. Create a single label on the userform, call it lblProgress.

    At the beginning of your code include the line...
    <pre>frmProgress.Show vbModeless
    frmProgress.lblProgress = "Starting..."
    </pre>

    When you want to update the status include the line
    <pre>frmProgress.lblProgress = "Updated status"
    </pre>

    At the end of your processing
    <pre>Unload frmProgress
    </pre>


    When I tested this just now I had to include
    <pre>Application.ScreenRefresh
    </pre>

    each time I changed the label, because I had screen updating turned off whilst my Macro was running.

    StuartR

Posting Permissions

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