VBA & ProgressBar

[lintz]

My project is linked to a spreadsheet where a lot of calulations are done and a final result is then displayed in 1 of my forms. As the calculations can take some time (30 secs to 1 min) I want to add a progressbar so the user doesn't go mad waiting for the result to be displayed.

This is the code that does the calculations........so basically I need the min to start at 0 and progress until the "Do While" has finished. I have checked out some examples on progressbars but they all seem to be for DB's or timers. I haven't been able to find an exaple for VBA. Can anyone help me out??

Do While MaxDD < Val(txtMaxDrawDown.Text)
    Range("BC30").Select
    ActiveCell.FormulaR1C1 = Risk
    Worksheets("LBOP-New Capital p.a").Calculate
    Range("BC25").Select
    MaxDD = Range("BC25")
        Risk = Risk - 0.00002
Loop

[Ephesians]

There are a few ways you can do this. With a popup form you can have a coloured box that grows during the calculation process... with your code I would do something like this..

Do While MaxDD < Val(txtMaxDrawDown.Text)
    Range("BC30").Select
    ActiveCell.FormulaR1C1 = Risk
    Worksheets("LBOP-New Capital p.a").Calculate
    Range("BC25").Select
    MaxDD = Range("BC25")
        Risk = Risk - 0.00002
    Progress = Val(txtMaxDrawDown.Text) - (MaxDD/Val(txtMaxDrawDown.Text))
    boxProgress.Width = boxOriginal.Width - (boxOriginal.Width * Progress)
    doevents
Loop

[lintz]

I declared Progress as ProgressBar but not sure if that's correct. Also changed your "box" code to the name of my ProgressBar. Again not sure if that is correct. After the changes I ran my app but got an error at the "Progress" line. Anyone able to offer some more assistance? Thanks.

Dim Progress As ProgressBar

Do While MaxDD < Val(txtMaxDrawDown.Text)
    Range("BC30").Select
    ActiveCell.FormulaR1C1 = Risk
    Worksheets("LBOP-New Capital p.a").Calculate
    Range("BC25").Select
    MaxDD = Range("BC25")
        Risk = Risk - 0.00002
    Progress = Val(txtMaxDrawDown.Text) - (MaxDD / Val(txtMaxDrawDown.Text))
    ProgressBar1.Width = ProgressBar1.Width - (ProgressBar1.Width * Progress)
    DoEvents
Loop

[WorkHorse]

Progress should be a number - an intger or long. I don't know all your variables or calculations. Here's a simple example of a progress bar.

Dim lMinVal As Single
Dim lMaxVal As Single
 
lMinVal = 20
lMaxVal = 1000
 
ProgressBar1.Min = lMinVal
ProgressBar1.Max = lMaxVal
 
Do While lMinVal < lMaxVal
    lMaxVal = lMaxVal - 1
    ProgressBar1.Value = lMinVal + (ProgressBar1.Max - lMaxVal)
Loop