Progress Bar in VBA(Excel)

[NarenMCA]

Hi,

I want to use status bar control 6.0 in VBA(Excel). Other functions in the Macro will be performing some copy/paste and filtering operations. I want the progress bar to show status based on the tasks performed by those functions.

Can any one help me in this.

Thanks in advance,
Naren

[kadghar]

i wouldnt recomend you to use controls other than the default ones. (txtbox, command button, etc)

in this case, i'd use the Excel's status bar to show the progress. Check this little example:

Sub StsBar()
    Dim t As Single
    t = Timer
    Do
        DoEvents
        Application.StatusBar = Timer - t
    Loop Until Timer > t + 10
End Sub
 

well, hth

[NarenMCA]

i wouldnt recomend you to use controls other than the default ones. (txtbox, command button, etc)

in this case, i'd use the Excel's status bar to show the progress. Check this little example:

Sub StsBar()
    Dim t As Single
    t = Timer
    Do
        DoEvents
        Application.StatusBar = Timer - t
    Loop Until Timer > t + 10
End Sub
 

well, hth

Thanks for you suggestion.

But I am trying this because I just want to learn this.

-Naren

[kadghar]

Thanks for you suggestion.

But I am trying this because I just want to learn this.

-Naren

Yeah, well, then go to the Tools menu > Aditional Controls (inside the VBA editor)

there you'll have to add some Microsoft Progress Bar control (and check the library that contains it, so when you run this macro in some others' office, it might tell you that you need an specific library, in that case, you'll have to go to the tools menu > references, and browse the file to add it, in most cases that file is already in most of computers, but just in case, make sure you know where to download it or have a copy in a pen drive)

Once you've added it, it'll be in your tools menu and you can use its method and properties as it were any other control.

HTH

[kadghar]

And i forgot to say

check te first example i wrote using DoEvents, you may find it useful, just change the control instead of the Excel's statusbar.