Results 1 to 13 of 13
  1. #1
    3 Star Lounger
    Join Date
    Apr 2007
    Location
    Lancashire, United Kingdom
    Posts
    264
    Thanks
    32
    Thanked 5 Times in 5 Posts

    How do I display a message?

    I have a macro that takes a minute or two to work through. How do I display a message so they know something is happening and then remove it when the macro completes?

    I don't want to use anything that needs a user interaction (e.g. msgbox).

    thanks

    alan

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    You can either use the statusbar, or create a userform with a label/textbox on it that contains your desired message, then use:
    Code:
    userform1.show false
    userform1.repaint
    ' your processing code here
    '...
    unload userform1
    for example. If you have loops in the code you can get fancier and add a progress meter if you wish.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    3 Star Lounger
    Join Date
    Apr 2007
    Location
    Lancashire, United Kingdom
    Posts
    264
    Thanks
    32
    Thanked 5 Times in 5 Posts
    I'd like to use the status bar - that sounds good - but how?

    Alan

    edit - just seen application.statusbar in the help - I'll try that

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Alan,

    Here's some code I found on the web a while ago that may be of some use. HTH

    VBA - Status Messages in Dialog Box.xls
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    3 Star Lounger
    Join Date
    Apr 2007
    Location
    Lancashire, United Kingdom
    Posts
    264
    Thanks
    32
    Thanked 5 Times in 5 Posts
    many thanks both - but I've done it with a simple "application.statusbar" message. It's only so the user doesn't think the system is looping.

    I have 20 passes through a routine and just have a simple "pass n/20" message which updates itself as the routine is accessed.

    Once again - thanks.

    Alan

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    FWIW, there's a nice simple progress bar example here: http://www.experts-exchange.com/Soft...soft-Apps.html
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    You can also use the Microsoft Progress Bar Control version 6.0 available from at least versions 2003 to 2010. Must add it to the tool box to place it on a form. Set the min, max, and starting values. To create a fluid progression of the bar, set the scrolling property in the properties window to smooth. By setting the ProgressBar.value equal to an incrementing counter inside a loop, the bar will progress proportionally to the number of loops.

    Progressbar.png Progressbar2.png Progressbar3.png

    Code:
    Private Sub CommandButton1_Click()
    
    'SET INITIAL VALUES
    ProgressBar1.Min = 1
    ProgressBar1.Max = 90000
    ProgressBar1.Value = 1
    
    For I = 1 To 90000
        '---CODE GOES HERE---
        ProgressBar1.Value = I  'INCREASE PROGRESS BAR
    Next I
    UserForm1.Hide
    
    End Sub
    Maud
    Attached Files Attached Files

  8. The Following User Says Thank You to Maudibe For This Useful Post:

    RetiredGeek (2013-07-13)

  9. #8
    3 Star Lounger
    Join Date
    Apr 2007
    Location
    Lancashire, United Kingdom
    Posts
    264
    Thanks
    32
    Thanked 5 Times in 5 Posts
    Thanks again all. I can't use a progress bar because I don't know how many items I am processing (I just read down the spreadsheet till I get to an empty cell). But the status bar fix does it for me.

    Alan

  10. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    You could count the items in the range at runtime before starting the loop to get the number...

    Steve

  11. #10
    3 Star Lounger
    Join Date
    Apr 2007
    Location
    Lancashire, United Kingdom
    Posts
    264
    Thanks
    32
    Thanked 5 Times in 5 Posts
    Yes, I know, but I don't. It's just more code and will take a while (there are many thousands of lines).

    As I said, I have a solution using the status bar which works for me. I like the progress indicator and may well use it in a future project.

    Alan

  12. #11
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi Alan

    If you are processing a block of records, with data headings in, say row 1, then you might be able to quickly get the number of records in the block by using this vba code line:

    zCount = [a1].currentregion.rows.count -1

    (The -1 is to not count the heading row)

    zeddy

  13. The Following User Says Thank You to zeddy For This Useful Post:

    alan sh (2013-08-02)

  14. #12
    3 Star Lounger
    Join Date
    Apr 2007
    Location
    Lancashire, United Kingdom
    Posts
    264
    Thanks
    32
    Thanked 5 Times in 5 Posts
    and that's why I love this forum.....

    I'll try that next week.

    Thanks

    Alan

  15. #13
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Alan,

    Another possibility is the use of a Dynamic Range Name. This works well if you have, as Zeddy say's a block of recorde e.g. no blank rows.

    Template: =OFFSET(Sheet1!StartCell,0,0,COUNTA(Sheet1!$A$2:$A $MaxNumberOfRows),NumOfCols)

    Example: Starting in Cell A2 using up to a maximum of 1000 rows and 1 column wide you would enter the following as the formula to the Range Name.

    =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A$2:$A$1002 ),1)

    The above is the definition of the range name MyRngName.

    Now I can use it in any formula e.g. =Sum(MyRngName)

    The nice thing about this is you can add/delete rows and the range automatically adjusts as do the results of the formula and no VBA required. Just make sure to change your MaxNumberOfRows if you pass the original setting. I usually set it high enough so I'll never have to worry about it, but then again I'm never dealing with that many records.
    DynamicRangeName.JPG
    HTH



    where: $A$2 is the starting point
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  16. The Following User Says Thank You to RetiredGeek For This Useful Post:

    alan sh (2013-08-05)

Posting Permissions

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