H

I am stuck with creating a progress bar and was directed to the below site for help

http://j-walk.com/ss/excel/tips/tip34.ht
Progress Ba

Unfortunately, I do not have a clue as to how to fit this example into my own work

/////////////////////////////////////////

This is my original request

I have a spreadsheet that contains a lot of information about client personal, loan & account details

I have created a macro that turns all this information into individual statements for each client in a new worksheet

What I would like to do if possible, is to have a progress bar that shows how much is done and is left to do as the process is being carried out. Currently, a new worksheet openms but, apart from the hour glass, it does not look like anything is happening and may cause another user to try and stop or restart the process

Any help wopuld be greatly appreciated.

By the way, all the work done creating the macro was done with lots of help from people on these Excel communities as I am not a programmer in any shape or form, so with this in mind, please give as much information as you possibly can if the problem can be solved

///////////////////////////////////////

It would be greatly appreciated if anyone can help any further

Also, if it helps, I can forward a working cut down copy of the Excel Sheet to anyone who wants more information

Kind regard

Malcolm

Hi,

John Walkenbach's progress macro shows how to put a
modeless progress bar on the screen. You'd need to
substitute the parts of his code that relate to updating
individual cells to the appropriate sections of your own
code. It's quite a useful technique once you master it.

In particular, you'd need to modify the following portion
of the code by inserting your own counter updating process:

RowMax = 64
ColMax = 64
' Loop through cells.
For r = 1 To RowMax
For c = 1 To ColMax
'Put a random number in a cell
Cells(r, c) = Int(Rnd * 1000)
Counter = Counter + 1
Next c

You may not even base your code on a row*column matrix, in
which case all references to RowMax and ColMax might
become redundant.

Before you can give the user feedback on your macro's
progress, you'll need some sort of counter to keep track
of where your macro's up to. If you want to express the
result as a %, you'll also need a number representing
whatever the counter will be at once you reach 100%. In an
Excel worksheet, the count could be based on the number of
cells, worksheets or other data to be processed - it's up
to you to choose the most suitable.

Also, bear in mind that your various routines may each
take different periods to perform a given action, so you
might need to weight them differently.

Another approach is to display a message on the status
bar. This is much easier to code, but is perhaps rather
less likely to be noticed. To do this:

1 Prefix your code with:
Option Explicit
Dim SBar As Integer

This stores the current state of the status bar so that it
can be restored after your code has finished.

2 Call the following MacroEntry and MacroExit subs from
the first and last lines of your macro, respectively, both
for feedback and maximum execution speed:

Sub MacroEntry()
SBar = Application.DisplayStatusBar
Application.DisplayStatusBar = True
Application.ScreenUpdating = False
Application.Calculation = xlManual
End Sub

Sub MacroExit()
Application.Calculation = xlAutomatic
Application.StatusBar = False
Application.DisplayStatusBar = SBar
Application.ScreenUpdating = True
End Sub

3 In the body of your macro, where all the work's being
done, insert lines like:

CurrentCount = CurrentCount +1
and
Application.StatusBar = Int(CurrentCount / TotalCount *
100) & "% Complete"
or
Application.StatusBar = "Processing Record " & CurrentCount

where "CurrentCount" and "TotalCount" are the names of the
counter that keeps track of where your macro's up to and
the number representing whatever the counter will be at
once you reach 100%, respectively. The second form is more
useful when you don't know how many items there are to
deal with. These lines update the status bar.


Cheers
-----Original Message-----
Hi

I am stuck with creating a progress bar and was directed
to the below site for help.

http://j-walk.com/ss/excel/tips/tip34.htm
Progress Bar

Unfortunately, I do not have a clue as to how to fit this
example into my own work.


//////////////////////////////////////////

This is my original request.

I have a spreadsheet that contains a lot of information
about client personal, loan & account details.

I have created a macro that turns all this information
into individual statements for each client in a new
worksheet.

What I would like to do if possible, is to have a
progress bar that shows how much is done and is left to do
as the process is being carried out. Currently, a new
worksheet openms but, apart from the hour glass, it does
not look like anything is happening and may cause another
user to try and stop or restart the process.

Any help wopuld be greatly appreciated.

By the way, all the work done creating the macro was done
with lots of help from people on these Excel communities
as I am not a programmer in any shape or form, so with
this in mind, please give as much information as you
possibly can if the problem can be solved.

////////////////////////////////////////

It would be greatly appreciated if anyone can help any
further.

Also, if it helps, I can forward a working cut down copy
of the Excel Sheet to anyone who wants more information.

Kind regards

Malcolm
.

Please feel free to send me the file

-----Original Message-----
Hi

I am stuck with creating a progress bar and was directed
to the below site for help.

http://j-walk.com/ss/excel/tips/tip34.htm
Progress Bar

Unfortunately, I do not have a clue as to how to fit
this example into my own work.


//////////////////////////////////////////

This is my original request.

I have a spreadsheet that contains a lot of information
about client personal, loan & account details.

I have created a macro that turns all this information
into individual statements for each client in a new
worksheet.

What I would like to do if possible, is to have a
progress bar that shows how much is done and is left to
do as the process is being carried out. Currently, a new
worksheet openms but, apart from the hour glass, it does
not look like anything is happening and may cause another
user to try and stop or restart the process.

Any help wopuld be greatly appreciated.

By the way, all the work done creating the macro was
done with lots of help from people on these Excel
communities as I am not a programmer in any shape or
form, so with this in mind, please give as much
information as you possibly can if the problem can be
solved.

////////////////////////////////////////

It would be greatly appreciated if anyone can help any
further.

Also, if it helps, I can forward a working cut down copy
of the Excel Sheet to anyone who wants more information.

Kind regards

Malcolm
.