progress bar in excel

Alexxx12 · #1 (**permalink**) 12-08-04, 14:23

hi

win2k, xp
excel 2k

I have an excel application with a form which, when initialized brings about a message with a yes/no option. if yes, then it initialize the progressbar with min and max and then opens the progressbar form. but it does not work. all that happens is that the progressbar shows up but none of the routines run and the progressbar remains blank. can anyone help?

Code:

Private Sub UserForm_Initialize()
On Error Resume Next
    Dim MYANS As String
    MYANS = MsgBox("Do you want REFRESH the reports?.", vbYesNo, "Refresh REPORTS..")
    If MYANS = vbYes Then
    	Progress_bar.Test
    End If

End Sub


Private Sub UserForm_Initialize()
     Progress_bar.ProgressBar1.Min = 1
     Progress_bar.ProgressBar1.Max = 6
End Sub
Public Sub Test()
    Dim pr_bar As Integer
            
    pr_bar = 1
    Me.Show
    Call refload_weekly_for_el
        pr_bar = pr_bar + 1
        Progress_bar.ProgressBar1.Value = pr_bar
    Call refload_weekly_for_sp
         pr_bar = pr_bar + 1
        Progress_bar.ProgressBar1.Value = pr_bar
    Call refload_weekly_for_em
         pr_bar = pr_bar + 1
        Progress_bar.ProgressBar1.Value = pr_bar
    Call refresh_daily_for_el
         pr_bar = pr_bar + 1
        Progress_bar.ProgressBar1.Value = pr_bar
    Call refresh_daily_for_sp
         pr_bar = pr_bar + 1
        Progress_bar.ProgressBar1.Value = pr_bar
    Call refresh_daily_for_em
         pr_bar = pr_bar + 1
        Progress_bar.ProgressBar1.Value = pr_bar
    If pr_bar = 6 Then
        Unload Progress_bar
        Exit Sub
    End If
End Sub

MikeTheBike · #2 (**permalink**) 12-09-04, 08:16

Hi

I think my brain hurts looking at your code.

I assume you have two UserForms and that the two Initialize events are not really in the same module/form!

Also you are calling the ‘Test’ routine from one Iniialize event and Me.Show in the 'Test' routine. This implies that ‘Me’ is loaded and initialized (but hidden) and the test routine is written in ‘Me’

However if you are trying to show a UserForm with a progress bar from code, and show the progress of the code that opened the ProgressBar form, then I don't think that it is possible.

In my experience when a UserForm is opened code execution in the calling code is suspended until the form is hidden/unloaded.

Is that not the case ?

Hope that make some sort of sense (as I said my brain hurts)!

MTB

Alexxx12 · #3 (**permalink**) 12-10-04, 11:21

hi,

I have attached a document with little more info: it still does not work:

MikeTheBike · #4 (**permalink**) 12-12-04, 05:11

Hi Aelxxx12

I have looked at your post (and found you previous posting).

As I said before the problem is that code execution is suspended when a form opens (Shown) untill the user does something to set of an event.

I think you have made it over complicated using the Initialze events. I only use these if I need to load info the the user need in the form ie List or Combo boxes etc.

I have put together an illistation of how it could be done in the attached zip.

I have assumed that the Refresh procedures are not in the first form you try to open, therefore I have opened it afterwords (if they are then use the Load UserForm1 I have Remed out).

I have also used the Select Case constuct for easier code maintenance in the future ( ie if you add say three Monthley proutines you would increase the cProcedureCount constant from 6 to 9 and the loop and progress bar will look after themselves.

Hope this helps

MTB

Internet Services

Web Development

Digital Marketing

Consumer Tech