[VBA] Strange behavior on rapid button press / code optimization

[Michu1]

Dear all,

I am new here, thanks for having me : )

I have below module code that inserts rows from a second (Formats) sheet on button click.
The rows are pre-format, including design and formulas.
Before the row or the multiple rows (depending on selection) are inserted, the macro copies the entire sheet into a backup sheet (to be able to undo the insertion of the row(s) ).
I have to use ActiveSheet, as I cannot predict what sheet name or number will be used by the user.
Please also assume that I have a primitive error handler (not shown here).

My issue is that I sometimes get the following error after multiple button presses:
"Excel cannot complete this task with available resources [...]"
I assume this means the clipboard is full (?!)
I have added Application.CutCopyMode = False which seems to delay but not prevent the occurrence of this error.
I also understand that Application.CutCopyMode = False does not actually empty the windows or office clipboard at all.

What seems to be the problem... well I have monitored the office clipboard (Home -> Clipboard)
Although nothing should be written into the clipboard and normally nothing is written into it,
if the button is pressed multiple times quickly/rapidly, suddenly there is code in the clipboard (seems to be the Backup is written into it).
I assume this is what is causing the error.
I also assume this happens because the code is somehow interrupted by the rapid button pressing or something like that.
I tried to disable the button for a few seconds to prevent rapid input, but I couldn't get that to work fully (at least not with Application.Wait, since that doesn't actually disable the button).

I also couldn't find anyone else who had the same problem as me (seemingly never related to the speed of button pressing)
I looked for code to empty the clipboard but I read that it depends on whether you have 32 or 64 bit system and I wouldn't want to make my sheet depended on that.

Any suggestions?
Code optimizations also always very welcome!

Option Explicit
 
Private Sub InsertRow_Click()
Dim xCount As Long
xCount = Selection.Rows.Count 'To count the rows selected and insert as many new rows as are
    
'Users are only allowed to add rows below the freeze pane (row 11) and where there are no cells with background color and the selection should never be the entire sheet therefore 

    If ActiveCell.Row < 11 Or ActiveCell.Interior.ColorIndex <> xlNone Or xCount = ActiveSheet.Rows.Count Then   
        MsgBox "Please chose a row in the work area."
    Else
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        Application.Calculation = xlCalculationManual
        Application.CutCopyMode = False 'This might not be needed but it is a remnant from me trying to clear the clipboard
        
        SaveBackup 'See following Subs

        'Depending on whether the cell background color in B2 is grey or empty a row format from the Formats sheet is inserted
            If Cells(Application.ActiveCell.Row, 2).Interior.Color = RGB(217, 217, 217) Then
                Sheets("Formats").Rows("2").EntireRow.Copy
                Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(xCount, 0)).EntireRow.Insert
            Else
                Sheets("Formats").Rows("1").EntireRow.Copy
                Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(xCount, 0)).EntireRow.Insert
            End If
                ActiveCell.EntireRow.Range("C1").Offset("1").Select

        Center_It 'See following Subs
        
        Application.CutCopyMode = False
        Application.EnableEvents = True
        Application.Calculation = xlCalculationAutomatic
        Application.ScreenUpdating = True
    End If
End Sub
 
Private Sub SaveBackup()

'Temporarily adds the sheet name for reference in B1 and then copies a backup of the active sheet into the Backup sheet before the rows are inserted

    ActiveSheet.Range("B1").Formula = "=MID(CELL(""filename"",A1),FIND(""]"",CELL(""filename"",A1))+1,255)"
    ActiveSheet.Range("B1").Value = ActiveSheet.Range("B1").Value
    ActiveSheet.Cells.Copy Destination:=Sheets("Backups").Cells
    ActiveSheet.Range("B1").Clear
End Sub
 
Private Sub Center_It()

'The purpose of this sub is to move the screen to where the active cell is

Dim i As Long
Dim j As Long
        Application.Goto reference:=ActiveCell, Scroll:=True
            With ActiveWindow
                i = .VisibleRange.Rows.Count / 2
                j = .VisibleRange.Columns.Count / 2
                ActiveWindow.SmallScroll Up:=i, ToLeft:=j
            End With
End Sub