MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Progress Bar and Browse window in Excel macro

  Asked By: Craig    Date: Jan 03    Category: MS Office    Views: 2520

Can anyone help me how can we use progress bar when some activity is performing
in Excel Macro and how to use Browse button to select a particular file from the
hard disk in Excel macro.



9 Answers Found

Answer #1    Answered By: Helina Bonkob     Answered On: Jan 03

To open a file  from a browser use:

Dim DataFile as String
DataFile = Application.GetOpenFilename("Excel Files (*.xls), *.xls") 'get
Excel file name
Application.Workbooks.Open DataFile 'Open Excel file

Answer #2    Answered By: Cara Lewis     Answered On: Jan 03

Do you have any idea, how can we use progress  bar in Excel VBA?

Answer #3    Answered By: Luis Fischer     Answered On: Jan 03

There are ways to do it in userforms, but rather than do that, I usually
just use Application.Statusbar = "Currently Processing XXXX"
If I'm cycling through rows, I'll do something like:

If (data.row mod 100 = 0) then Application.statusbar = "Processing Row: "
& data.row
(so that it only updates the statusbar every 100 rows)

be sure to use: Application.statusbar = false
to reset it at the end of the sub.

Answer #4    Answered By: Xavier Thompson     Answered On: Jan 03

I have used the below mentioned coding but, I haven't get anything on the
screen. Could you please check this out?

Private Sub init()
For i = 1 To 65000
If i = 100 Then
'MsgBox "check"
End If

If (i Mod 100 = 0) Then
Application.StatusBar = True
Application.StatusBar = "Processing Row:" & i
End If

Cells(i, 1) = i
Next i
Application.StatusBar = False
End Sub

Moreover, is that anyway can we use the progress  bar (like a window  which is
used for installation process)? Please advice me

Answer #5    Answered By: Damon Perez     Answered On: Jan 03

To utilize a progress  meter control you need to add a 'User Form'. You
would then need to call the form whenever the macro/program is executed.
A 'User Form' will have standard controls available, but the 'Progress
Meter' tool may need to be added. Simply right mouse click the controls
that appear next to the user form, and select  'Additional Controls'.
Scroll down until you see 'Microsoft Progress Meter'. There may be more
than one version, I always use the most recent version.

Insert the control on your user form, and give it a logical name, along
with your user form. Now it is time for code.

Here is a procedure that calculates a percentage of completion and
updates the progress meter control.

Sub IncreaseBar(iCurVal As Integer, iMaxVal As Integer)

Dim Percent As String

Dim PercentLabel As String

Dim sMaxVal As String

Dim sCurVal As String

'Put integers into strings

sMaxVal = iMaxVal

sCurVal = iCurVal

'Increase progress bar

Percent = getPercentage(sCurVal, sMaxVal)

Forms!frmMain!EmpProgressBar.Value = Percent 'Make sure to use your
form name, and name of control!!


End Sub

Function getPercentage(ProgressBarCurrentValue As String,
ProgressBarMaxValue As String) As String

'calculate Percentage

getPercentage = Format(Val(Val(ProgressBarCurrentValue /
ProgressBarMaxValue) * 100), "0")

End Function

As you can see in the main sub procedure, you need to bring in the total
rows being calculated, and the current row position being worked on. You
can alter this any way you like, such as calculating number of rows etc.
within the procedure. My code is a secondary sub procedure as I port
this to all my programs where I use a progress meter. I have many, and
the users like to see it.

Answer #6    Answered By: Betty Fischer     Answered On: Jan 03

I have created the new userform and included the Microsoft progress  bar into
that. But when i execute the user form is waiting until I press close button  of
the progress bar  user form and then its moving on to the next record.

Moreover, I am unable to understand the below mentioned command.

"Forms!frmMain! EmpProgressBar. Value = Percent 'Make sure to use your
form name, and name of control!!"

I have enclosed the excel  file with coding for your easy reference. Could you
please help  me out at the earliest?

Answer #7    Answered By: Mabel Davis     Answered On: Jan 03

I replied to your email address just a few minutes ago, and replied here
to the group to inform you of this. It appears as though both my replies
have yet to make it to you as of yet.

As of the forms reference confusion, that original line of code is
actually for MSAccess, I forgot to adjust before sending to you.

When referring to a form control within Excel simply use the form name,
followed by the control name.

UserForm1!ProgressBar.Value = Percent

Answer #8    Answered By: Ernesto Robinson     Answered On: Jan 03

I think there are a couple of points that may be confusing to you
The .Value property of the progress  bar is 1 to 100, or implied percent.
so, you have to figure out when your "job" is 1%, 2%, 10%... whatever,
then update the .value of the Progress bar  with this number.
Second, Craig's example doesn't seem to be for Excel, (probably for Access).
The way I would handle it:
If your userform name is: ProgressForm
and your progress bar name is: ProgressBar1
then the Value property is set by:
ProgressForm.ProgressBar1.Value = (some calculated integer from 1 to 100)

Next, your question about having the macro  wait until the userform exits
The term for this is called "modal" (no idea why, and I don't care!)
the default for your userform is "modal", you want a "modalless" form.
To do that, in your properties box, set ShowModal to False.
I think you can use
Progressform.ShowModal = false
in your script before you issue the Progressform.Show
but then, you'd have to remember to due that any time you use this form.
It's easier to just put it in the Properties.

I created:
Sub Progbar()
Dim inx, Modcnt, I, Percnt
ProgressForm.ProgressBar1.Value = 0
Modcnt = Int(65000 / 100)
For I = 1 To 65000
If (I Mod Modcnt = 0) Then
Percnt = I / Modcnt
Application.StatusBar = Percnt
ProgressForm.ProgressBar1.Value = Percnt
If (Percnt Mod 10 = 0) Then Application.Wait (Now +
End If
Next I
Unload ProgressForm
Application.StatusBar = False
End Sub

Answer #9    Answered By: Marion Hayes     Answered On: Jan 03

Yeah, my original need was for Access, though I've changed it for Excel
since then. I just happened to grab the Access function by mistake. The
point is it works, it's just a matter of putting it all together for
ones needs.

Didn't find what you were looking for? Find more on Progress Bar and Browse window in Excel macro Or get search suggestion and latest updates.