Activating a Macro from a Userform Automatically

[michaeldh]

Mr Excel,

I would like to have a userform appear and then subsequently perform an operation automatically ie without clicking a command button on the userform. Is this the Userform_Initialise command??

I want to click a button on Sheet1, have the userform appear and then carry out an operation.

I am using a basic example to better understand what I am doing so that I can apply it to a bigger project.

The Additional control I am using is Microsoft Progress Bar 6.0 (SP4)


Example:

Click a button within Sheet1 and activate userform1. Using a loop macro, repeat 1000 times placing the value of each increment in cell A1 of sheet1. Whilst everything is happenning make a Progress Bar increment from 1 to 1000 at the same time.

My code is as follows:

Private Sub CommandButton1_Click() 'Don't want to use a button

ProgressBar1.Min = 0
ProgressBar1.Max = 1000

For Count = 1 To 1000
ProgressBar1.Value = Count
'my code here
Sheets("Sheet1").Select
Range("A1").Select
ActiveCell = Count + 1
Next

Unload Me

End Sub

If you could help me out with some pointers I will definately owe you one!!

Thanks.

Michael.







[ This Message was edited by: michaeldh on 2002-08-18 03:03 ]

[tusharm]

There are many ways to create a progress bar.

1) The simplest way is to use the Application.Statusbar to provide a progress report. Two examples of this follow.

Sub useStatusbar1()

    Dim i As Long

    For i = 1 To 10

        Application.StatusBar = "Now on " & i & " out of 10"

        'do stuff

        Application.Wait (Now() + TimeSerial(0, 0, 1))

        Next i

    Application.StatusBar = False

    End Sub



Sub useStatusbar2()

    Dim i As Long

    For i = 1 To 10

        'do stuff

        Application.Wait (Now() + TimeSerial(0, 0, 1))

        Application.StatusBar = String(i, Chr(1)) _

            & " (" & CInt(i / 10 * 100) & "%)"

        Next i

    Application.StatusBar = False

    End Sub

2) If you must use a graphical display, there are three choices. The first two use the ProgressBar control.

2.1) The first is to have the user click something on the chart that initiates the process. For an example of that see the ProgressBar help in CMCTL198.chm.

2.2) The only way to create a progress bar programmatically and update it without user intervention is to create an asynchronous task. There are two ways to do that. Note that while the examples below may look simple, you are initiating parallel asynchronous tasks. This introduces a degree of complexity that may be masked by VBA but which can come back to haunt the inexperienced programmer.

2.2.1) Use a modeless userform. First, this requires XL2000 or newer. Second, creating a modeless userform initiates a concurrent asynchronous task. It becomes your responsibility to manage both your primary code 'stream,' so to say, and this new asynchronous stream that is displaying the progress bar.

Sub testProgBarModeless()

    Dim i As Integer

    With UserForm1

    .Show vbModeless

    .ProgressBar1.Value = 0

    For i = 1 To 10

        'do stuff

        Application.Wait (Now() + TimeSerial(0, 0, 1))

        .ProgressBar1.Value = CInt(i / 10 * 100)

        Next i

    .Hide

        End With

    End Sub

2.2.2) Use the OnTime method to intiate a asynchronous task. Something along the lines of

Sub testProgBarModal()

    Application.OnTime Now() + TimeSerial(0, 0, 3), "updateProgBarModal"

    With UserForm1

    .ProgressBar1.Value = 0

    .Show

        End With

    End Sub

Sub updateProgBarModal()

    'This is called for async processing by testProgBarModal

    Dim i As Integer

    With UserForm1

    For i = 1 To 10

        'do stuff

        Application.Wait (Now() + TimeSerial(0, 0, 1))

        .ProgressBar1.Value = CInt(i / 10 * 100)

        Next i

    .Hide

        End With

    End Sub

2.3) Create your own pictorial display and don't use the ProgressBar control. There is no guarantee that the control will exist on every machine on which the code will run. For an example of this you will have to wait for me to create a tutorial on my web site that documents all of the above and this additional technique.

[hainejo]

Thanks to everyone for your input. I'm sure to have it solved with all the options you have provided me.