Issues creating Command Button through Macro

Question

I created a macro that will search for a certain text in a column and create a button when it finds that text. This worked without issue until we upgraded to Office 2016 for Mac.

Now I'm getting the error "Run-time error '1004': Unable to set the Caption property of the Button class"

Here's the code that it's getting stuck on, it errors out at the ".Caption" line:

For Each r In Range("T:T") 'This will add the "New Version" 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 Version" .OnAction = "new_vergraph" & numb End With End If Next r

Any help would be greatly appreciated! I'm racking my brain trying to figure out the difference in syntax.

Answer 1

I figured out the problem, interestingly enough.  It all had to do with the top row being frozen.  Un freezing the top row makes everything work, no idea why, but it appears to be some sort of bug in 2016 for Mac.  So for now, it's easier to deal with the annoyance of scrolling up a spreadsheet than it not working at all!

Answer 2

Hi Jim,

Thanks for the reply!  Yes it happens on all of our Macs with Excel 2016 installed. Excel is fairly up to date. Version 15.31 (170216) is what we have installed.

I just tried running only this portion of the macro in a new spreadsheet and it worked. I'm not sure why the context of the rest of my macro is causing this to fail, it's quite baffling as there's really nothing that comes before this portion besides a copy / paste that sets up the text that the Buttons.Add is looking for.

Here's the entire code from my macro that fails every time at the first ".Caption" section:

Sub new_spot()

    'Go to the Template sheet and copy rows 1-9

    With Worksheets("Template")

        .Visible = False

        .Rows("2:12").Copy

    End With

    'Variables

    Dim numb As Integer

    numb = Worksheets("Template").Range("B15").Value

    'Check to see if there is already 100 graphics, and end if there is

    If numb > 100 Then

        MsgBox "You have exceeded the number of allowable graphics. Please start a new tracker. :)"

        End

    End If

    'Select the GFX sheet

    Sheets("GFX").Select

    'Go to the last cell in row B and start 2 from the last blank cell

    Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Select

    'This pastes the sections from the template to the GFX sheet

    Rows(Selection.Row).PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _

        , SkipBlanks:=False, Transpose:=False

        'Variables

        Dim r As Range

        Dim verline As String

        verline = "ver_line_" & numb

        Dim newasset As String

        newasset = "new_asset_" & numb

        Dim addimg As String

        addimg = "add_img_" & numb

        Dim newgraph As String

        newgraph = "new_graph_" & numb

        For Each r In Range("T:T")

        'This will add the "New Version" 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 Version"

                    .OnAction = "new_version" & numb

                End With

            End If

        Next r

        'This will add the "New Asset" button

        For Each r In Range("T:T")

            If r.Value Like newasset Then

                With r.Parent.Buttons.Add(r.Left, r.Top, r.Width, r.Height)

                    .Font.Bold = True

                    .Caption = "New Asset"

                    .OnAction = "new_asset" & numb

                End With

            End If

        Next r

        For Each r In Range("T:T")

        'This will add the "New Graphic" button

            If r.Value Like newgraph Then

                With r.Parent.Buttons.Add(r.Left, r.Top, r.Width, r.Height)

                    .Font.Bold = True

                    .Caption = "New Graphic"

                    .OnAction = "new_graph" & numb

                End With

            End If

        Next r

    'Then we need to go back to the template and increase the integer by one on all the variables, so the macro knows to use the next sequence

    Worksheets("Template").Range("B15").Value = numb + 1

    numb = Worksheets("Template").Range("B15").Value

    Worksheets("Template").Range("T7").Value = "ver_line_" & numb

    Worksheets("Template").Range("T9").Value = "new_asset_" & numb

    Worksheets("Template").Range("T12").Value = "new_graph_" & numb

    Worksheets("Template").Range("A12").Value = "last_line_" & numb

End Sub

Answer 3

Hi Jander78,

Does the issue happen to multiple files or machines which had installed Excel 2016 for Mac?

In addition, could you share the version of your Excel? Is it up to date?

Regards,

Jim