CODEWITHSUNDEEP
vbaexcelerror-handlingruntime-error
Andrew
Andrew

Reputation: 39

excel vba - Is there a way to prevent macro button spamming?

I have a sheet with several macro buttons that all do slightly different things and all is well, unless the user spam clicks different buttons somewhat quickly.

I'm not experienced enough to know if this is a common issue or something with the way my code works, but it seems that everything works correctly if a macro button is clicked and properly executes before another is clicked. Even spam clicking the same macro button seems to work well, but the moment two or more are clicked in rapid succession, the code doesn't execute properly.

The code itself just finds cells with particular values, and adds or deletes rows and sometimes copies and pastes from other areas of the sheet. For example, if you were to click button X to find part X of the worksheet and add a row below it, and spam click button Y to find part Y of the worksheet and delete a row above it, button Y will fail at finding the cell it needs to find (even though it exists) and trigger an error messagebox telling the user that part Y seems to be missing, and meanwhile button X will have some weird issues itself like deleting the wrong row as if it anticipated/compensated for what it expected Y to do.

I tried using CommandButton1.Enabled = False for every command button at the start of macro and enabling the buttons again at the end of every macro, but I can still spam click the buttons regardless during the macro execution.

Is there an easy way to prevent other macro buttons from being clicked until the current macro code has run? I've tried disabling and enabling the buttons, I've tried adding wait times. If I can't solve this issue I will likely resort to saving a duplicate hidden worksheet that, upon error, is unhidden, and the problem worksheet deleted, another 'backup' copy of the duplicate is created and hidden, all the while the user is notified not to spam the macro buttons.

Upvotes: 1

Views: 2486

Answers (3)

JosephC
JosephC

Reputation: 929

Add a check at the start of each button to a global variable. Only run the button's code if no other button is running at the same time.

In case the code is taking a longer time to run I've added icon changes to show that the program is busy while code is being run, as suggested by Chronocidal.

Public Running As Boolean

Private Sub CommandButton1_Click()
If Running = False Then
    Running = True
    Application.Cursor = xlWait        

    ' Your Buttons Code

    Running = False
    Application.Cursor = xlDefault
Else ' (Optional)
    MsgBox ("I'm busy, try again later!")
End If

End Sub

Upvotes: 1

Gary's Student
Gary's Student

Reputation: 96771

This is based on the observation that:

What you cannot see, you cannot click.

All buttons whether they are forms buttons or ActiveX buttons or AutoShapes are Shapes. All Shapes are either Visible or not Visible.

If a Shape is not Visible, it cannot be clicked. Therefore at the beginning of each button sub include a call to:

Sub HideAllButtons()
    Dim s As Shape
    For Each s In ActiveSheet.Shapes
        s.Visible = False
    Next s
End Sub

and at the end of the button code include a call to:

Sub ShowAllButtons()
    Dim s As Shape
    For Each s In ActiveSheet.Shapes
        s.Visible = True
    Next s
End Sub

Thus while any button is running, the user is "locked out" from clicking any other button.

Upvotes: 1

jamheadart
jamheadart

Reputation: 5343

I recommend using a UserForm to host your activeX controls, or if you just have one or two buttons, replace them with hyperlinks on the sheet and use the following code in a worksheet.followhyperlink event to check which hyperlink was clicked:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    If Target.Range.Address(False, False) = "A1" Then MsgBox ("Woah I clicked cell A1!")
End Sub

If you have a userForm with buttons on, then you can make each button click hide the form until the macro is complete automatically:

Private Sub CommandButton1_Click()
UserForm1.Hide
    ' Do the macro stuff
UserForm1.Show
End Sub

Upvotes: 1

Related Questions