CODEWITHSUNDEEP
excelvbabuttonuserform
steventnorris
steventnorris

Reputation: 5896

UserForm Button Still Functioning When Disabled

I am disabling a button onclick, but it is still allowing the click.

Code is like below:

UsrForm.Field1.Value = ""
UsrForm.Field2.Value = ""
UsrForm.btn.Enabled = False
UsrForm.Repaint

/*Processing Occurs*/

UsrForm.Field1.Value = val1
UsrForm.Field2.Value = val2
UsrForm.btn.Enabled = True

However, if I double click or click a few times where the disabled button is, it still runs the method several times, despite being disabled.

Upvotes: 1

Views: 523

Answers (2)

Jzz
Jzz

Reputation: 739

I think we have a proper bug here. The solution posted by S Meaden does not work (at least, not in my testing). Here's what I trew together for testing:

Private Sub CommandButton1_Click()
Dim w As Date

Me.CommandButton1.Enabled = False

w = Now + TimeSerial(0, 0, 2)

Debug.Print "point 1: " & Now

Application.Wait w

Debug.Print "point 2: " & Now

Me.CommandButton1.Enabled = True

End Sub

Clicking it makes it gray out (as it should when disabling) and run the routine. Clicking twice however runs the routine twice. Because it prints the times, it is clear that the routines run in sequence, so it seams that excel (in my case excel, haven't tested with other applications) remembers the clicks, and when the routine finishes (and the button is enabled again) the routine is called. It runs 3 or 4 times in a row as well.

Because of this, implementing S Meaden's answer, like so:

Dim clicked as Boolean
Private Sub CommandButton1_Click()
Dim w As Date

If Not clicked Then
    clicked = True
    Me.CommandButton1.Enabled = False
    w = Now + TimeSerial(0, 0, 2)
    Debug.Print Now
    Application.Wait w
    Debug.Print "punt 2 (" & Now & ")"

    Me.CommandButton1.Enabled = True
    clicked = False
End If

End Sub

does not work either.

It seems that if the button is enabled after the routine is finished, the clicks that were placed during routine execution are discarded. So as a workaround, you could use:

Private Sub CommandButton1_Click()
Dim w As Date

Me.CommandButton1.Enabled = False

w = Now + TimeSerial(0, 0, 2)

Debug.Print "point 1: " & Now

Application.Wait w

Debug.Print "point 2: " & Now
Me.Button1_clicked = False

Application.OnTime (Now + 0.000001), "enable_commandbutton"

End Sub

with "enable_commandbutton" being:

Public Sub enable_commandbutton()
Dim uf As Object
Debug.Print "check"
For Each uf In VBA.UserForms
    If uf.Name = "UserForm1" Then
        uf.CommandButton1.Enabled = True
    End If 
Next uf


End Sub

in a normal codemodule.

It is not pretty, but it works.

Upvotes: 1

S Meaden
S Meaden

Reputation: 8260

That's interesting. I agree your code should work and I am puzzled by that. However, I'm the sort of guy who would code around and so here is some that uses a module level variable to keep note of whether the procedure is already running.

Option Explicit

Private mbAlreadyProcessing As Boolean

Private Sub btn_Click()
    On Error GoTo ErrHandler
    If Not mbAlreadyProcessing Then
        mbAlreadyProcessing = True
        'do some work
        mbAlreadyProcessing = False
    End If
    Exit Sub
ErrHandler:
    'here we remember to "re-enable"
    mbAlreadyProcessing = False
    'do some error handling

End Sub

Upvotes: 1

Related Questions