macro running .... message box ? is it possible?
macro running .... message box ? is it possible?
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: macro running .... message box ? is it possible?

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    i am running a macro and i have included Application.ScreenUpdating = false. Is there a way a message can be displayed saying macro executing please wait.... when the macro is running in the background.

  2. #2
    Guest

    Default

    The only possibility I know is to use the status bar (grey bar on the bottom left of the screen, which usually says "Ready").

    To change the text of the status bar, use the code

    Application.StatusBar = "Any Text"

    At the end of your macro, you should put the code

    Application.StatusBar = False

    otherwise the last message will remain after your macro has ended.

  3. #3
    New Member
    Join Date
    Mar 2002
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks. That helps.

  4. #4
    New Member
    Join Date
    Mar 2002
    Posts
    48
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    This may be a little too much than what your asking for, however you could use a user form to create a percentage indicator, just like you see in other windows applications. Create a label on the form and set the background color to something like blue or red. Then just stretch the label within the code after a certain number of statements have been executed. For this to work, simply put your existing macro in the UserForm_Initialize() event, and create a function that will set the bar to a specified length with parameters, and throughout your macro place call statements to this "stretching" procedure. Its a REALLY cool way to show a "working in the background" status bar. If you want more info just post.

    Chris

  5. #5
    New Member
    Join Date
    Mar 2002
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Ya more info will help thanks

  6. #6
    New Member
    Join Date
    Mar 2002
    Posts
    48
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Ok, I looked all over to find a web page displaying this, with no success. So I will try to explain it the best way i can without writing the code, because it can get rather lengthy...Also note that I told you to place the code in the Initialize event...dont do this! I have a remedy below...

    o Build a userform (not to large), and change its Caption property to "ProgBar"

    o Add a Frame Control in the center, and name it "FrmProg"

    o Add a label control inside of the Frame and make its background color blue, name this LblProg, and change its special effect to frmSpecialEffectRaised

    o Now you should have a rectangular Userform, a frame in the center with a long bar inside the frame (the bar is just the label with a blue background)

    o Place the following code in the code for the userform, replacing xxx with your prodedure (the one you want to have running in the background while the status bar is in front)
    Private Sub UserForm_Activate()
    Call xxx
    End Sub

    o Insert the following code after that:
    Sub UpdateProg(Pct)
    With ProgBar
    .frmProg.Caption = Format(Pct, "0%")
    .LblProg.Width = Pct * (.frmProg.Width - 5)
    .Repaint
    End With
    End Sub

    o YOUR ALMOST DONE! Now, you want to insert calls to UpdateProg throughout your xxx procedure, the with the parameter Pct equal to the percent your xxx procedure is completed. For example, when the code is 50% done, place "Call UpdateProg(.5)" in your xxx procedure.

    o To get it to run, instead of calling the xxx procedure, load the form and show it, NEVER CALLING XXX (this is important). Do this by inserting the following wherever you initially called XXX, call it say ShowProgIndicator:
    Sub ShowProgIndicator()
    Load frmProg
    frmProg.LabelProg.width=0
    frmProg.Show
    End Sub


    WHEW taht was a lot... Remember the key is to situate all teh objects on your form so it looks like a progress bar youve seen before, then run the code...GOOD LUCK!!!

    Chris

  7. #7
    . MrExcel's Avatar
    Join Date
    Feb 2002
    Location
    Merritt Island Florida
    Posts
    858
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default

    I could've sworn someone had this on the 'net, but now I think I remember seeing it in J-Walk's Power Programming with Excel 2000 book - yes pages 396-400.

    It is a pretty cool effect - I've used it a few times.

    Bill
    View a collection of recent Excel articles in the Excel Daily News

  8. #8
    New Member
    Join Date
    Mar 2002
    Posts
    48
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I Knew it was from somewhere because i remember seeing it a while ago. I couldnt find it to post though (I even looked through J-walks site for the Power Programming in VBA book).

  9. #9
    New Member
    Join Date
    Mar 2002
    Posts
    48
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    I'll also note Microsoft actually has a similar version posted on their excel site, but this one is much nicer

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com