How to display progress bar when userform is loading in VBA excel -

i have created macro using userform , has many controls static in nature , displays upon userform intialization. has (userform initialize code) code written add checkboxes in 1 of frame dynamically using data in sheet1. taking bit of time(say 30 sec-1 min) depending on data present in sheet.

during period want user shown progress bar of % completion.

i tried application.statusbar functionality didnt workout. thought go progressbar. can please in regard?

this progress bar i've used last 5 or 6 years (originally posted in http://www.mrexcel.com/forum/excel-questions/527468-progress-bar.html).

i'd follow rorys advice though , use listbox if you're creating potentially hundreds of controls.

create form called 'progress bar'

give these dimensions:
name: progressbar
height: 49.5
width: 483.75
showmodal: false <---- important bit or won't update properly.

add label form these dimensions:
name: boxprogress
caption: boxprogress
height: 18
left: 6
top: 6
width: 468
backcolour: &h008080ff&

in normal module add procedure:

sub updateprogressbar(n long, m long, optional displaytext string) '// darksprout april08 '// omit displaytext display progress percentage on error goto err_handle  if n >= m     progressbar.hide else     if progressbar.visible = false 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 in code this:

sub test()      dim x long      x = 1 100         updateprogressbar x, 100     next x  end sub 

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

the variables:
m represents maximum number bar reach , n represents current value display.