Excel Form progress bar within macro problem

[nicknco]

hi there,

I am trying to run a progress bar based on moving an image within a form control whilst I am running a macro in the background. However, the form control prevents the rest of the macro from looping. I have tried using Unload Form to close the form until the macro loops back, but this doesn't work. I attach the draft code below. Can anyone help? Thanks alot!

Sub Sales()
'
' Sales Macro
'

'
Dim sngPercent As Double
Dim intMax As Integer
Dim y As Integer


y = Range("Start_year")
intMax = Range("End_year") - Range("Start_year") + 1

Do While y < Range("End_year") + 1

' progress bar
Application.ScreenUpdating = True
sngPercent = (y - Range("Start_year")) / intMax
Load UserForm2
UserForm2.Show
UserForm2.Repaint
UserForm2.imgXPMarker.Left = 50 + (UserForm2.labXPBackground.Width * sngPercent)
Range("sng").Offset((y - 2011), 0) = sngPercent
Application.ScreenUpdating = False
UserForm2.Hide
Unload UserForm2
DoEvents

' main loop

Sheets("Uptake Calculations").Select
Range("Calculation_year") = y
ActiveSheet.Calculate

Range("Annual_sales").Select
Range("Annual_sales").Copy

Sheets("Annual Sales").Select

Range("Output").Offset((y - 2011) * 12, 0).Select
Range("Output").Offset((y - 2011) * 12, 0).PasteSpecial xlPasteValuesAndNumberFormats


y = y + 1

Loop

Worksheets("Control").Select

Application.ScreenUpdating = True

'
End Sub

[royUK]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here