Retrieve the name of the button from button array in Excel 2019 VBA Script

21 Views Asked by At

I could not able to retrieve the name of the button from button array in Excel 2019 VBA macro.

I am retrieving some records from the database table. I don't know how many records come from the database before fetching. Each record should have one "button" to update in the database table. So, I have created the buttons in EXCEL VBA script dynamically when retrieving the records and populated in the sheet.

This is the VBA macro code:

     ....
         If i > 0 Then
            If i = 1 Then
                Set btn = ActiveSheet.Buttons.Add(Left:=1000, Top:=ActiveSheet.Rows.RowHeight + 5, Width:=100, Height:=20)
            Else
                Set btn = ActiveSheet.Buttons.Add(Left:=1000, Top:=(i * ActiveSheet.Rows.RowHeight) + 5, Width:=100, Height:=20)
                'MsgBox ActiveSheet.TaskState.List(TaskState.ListIndex)
            End If
            btn.Text = "Update"
            btn.Name = "butn" & i
            btn.OnAction = "ServiceNowRestAPIQuery_UPDATE"
         End If
     next i
     ....


My Requirement: If the 1st button clicked I have to get the 1st row values (cell values), send them to DB and update the database table. if the 2nd button is clicked fetch the 2 row values (cell values), and so on...

Any help would be highly appreciated.

thank you all

0

There are 0 best solutions below