I have a spreadsheet that uses a button to create another button. It has worked for the past couple of years up until recently when a couple of systems were updated to Excel 16.16.1 (MacOS). This issue only started occurring on Excel for Mac. Something with the latest revision is breaking the VBA control that allows this to function.
Here's the code:
Sub new_button()
'Click this button to make more buttons
'Numb is an index that increases everytime the button is pressed
Dim numb As Integer
numb = Worksheets("Sheet1").Range("F2").Value
Dim r As Range
'These are the strings that exist on the spreadsheet
Dim verline As String
verline = "New Button Goes Here " & numb
For Each r In Range("D10:D29")
'This will search for the string then add the "New Button" button
If r.Value Like verline Then
With r.Parent.Buttons.Add(r.Left, r.Top, r.Width, r.Height)
.Font.Bold = True
.Caption = "New Button"
.OnAction = ""
End With
End If
Next r
'Increase the integer to move to the next instance
Worksheets("Sheet1").Range("F2").Value = numb + 1
End Sub
The code searches a range and places a button where ever it finds a certain string. After the latest update, this stopped working and results in the following error:
"Run-time error '-2147319765 (8002802b)': Automation error"
Attached is a spreadsheet stripped down to just this function, which results in the error message.
Please help! I wish I could get our office off of Mac, but they're designers and won't budge. :)
