progress box

[vinny2984]

I have a macro which takes a few minutes to do its work.
Does anyone know if there is a way to display a message when the macro starts saying something like "this procedure is likley to take several minutes blah blah" and have that message remain on the screen until the macro ends, or even show a progress bar.

any odeas would be great
thanks

[mdmackillop]

Simplest is to use the StatusBar. You'll see a sample in the VBA Help file.

[Paul_Hossler]

or even show a progress bar.


Many ways, I guess, but you could add a user form and a ProgressBar control, along with text and other information

You'll have to add the ProgressBar to your Controls toolbox (right click on the toolbox). I also added the StatusBar control. Note that this is not the same thing as Application.StatusBar = "some message"

The ProgressBar is in MSCOMCTL.OCX, so it should be on any machine


Show the User Form, process and update the ProgressBar, and then Hide the form when you're done


Option Explicit
'pauses processing
Declare Sub Sleep Lib "KERNEL32" (ByVal dwMilliseconds As Long)
'pass the amount of time (in milliseconds)
Sub TakeNap(timelength As Long)
Sleep (timelength)
End Sub

Sub Demo()

Dim i As Long
Dim oForm As UserForm
Dim oStatus As StatusBar
Dim oProgress As ProgressBar


Load UserForm1

Set oForm = UserForm1
Set oStatus = oForm.StatusBar1
Set oProgress = oForm.ProgressBar1

With oProgress
.Min = 27
.Max = 483
End With

With oStatus

.Width = oProgress.Width

.Panels.Add
.Panels.Add

.Panels(1).Width = 0.25 * .Width
.Panels(2).Width = 0.5 * .Width
.Panels(3).Width = 0.25 * .Width

End With

Call UserForm1.Show(vbModeless) ' need the vbModeless

oForm.Repaint

With oProgress

oStatus.Panels(1).Text = "Min = " & .Min
oStatus.Panels(3).Text = "Max = " & .Max

For i = .Min To .Max

.Value = i

oStatus.Panels(2).Text = "Current = " & _
Format(i / .Max, "0.0%")
Call Sleep(10)

Next i

End With

UserForm1.Hide

Unload UserForm1

End Sub


Paul

[Paul_Hossler]

Better make that math a little better


oStatus.Panels(2).Text = "Current = " & _
Format((i - .Min) / (.Max - .Min), "0.0%")