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: