pop up "loading"

[Raanan]

I have a dashboard that updates information form a raw file that opens and hides alongside it.

Is there a way I can have a pop up (message box type thing) that is a sort of "loading" screen so that users dont try do anything whilst calculations are being performed.

(excel 2007 the calculation thing at the bottom isn't enough )

what i mean is i have a progress bar that just looks good! I need one that updates WITH calculation bar if possible. Or loads in the front whilst calculating.

[Olly]

A modeless userform would do what you want.

[Raanan]

oh.. right..

ha ok..

I can read up on specifically what that means..

I assume it will pop up whilst XL does its things then close when done (or when I set it)

[Raanan]

Ok slight problem. the code im using for it keeps bringing an error if i try modeless

Sub Main()
    Dim Counter As Integer
    Dim RowMax As Integer, ColMax As Integer
    Dim r As Integer, c As Integer
    Dim PctDone As Single
    
    Application.ScreenUpdating = False
    
    ' Initialize variables.
    Counter = 1
    RowMax = 100
    ColMax = 25

    ' Loop through cells.
    For r = 1 To RowMax
        For c = 1 To ColMax
            'Put a random number in a cell
            Cells(r, c) = Int(Rnd * 1000)
            Counter = Counter + 1
        Next c

        ' Update the percentage completed.
        PctDone = Counter / (RowMax * ColMax)

        ' Call subroutine that updates the progress bar.
        UpdateProgressBar PctDone
    Next r
    ' The task is finished, so unload the UserForm.
    Unload UserForm1
End Sub

Sub UpdateProgressBar(PctDone As Single)
    With UserForm1

        ' Update the Caption property of the Frame control.
        .frameprogress.Caption = Format(PctDone, "0%")

        ' Widen the Label control.
        .labelprogress.Width = PctDone * _
            (.frameprogress.Width - 10)
    End With

    ' The DoEvents allows the UserForm to update.
    DoEvents
End Sub

Run-time error '50290': Method 'ScreenUpdating' of object '_Application' failed...



I guess I cant have modeless and the progress bar to update like that?

Whats the best way for it