1

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.

Andrew
  • 39
  • 7
  • Are these activeX buttons? Have you tried hyperlinks to run code instead? I always use hyperlinks instead of buttons, but mainly to prevent the resizing errors – jamheadart Jun 19 '18 at 12:29
  • Yes, they are ActiveX buttons. What do you mean by resizing errors? Also, I have tried setting the macros to other shapes that weren't activeX buttons but they weren't able to call other subs so I didn't look into it any further. – Andrew Jun 19 '18 at 12:32
  • The resizing error is a horrible bug where you plug your laptop in to a 2nd screen and then activeX buttons on your Excel sheet just start increasing in size with each click (or the text on the button gets so small you can't see it) – jamheadart Jun 19 '18 at 12:33
  • I'll have to look into that, it sounds terrifying lol. Most of the workstations around here are laptops plugged into a dock which has two screens running from the dock, as well as standard workstations with dual screens. – Andrew Jun 19 '18 at 12:40
  • I didn't test it, but wouldn't it work to have a global (boolean) variable which you set at the beginning of the macro to true and after the macro is finished to false? and in the beginning of the macro you ask something like "If disalbed=true then End sub"? – Kajkrow Jun 19 '18 at 12:41
  • I'm surprised you haven't encountered the resize error if you're used to sharing screens from a laptop - although it may even be a bug they've fixed recently. I don't recommend it but if you really want to try seeing it, open a few workboks with ActiveX buttons on Worksheets and then unplug from screen, redock in another location, plug in to another screen etc a few times and you might see it - it's so annoying though, once it starts it never seems to stop. – jamheadart Jun 19 '18 at 12:44
  • I work on a proper desktop workstation and until now I've never been asked to create worksheets for anyone else, so this is my first foray into creating something for others to use. I don't have remote work but many others do so that's why they have laptops. This whole issue arose because I'm attempting to idiot-proof the worksheet so there isn't a way somebody could screw it up bad enough to be sending me emails about it. – Andrew Jun 19 '18 at 12:55
  • ActiveX buttons are horrible. That said, I've never seen anything like this without a `DoEvents` statement. But I also *never* use ActiveX controls, so maybe it's a quirk of those objects. Disable events, Disable screen updating, and/or set `Application.interactive = False` at the onset of each macro, remembering to set it to `True` at completion. – David Zemens Jun 19 '18 at 13:01
  • I will give this a try first since its least disruptive to the current worksheet's layout (aka avoiding userforms). Side question - what would you recommend to use as an alternative to activeX buttons? – Andrew Jun 19 '18 at 13:20

3 Answers3

1

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
jamheadart
  • 5,047
  • 4
  • 32
  • 63
  • I currently have 8 buttons, plus two option buttons that are currently being used to toggle the language of the worksheet. What is the difference with using a userform for the buttons? – Andrew Jun 19 '18 at 12:37
  • Just the fact you can disable the entire userform with one command, or actually hide the form so the buttons have gone completely (can't click what's not there!) – jamheadart Jun 19 '18 at 12:42
  • 1
    Also may I ask - do you have a "DoEvents" line somewhere in the code which is allowing the button clicks while a macro runs? – jamheadart Jun 19 '18 at 12:42
  • closing the userform with the buttons on it after a macro button is clicked sounds like it could work, though it does make every action 2 clicks instead of 1. Also, I don't have any DoEvents in the code. They are all relatively simple macros that take in total maybe 1 or 2 seconds tops to execute so I didn't consider adding any. – Andrew Jun 19 '18 at 12:51
  • You can get the form to hide automatically after a button click - see my amended answer – jamheadart Jun 19 '18 at 12:55
1

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.

Gary's Student
  • 95,722
  • 10
  • 59
  • 99
  • Thank you, this was the simplest way to get what I want without changing the way the buttons are presented. For some reason, anything like `Application.interactive = False` and `True` before/after the macros and `button.enabled = False` didn't prevent button spamming - but hiding the button seems to work. – Andrew Jun 21 '18 at 12:21
  • @Angelo Users are like chickens, they love to peck at things. This approach removes the temptation to "peck". – Gary's Student Jun 21 '18 at 12:23
1

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
JosephC
  • 917
  • 4
  • 12
  • 3
    Or use `Application.Cursor = xlWait` and `Application.Cursor = xlDefault` to set the cursor to an hourglass/spinning circle - *most* users are trained to understand that that means "stop doing stuff until the computer finishes" – Chronocidal Jun 19 '18 at 14:58
  • @Chronocidal I like the additional user feedback. Will update to the answer to include it. – JosephC Jun 19 '18 at 16:30