1

I have created Macro using Userform and it has many controls which are Static in nature and displays upon userform intialization. But it also has (Userform initialize code) code written add checkboxes in one of the frame dynamically using the data in the sheet1. which is taking a bit of time(say 30 sec-1 min) depending on the data present in the sheet.

during this period i want to user to be shown a progress bar of % completion.

I tried Application.Statusbar functionality but it didnt workout. So thought to go for Progressbar. Can anyone please help in this regard?

Community
  • 1
  • 1
MSB705
  • 11
  • 1
  • 4
  • 1
    IMO, if it takes that long to load your form, you need to rethink what you are doing. – Rory Jun 25 '15 at 13:57
  • datasheet has more than 1000 rows data. From coulmn 1, I should pick up unique values and add them as checkboxes in Frame of Userform dynamically. This consumes time and thats where i am planning to add progress bar. – MSB705 Jun 25 '15 at 14:15
  • 1
    I stand by my comment - why would you want that many checkboxes rather than use a listbox? – Rory Jun 25 '15 at 14:26
  • I know how useful list box could be here, but the unique values that would come out fo the datasheet would maximum be 15. Hence i wanted to have them added as a checkbox(as it looks good :) ). Morever, there are two listboxes present on the form and which comes in to action after Userform is intiated. – MSB705 Jun 26 '15 at 06:58
  • If it's only 15 items out of 1000 rows, it really shouldn't take as long as you suggest. I think you ought to look at the code you use for that part. – Rory Jun 29 '15 at 07:53

1 Answers1

2

This is the progress bar I've used for the last five or six years (originally posted in http://www.mrexcel.com/forum/excel-questions/527468-progress-bar.html).

I'd follow Rorys advice though and use a listbox if you're creating potentially hundreds of controls.

Create a form called 'Progress Bar'

Give it these dimensions:
Name: ProgressBar
Height: 49.5
Width: 483.75
ShowModal: False <---- Important this bit or it won't update properly.

Add a label to the form with these dimensions:
Name: BoxProgress
Caption: BoxProgress
Height: 18
Left: 6
Top: 6
Width: 468
BackColour: &H008080FF&

In a normal module add this procedure: 

Sub UpdateProgressBar(n As Long, m As Long, Optional DisplayText As String)
'// DarkSprout April08
'// Omit DisplayText to display progress percentage
On Error GoTo ERR_HANDLE

If n >= m Then
    ProgressBar.Hide
Else
    If ProgressBar.Visible = False Then ProgressBar.Show
    ProgressBar![BoxProgress].Caption = IIf(DisplayText = "", Round(((n / m) * 10000) / 100) & "%", DisplayText)
    ProgressBar![BoxProgress].Width = (n / m) * 468
    DoEvents
End If
Exit Sub

ERR_HANDLE:
    Err.Clear
    ProgressBar.Hide
End Sub

Use it in your code like this:

Sub test()

    Dim x As Long

    For x = 1 To 100
        UpdateProgressBar x, 100
    Next x

End Sub

You'll need to call the procedure every time you want the progress bar to update.

The variables:
m represents the maximum number the bar will reach and n represents the current value to display.

Darren Bartrup-Cook
  • 18,362
  • 1
  • 23
  • 45
  • Thanks a lot, Darren. It's working as i needed. :) :) – MSB705 Jun 26 '15 at 07:40
  • But i would like to have a small change in this progress bar. Instead of long progress bar, how can i change this in to a small box where % completion appears with strip of colors or just a dot dot format? – MSB705 Jun 26 '15 at 07:51
  • Without any testing - I'd say remove `ProgressBar![BoxProgress].Width = (n / m) * 468` and set it to a static width then have a play with the Caption line to get the text you want. Can probably do something a bit more flashy with the newer version of Excel - the code was written for 2003. – Darren Bartrup-Cook Jun 26 '15 at 10:15
  • Thanks Darren. I adjusted the width and It came out really well :) – MSB705 Jul 03 '15 at 12:39