We found a problem with some content in XXXXX.xlsm. Do you want us to try to recover as mu

Hi I'm sorry, I'm a newbie with excel..

I get an error after closing and reopening the excel book. I tried to lookup the issue, I tried everything I could find.
I am able to compile and run the vba, but soon as I save it and reopenit, i get the message below.

"We found a problem with some content in "XXXXX.XLSM". do you want us tor try to recover as much as we can? if you trust the source of the workbook. click yes."

I've changed the Trust centre settings for the ActiveX Settings / Macro Settings / Trusted Locations. saved as an excel 95 file and recreated as an xlsm file. verifying any references.
I'm trying to adding an activeX Command button (I even tried to put a Form Control button) to the excel sheet.
I've even tried renaming the sheets.

Capture.PNG

In the attached image, after I say yes to the error, excel creates a new "Sheet1" object and moves the original "Sheet1" object to a new sequence number, in the image above, the original sheet was renumbered to "Sheet3"..

Any help would be greatly appreciated

here is the code for the button click event, it's simply copying and pasting columns to new area (there is a data validation list that i'm getting form another page).

Private Sub Add_Column_Click()

Dim sht As Worksheet
Dim LastRow As Long
Dim LastColumn As Long
Dim StartCell As Range
Dim Lrow As Single
Dim AStr As String
Dim Value As Variant


Dim lCol As Long
    
    lCol = Cells(6, Columns.Count).End(xlToLeft).Column
    Set sht = Worksheets("Tally Sheet")
      
    'Dim x As Integer
      
     If iColumn < 14 Then
        iColumn = 14
    Else
        iColumn = iColumn + 2
    End If
   
    Set StartCell = Range(Cells(6, iColumn).Address(RowAbsolute:=False, ColumnAbsolute:=False)) '
    'Find Last Row and Column
   ' LastRow = sht.Cells(sht.Rows.Count, StartCell.Column).End(xlUp).Row
   ' LastColumn = sht.Cells(StartCell.Row, sht.Columns.Count).End(xlToLeft).Column

    ' Copy fields
    Worksheets("Tally Sheet").Range(Cells(6, iColumn), Cells(26, iColumn + 1)).Copy _
    Destination:=Worksheets("Tally Sheet").Range(Cells(6, iColumn + 2).Address(RowAbsolute:=False, ColumnAbsolute:=False))

    ' Clear existing contents that were copied
    Worksheets("Tally Sheet").Range(Cells(6, iColumn + 2), Cells(24, iColumn + 2)).ClearContents
   
   ' Hide calculation fields
    Range(Cells(1, iColumn + 3).Address(RowAbsolute:=False, ColumnAbsolute:=False)).EntireColumn.Hidden = True

    ' Populate Drop Down list
    Lrow = Worksheets("Item").Range("A" & Rows.Count).End(xlUp).Row

    For Each Value In Worksheets("Item").Range("A2:A" & Lrow)
        AStr = AStr & "," & Value
    Next Value

    AStr = Right(AStr, Len(AStr) - 1)

    With Worksheets("Tally Sheet").Range(Cells(6, iColumn + 2), Cells(6, iColumn + 2)).Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=AStr
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With

End Sub

Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #3 requires code tags. I have added them for you this time because you are a new member. --6StringJazzer