asked on

VBA - Excel progress bar

Hi guys,

I'm currently working on a vba project where i'm a bit stuck, but found a solution but i don't know how to execute maybe you guys can help :)

What i'm seeking is a userform progress bar that gives me % of how far it has come in the import process. What i'm doing is that i'm running an import job with VBA that imports data from a Lotus Notes data base into a sheet, and its quit heavy and takes some time i want the end user to know how far it has come in the import process, i have to import 800 rows from lotus notes, would it be possible to create a progress bar for this? I have found various progress bars just not what i need.

in short I the progress bar to show that when it reach to row 80 thats equal to 10 % , row 160 equal to 20% and so on till row 800 equal to 100%

please advise, thank you in advance

Martin Liss

I'm attaching a workbook that implements a progress bar. You can adapt itto your needs.
Progress-Bar.xls

Martin Liss

Or you can simply do this which updates the status bar depending on what's in column A.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.StatusBar = Range("A1048576").End(xlUp).Row / 800 * 100 & "%"
End Sub

Open in new window

Martin Liss

And if you only want to see the progress percent change when 80, 160, 240, etc rows exist then

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("A1048576").End(xlUp).Row Mod 80 = 0 Then
    Application.StatusBar = Range("A1048576").End(xlUp).Row / 800 * 100 & "%"
End If
End Sub

Open in new window

Harsh Kumar

ASKER

Thank you for you quick reply Martin,

I'm sorry i'm quit new to VBA so still learning so please bare with me :)

I'm looking at the progress-bar.xls you have attached and it looks awesome but i'm seeing that it runs on time would it be possible for it to check up on rows instead? as in define that when it has reached to row 80 thats 10% and row 160 thats 20% etc.?

Harsh Kumar

ASKER

I cant get this to work... :(

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("A1048576").End(xlUp).Row Mod 80 = 0 Then
    Application.StatusBar = Range("A1048576").End(xlUp).Row / 800 * 100 & "%"
End If
End Sub

Open in new window

can you send me a sample file maybe?

Martin Liss

Add the 80th row and the status bar will show 10%.
28953690.xlsm

Harsh Kumar

ASKER

Sadly no it does not, could it be that I run Office2007 it wont work?

Martin Liss

When you add something in cell A80 and then select some other cell, what happens?

Harsh Kumar

ASKER

ohhh SORRY!!! it does work!! i was in a complete other work! my bad! now i just need a userform that will change by the % of the the stuatus bar

Martin Liss

Do you need help with that? If so what do you want in the userform and should it always be visible or just when the percent changes?

Harsh Kumar

ASKER

i would very much very much like that thank you, i just want a simple progress bar nothing too fancy, it should be visible untill its has reach too row 800 = 100% and then close down and then continues with rest og the macro's

Harsh Kumar

ASKER

this is what the process is:

*Auto start import when doc_open
*Userform progress bar starts counting <---- need help with this
*Import is done,
*close down progress bar <---- need help with this
*continues with rest of the macro's that is call in the sequence

ASKER CERTIFIED SOLUTION

Martin Liss

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Sjef Bosman

You can do this the other way round, and it might be a lot faster (which would make the need for a progress bar obsolete). It depends on what you want to do with the data.
It is possible to generate your data right from Notes/Domino, and present it directly in Excel. There is even more than one way to do that. If you're interested, and you are allowed to modify the design of your Notes database, I'm really willing to show you how it's done.

Harsh Kumar

ASKER

Works like a charm!!! thank you soo much for your assistance!

Sjef Bosman

No reaction to my comment?

Harsh Kumar

ASKER

Sorry Sjef, i did not see you comment?!?!? so sorry for that!! But what did ou have in mind? sadly i'm not able to deploy anything on the domino server :( thats is why i chose the Excle model.

Sjef Bosman

The following scenario:
- the Domino database should be made accessible from a browser
- create (quite simply) an agent in the Notes database
- the agent generates HTML, and the necessary information to force Excel to open and read the data
- it takes one URL to activate the spreadsheet

I'm not saying it's dead easy, but it's very doable.

Example, and some explanation:
http://www.codestore.net/store.nsf/unid/DOMM-4PSGWL/

Also:
http://www.breakingpar.com/bkp/home.nsf/0/87256B280015193F87256E32007999E6

Harsh Kumar

ASKER

Awesome Sjef!!! thanks alot! I will try this approach aswell :)

Sjef Bosman

:-) Do not hesitate to come back here, or ask a new question.

Harsh Kumar

ASKER

I will do thanks! wil eventually look at it next weekish

Martin Liss

You're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.

Marty - Microsoft MVP 2009 to 2016
Experts Exchange MVE 2015
Experts Exchange Top Expert Visual Basic Classic 2012 to 2015