Active X buttons only work once

[matrix26]

Hi,
I've created a sheet that when data is posted the Active X buttons, when pressed, will do 1 on the following 3 actions
1 Clear Data - Clear all data from worksheet
2. Tidy Up - rearranges data in to a table
3. Create Email - Copies the data in the table and inserts it in to an email

The strange thing is that once I've used 1 of the 3 buttons they no longer work.
All that happens is that the button being pressed replicates itself for the duration of the button press.
I'm not in developer mode
I can't lock the worksheet or the buttons don't work

Where am I going wrong?
Here's my code
Thanks

Option Explicit
Private Sub CommandButton1_Click()

Cells.Select
    Selection.Delete Shift:=xlUp
    Range("A1").Select
 
End Sub
Private Sub CommandButton2_Click()

Range("A2").Select
    Rows("2:2").RowHeight = 53.25
    Columns("B:B").ColumnWidth = 24.14
    Columns("B:B").ColumnWidth = 29.57
    Columns("A:A").ColumnWidth = 22.57
    Columns("C:C").ColumnWidth = 14.86
    Columns("D:D").ColumnWidth = 15.14
    Columns("F:F").ColumnWidth = 21.29
    Columns("G:G").ColumnWidth = 13
    Columns("H:H").ColumnWidth = 16.57
    Columns("K:K").ColumnWidth = 13.29
    Rows("3:3").Select
    Selection.RowHeight = 36
    Selection.RowHeight = 76.5
    Selection.RowHeight = 108.75
    Selection.RowHeight = 156
    Selection.RowHeight = 194.25
    Selection.RowHeight = 217.5
    Columns("B:B").ColumnWidth = 34.29
    Selection.RowHeight = 205.5
    ActiveWindow.SmallScroll Down:=0
    Rows("4:4").RowHeight = 88.5
    Rows("4:4").RowHeight = 119.25
    ActiveWindow.SmallScroll Down:=3
    Rows("5:5").RowHeight = 59.25
    Rows("5:5").RowHeight = 92.25
    Rows("5:5").RowHeight = 117.75
    Rows("6:6").RowHeight = 43.5
    Rows("6:6").RowHeight = 71.25
    Rows("6:6").RowHeight = 105
    ActiveWindow.SmallScroll Down:=2
    Rows("7:7").RowHeight = 67.5
    Rows("7:7").RowHeight = 75.75
    Rows("8:8").RowHeight = 65.25
    Rows("8:8").RowHeight = 87.75
    Rows("9:9").RowHeight = 60.75
    Rows("9:9").RowHeight = 75.75
    ActiveWindow.SmallScroll Down:=3
    Rows("10:10").RowHeight = 52.5
    Rows("10:10").RowHeight = 93
    Rows("11:11").RowHeight = 71.25
    Rows("11:11").RowHeight = 117.75
    Rows("12:12").RowHeight = 68.25
    Rows("12:12").RowHeight = 99.75
    ActiveWindow.SmallScroll Down:=3
    Rows("13:13").RowHeight = 76.5
    Rows("13:13").RowHeight = 106.5
    Rows("14:14").RowHeight = 68.25
    Rows("14:14").RowHeight = 75.75
    ActiveWindow.SmallScroll Down:=-42
    Columns("D:D").Select
    Selection.Delete Shift:=xlToLeft
    Columns("G:G").Select
    Selection.Delete Shift:=xlToLeft
    Columns("J:J").Select
    Selection.Delete Shift:=xlToLeft
    Range("A1:I14").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Application.CutCopyMode = False
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$I$14"), , xlYes).Name = _
        "Table1"
    Range("Table1[#All]").Select
    ActiveWindow.SmallScroll Down:=-39
    ActiveWorkbook.Worksheets("NIGHTSHIFT EMAIL").ListObjects("Table1").Sort. _
        SortFields.Clear
    ActiveWorkbook.Worksheets("NIGHTSHIFT EMAIL").ListObjects("Table1").Sort. _
        SortFields.Add2 Key:=Range("Table1[Start Time]"), SortOn:=xlSortOnValues, _
        Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("NIGHTSHIFT EMAIL").ListObjects("Table1").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Rows("2:2").RowHeight = 72
    Rows("2:2").RowHeight = 72.75
    Rows("3:3").RowHeight = 113.25
    Rows("3:3").RowHeight = 75.75
    ActiveWindow.SmallScroll Down:=3
    Rows("5:5").RowHeight = 161.25
    Rows("5:5").RowHeight = 178.5
    ActiveWindow.SmallScroll Down:=-21
 
End Sub
Private Sub CommandButton3_Click()

    Dim oLookApp As Outlook.Application
    Dim oLookItm As Outlook.MailItem
    Dim oLookIns As Outlook.Inspector
    Dim oWrdDoc As Word.Document
    Dim oWrdRng As Word.Range
    Dim oWrdTbl As Word.Table
    Dim ExcTbl As ListObject
    On Error Resume Next
    Set oLookApp = GetObject(, "Outlook.Application")
    If Err.Number = 429 Then
    Err.Clear
    Set oLookApp = New Outlook.Application
    End If
    Set oLookItm = oLookApp.CreateItem(olMailItem)
    Set ExcTbl = Sheet1.ListObjects(1)
    With oLookItm
    .To = "xxxxxxxxxxxx.com;xxxxxxxxxxxxx.com"
    .Subject = "NIGHTSHIFT WORKLOAD"
    .Body = "Hi" & vbNewLine & vbNewLine & _
            "**New LWI for 1K/4K devices**" & vbNewLine & _
            "Every time you do a 1K or 4K device you MUST follow this LWI for each device:" & vbNewLine & _
            "XXXXXXXXXXXX" & vbNewLine & vbNewLine & _
            "**All ASR Devices to be done 1 at a time.**" & vbNewLine & _
            "Ensure before you reload that you have entered the below config as part of your saved PRE CHECKS." & vbNewLine & _
          "Failure to complete will result in a loss of device management." & vbNewLine & _
          "(Please note that the exec command does not show in running config if you check for it)" & vbNewLine & vbNewLine & _
          "conf t" & vbNewLine & _
          "line vty 0 4" & vbNewLine & _
          "exec-timeout 5 0" & vbNewLine & _
          "exec" & vbNewLine & _
          "end" & vbNewLine & _
          "wr mem" & vbNewLine & vbNewLine & _
          "**For notification emails use the correct drop down list on the Change Checklist Form**" & vbNewLine & vbNewLine & _
          "Please make sure you MD5 check IOS prior to reloading devices." & vbNewLine & vbNewLine & _
          "SHOULD ANY 4K \ 1K DEVICE REQUIRE A ROLL BACK PLEASE ENSURE YOU FOLLOW THE LWI." & vbNewLine & _
          "FAILURE TO DO SO WILL RESULT IN SSH \ IPSEC BEING REMOVED FROM THE DEVICE."
    .Display
    Set oLookIns = .GetInspector
    Set oWrdDoc = oLookIns.WordEditor
    ExcTbl.Range.Copy
    Set oWrdRng = oWrdDoc.Application.ActiveDocument.Content
    oWrdRng.Collapse Direction:=wdCollapseEnd
    Set oWrdRng = oWdEditor.Paragraphs.Add
    oWrdRng.InsertBreak
    oWrdRng.PasteExcelTable LinkedToExcel:=True, WordFormatting:=True, RTF:=True
    Set oWrdTbl = oWrdDoc.Tables(oWrdDoc.Tables.Count)
    oWrdTbl.AllowAutoFit = True
    oWrdTbl.AutoFitBehavior (wdAutoFitWindow)
   
    End With
  
End Sub

 

[matrix26]

I found a similar post that said ActiveSheet.Buttons.Add was causing the issue, but I don't have that line of code

 

[RoryA]

Do you have a particular reason for using ActiveX buttons rather than Form ones, or shapes? It's best to avoid activex if possible.

 

[matrix26]

Do you have a particular reason for using ActiveX buttons rather than Form ones, or shapes? It's best to avoid activex if possible.

Thanks for the reply.

I tried Form Buttons but, despite having them marked as "Do not move or size with cells" they still move.
I was about to try assigning a macro to a hyperlink to see if that works.

 

[RoryA]

Rather than deleting every cell on the worksheet, I'd suggest you just clear them:

Private Sub CommandButton1_Click()

Usedrange.Clear
    Range("A1").Select
 
End Sub

 

[matrix26]

Rather than deleting every cell on the worksheet, I'd suggest you just clear them:

Private Sub CommandButton1_Click()

Usedrange.Clear
    Range("A1").Select

End Sub

Thanks,
I'll update the code