CODEWITHSUNDEEP
excelvba
Mike D
Mike D

Reputation: 2841

disabling buttons or other controls during processing

I have some button controls which fire off lengthy processing. Do I have to worry about the user pressing the button and firing off another execution while a first execution is active?

I found I could use the click events to disable the button, call the processing routine, and then enable the button. But this seems fraught with danger if the processing routine fails.

I could add on error stuff in the click routine to catch failures but would any on error calls in the processing routine cancel those out?

Upvotes: 1

Views: 299

Answers (4)

Mike D
Mike D

Reputation: 2841

I decided to disable the button in its click handler, then re-enable it after calling the processing routine.

Private Sub CommandButton2_Click()
    Dim s As Integer
    s = CommandButton2.Enabled
    If s Then
        On Error GoTo Fail
        CommandButton2.Enabled = False
        GetData
Fail:
        CommandButton2.Enabled = True
    End If
End Sub

I also find this more pleasing than removing the default embedded formula =EMBED("Forms.CommandButton.1","") and attaching a macro.

Upvotes: 1

Doug Glancy
Doug Glancy

Reputation: 27488

I get the opposite result as enderland, with a button on a form or on a sheet (button from the Forms menu). I set a static variable that will increment each time the button is pressed, and if I press it while the code is running, it runs and increments again. So I'd say yes, you need to set a global variable to handle this:

Sub CommandButton1_Click()
Static Incrementer As Long
Dim i As Long

Incrementer = Incrementer + 1
For i = 1 To 1000
Debug.Print Incrementer
Next i
End Sub

Upvotes: 0

Tim Williams
Tim Williams

Reputation: 166885

From experience I've found it's safer to expect that you might see multiple parallel executions, and plan to avoid them. Try this with and without the marked line and clicking the button repeatedly:

Sub CommandButton1_Click()

    Static InProgress As Boolean

    If InProgress Then Exit Sub  'try commenting out...

    InProgress = True

    Debug.Print "starting...."
    DoProcessing
    Debug.Print "....Done"

    InProgress = False

End Sub


Sub DoProcessing()
    Dim x As Long, v

    For x = 1 To 1000
        ActiveSheet.Cells(x, 1).Copy ActiveSheet.Cells(x, 2)
        DoEvents
    Next x

End Sub

This specific behavior is caused by the DoEvents call - without this you should not see parallel runs, as enderland noted.

Upvotes: 2

enderland
enderland

Reputation: 14185

Do I have to worry about the user pressing the button and firing off another execution while a first execution is active?

No - as long as there's no chance there's any code in your processing which calls DoEvents.

Try assigning the following code to a button in your worksheet (you may need to adjust the iterators higher to get sufficient runtime) and clicking an additional button - or doing anything else in Excel for that matter.

Sub test()
    For i = 1 To 100
        For j = 1 To 100
            Range("A1").Select
        Next j

    Next i
End Sub

An added bonus is it helps illustrate how .select causes slower operations in working with Excel...

I found I could use the click events to disable the button, call the processing routine, and then enable the button. But this seems fraught with danger if the processing routine fails.

It is probably a good idea to include some sort of error handling regardless of simply reenabling buttons for any meaningful operations unless you are the sole user.

I could add on error stuff in the click routine to catch failures but would any on error calls in the processing routine cancel those out?

You would have to properly account for errors and the different places. The answer to this is "it depends on your implementation."

Upvotes: 0

Related Questions