VBA Progress bar without a loop

Posted on 2013-10-14
Medium Priority
Last Modified: 2013-10-14

I have been trying unsuccessfully to implement the progress bar found in this Experts Exchange article, however, I am running into difficulty because my program does not use a loop.

I have attached the main XLSM file containing the actual program as well as the TXT file that is to be used when executing the program.

The sequence of events should follow:

-User opens 'EOM-Reports-Template.xlsm" file
-On sheet(START HERE), user presses 'Import SAP data' button and navigates to the location of the TXT file (in this case, the TXT file provided by me.)
-Once the text file is selected, a series of sub procedures execute, all of which are stored in the 'txt_import' module  This this the point I would like the progress par to popup and provide the user with a display of the script progress.
-Once these are finished, a Save As prompt asks the user to enter a name for the file and location.  The idea here is that the original file remains untouched and is closed after the user saves the file as a new document.

I have tried a few different solutions found when searching Google and Experts Exchange but they all seem to fail when the progress bar pops up.  AS the file stands now, the progress bar popup is disable so that you may see the intended order of operations.

Thank you in advance for your help.
Question by:peterschussheim
  • 2
LVL 52

Accepted Solution

Martin Liss earned 2000 total points
ID: 39571445
I found that article a while back and I use it without looping in one of my projects. I didn't like the colors so I changed them. Here's a working project. Run the PB macro.

Author Closing Comment

ID: 39571875
Thank you for the solution, it was what I was looking for.  I added another parameter to the "SetDialogValues" procedure:

Public Sub SetDialogValues(intValue, _
                           Optional ByVal statusValue As String)
    ProgressDialogue.SetStatus statusValue
    ProgressDialogue.SetValue intValue

End Sub

Open in new window

The statusValue variable allows me to update the text string with each integer update.

Thanks again
LVL 52

Expert Comment

by:Martin Liss
ID: 39572029
You're welcome and I'm glad I was able to help.

Marty - MVP 2009 to 2013

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Viewers will learn the basics about Excel 2013’s new Flash Fill feature.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

600 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question