Macro to automatically add macro-enabled buttons to a row as new rows are added from a linked Sharepoint list

[ExcelJockey18]

Hi everyone!

I've often perused this forum for the wisdom that exists here, and am finally reaching out for some specific help. First some background:

I'm building an Excel "tool" that is meant to track the status/progress of a "ticket." There is a SharePoint tool where new tickets are entered/created and old tickets are stored (a repository, basically), but it is mostly buggy and dysfunctional, so for the time being the only usable thing from the SharePoint tool is the dynamic list of ticket data. I am less familiar with SharePoint than I am with Excel, so I'm building this separate, [hopefully] temporary Excel tool to do some things that the SharePoint tool does not do right now.

Tab 1 of the Excel file is the linked SharePoint list, which, when refreshed, reflects the latest tickets. Tab 2 is a "tracker" I'm building, where each row represents a ticket in Tab 1, but is filtered to show only tickets with specific criteria. The first few columns in Tab 2 are linked to Tab 1, reflecting basic ticket info. Columns C through F represent the 4 stages of a ticket (acknowledged, handed off, received, closed). Each ticket row will have 4 buttons, one in each column (C through F) that, when clicked, will generate a timestamp, indicating when that stage occurred.

MY ASK
As the SharePoint list is refreshed, new tickets are added to Tab 1, and thus Tab 2. Is there a way to build a macro that allows the 4 buttons to be automatically copied/added to the new row whenever a new row is added to Tab 2 as a result of refreshing the list? And will the fact that the data in Tab 2 is filtered cause any complications of which I should be aware?

I appreciate your time and any help you can give me! Thank you!

 

[tonyyy]

ExcelJockey18,

Welcome to the Board.

The following might get you started...

Sub AddButtons_1060343()
Dim ws As Worksheet
Dim LastRow As Long
Dim btn As Button
Dim r As Range

Set ws = Sheets(2)
LastRow = ws.Cells(Rows.Count, "A").End(xlUp).Row
ws.Columns("C:F").ColumnWidth = 18

ws.Buttons.Delete
Application.ScreenUpdating = False
For Each r In ws.Range("C2:F" & LastRow)
    Set btn = ActiveSheet.Buttons.Add(r.Left, r.Top, r.Width / 5, r.Height)
    With btn
        .OnAction = "btnS"
        .Caption = ""
        .Name = r.Address(0, 0)
    End With
Next r
Application.ScreenUpdating = True
End Sub

Sub btnS()
Dim j As String
j = Application.Caller
ActiveSheet.Range(j).Value = Format(DateTime.Now, "mm/dd/yy hh:mm")
End Sub

The AddButtons macro will first delete all existing buttons, then add buttons in the visible cells in Columns C through F. The btnS macro will execute when a button is clicked, and add a timestamp to the button's cell.

Don't know how Tab2 is being refreshed so I can't say how to trigger the AddButtons macro.

Cheers,

tonyyy