Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2002
    Location
    NJ
    Posts
    139

    Unanswered: progress bar in excel

    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

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    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

  3. #3
    Join Date
    Sep 2002
    Location
    NJ
    Posts
    139

    progress bar in excel

    hi,

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

  4. #4
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1

    Progress Bar

    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
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •