Active X buttons only work once

matrix26

New Member
Joined
Dec 16, 2020
Messages
30
Office Version
  1. 2016
Platform
  1. Windows
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
VBA Code:
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
 
Last edited by a moderator:

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

matrix26

New Member
Joined
Dec 16, 2020
Messages
30
Office Version
  1. 2016
Platform
  1. Windows
I found a similar post that said ActiveSheet.Buttons.Add was causing the issue, but I don't have that line of code
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,365
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

New Member
Joined
Dec 16, 2020
Messages
30
Office Version
  1. 2016
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,365
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Rather than deleting every cell on the worksheet, I'd suggest you just clear them:

Code:
Private Sub CommandButton1_Click()

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

matrix26

New Member
Joined
Dec 16, 2020
Messages
30
Office Version
  1. 2016
Platform
  1. Windows
Rather than deleting every cell on the worksheet, I'd suggest you just clear them:

Code:
Private Sub CommandButton1_Click()

Usedrange.Clear
    Range("A1").Select

End Sub
Thanks,
I'll update the code
 

Forum statistics

Threads
1,141,715
Messages
5,708,030
Members
421,540
Latest member
quocbinh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top