Error msg pops out when a button is clicked more than once

Hello jlyh11,

Welcome to the Forum!

To better understand your problem, please post a copy of the workbook for review. It will save a lot time if you do.

Hi Leth,
I know the problem.
What I do is define Public variable isBusy as boolean
The routine wher you press the button should be some like this:

Sub Button_Click()
if isBusy = True then exit Sub
isBusy = True
the rest of you code goes here


isBusy=False
Exit Sub

Hope this gives you an idea

Hello Keebellah,
I tried to put this in together with the exisiting code but it didnt work. The error box still came out. Could you see if the order is correct?

Sub CC()
Application.DisplayAlerts = False
If isBusy = True Then Exit Sub
isBusy = True



Dim lngLastRow As Long, lngRow As Long
    
    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With
    
    lngLastRow = Range("A" & Rows.Count).End(xlUp).Row
    
    For lngRow = lngLastRow To 2 Step -1
        If Right(Range("B" & lngRow), 5) = "Total" Then Rows(lngRow).Delete
    Next lngRow

    For lngRow = lngLastRow To 2 Step -1
        If Right(Range("A" & lngRow), 5) = "Total" Then Rows(lngRow).Delete
    Next lngRow
    
    For lngRow = lngLastRow To 2 Step -1
         If Right(Range("B" & lngRow), 5) = "total" Then Rows(lngRow).Delete
    Next lngRow

    For lngRow = lngLastRow To 2 Step -1
        If Right(Range("A" & lngRow), 5) = "total" Then Rows(lngRow).Delete
    Next lngRow
    
    For lngRow = lngLastRow To 2 Step -1
        If Left(Range("B" & lngRow), 5) = "Total" Then Rows(lngRow).Delete
    Next lngRow

    For lngRow = lngLastRow To 2 Step -1
        If Left(Range("A" & lngRow), 5) = "Total" Then Rows(lngRow).Delete
    Next lngRow
    
    For lngRow = lngLastRow To 2 Step -1
         If Left(Range("B" & lngRow), 5) = "total" Then Rows(lngRow).Delete
    Next lngRow

    For lngRow = lngLastRow To 2 Step -1
        If Left(Range("A" & lngRow), 5) = "total" Then Rows(lngRow).Delete
    Next lngRow
    
    With Application
        .Calculation = xlCalculationAutomatic
        .ScreenUpdating = True
    End With
    
Lastrow = Range("E" & Rows.Count).End(xlUp).Row
Range("A7 : C" & Lastrow).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, SkipBlanks:=False, Transpose:=False
Selection.SpecialCells(xlCellTypeBlanks).Select
Application.DisplayAlerts = False
Selection.FormulaR1C1 = "=R[-1]C"

With Range("A7:C" & Lastrow)
    .Cells.Copy
    .Cells.PasteSpecial xlPasteValues
    .Cells(1).Select
End With
Application.CutCopyMode = False


With Range("A7:C" & Lastrow)
    .Cells.Copy
    .Cells.PasteSpecial xlPasteValues
    .Cells(1).Select
End With
Application.CutCopyMode = False


isBusy = False
Exit Sub

End Sub

Hello Keebellah,

I am waiting for the OP to respond before proposing any solutions. Assumptions made on a code fragment can waste a lot of time. Perhaps you have a lot of time but do not assume the same for the OP.

Originally Posted by Leith Ross

Hello Keebellah,

I am waiting for the OP to respond before proposing any solutions. Assumptions made on a code fragment can waste a lot of time. Perhaps you have a lot of time but do not assume the same for the OP.

Could you explain your "Perhaps you have a lot of time but ..."
The idea is not an assumption, it's reading what the OP says.
A button can be pressed many times and can trigger error. It was just an idea.
And also yes: I DO HAVE A LOT OF TIME that's the advantage of being and old and retired person

Without seeing your workbook the max I can suggest you to use one line of code in the beginning On Error Resume Next and see if the code doesn't produce any undesired output if it bypasses that error and gets executed again.

Or after looking at your code, it seems that you might get an error because of the following line if no blank cell is found

Selection.SpecialCells(xlCellTypeBlanks).Select

So to avoid that error you may replace the following lines of code

Selection.SpecialCells(xlCellTypeBlanks).Select
Application.DisplayAlerts = False
Selection.FormulaR1C1 = "=R[-1]C"

With this

On Error Resume Next
Selection.SpecialCells(xlCellTypeBlanks).Select
If Err = 0 Then
   Selection.FormulaR1C1 = "=R[-1]C"
End If
On Error GoTo 0

See if that resolves your issue.