is there a way to allign a form button to a cell and assign another sub to it

Question

Question has been solved how to make multiple buttons reference different adjacent offset cells

Sub Tester() Dim btn As Object Dim rng As Range Dim i As Long Dim lastRow As Long

lastRow = 99999 'Modify as needed this will be the last possible row to add a button

For i = 2 To lastRow Step 4
    Set rng = ActiveSheet.Cells(i, 21)  'Column 2, row i

    '## Create the button object and assign a variable to represent it
    Set btn = ActiveSheet.Buttons.Add(1, 1, 1, 1)

    '## use the btn variable to manipulate the button:
    With btn
        .Top = rng.Top
        .Left = rng.Left
        .width = rng.width
        .height = rng.RowHeight
        .OnAction = "offsetRelative"
        .Caption = "Close"
    End With
Next
End Sub

Sub offsetrelative()
Dim rowNumber As Long
'## Get the row number of the button:
rowNumber = ActiveSheet.Shapes(Application.Caller).TopLeftCell.Row
Application.ScreenUpdating = False
 Sheets("Sheet6").Range("AD" & CStr(rowNumber)).Value = 1

 Application.ScreenUpdating = True
End Sub

the above solution works great but is a little slow to calculate whenever I click a button. I need it to be instant but it hangs for 5 seconds...is there any way to avoid this?

Answer 1

You're on the right track. I'm going to use the first line from your recorded macro, and integrate with the Tester attempt you have made. Then put it in a loop to create as many buttons as you want.

Sub Tester()
    Dim btn as Object
    Dim rng As Range
    Dim i as Long
    Dim lastRow As Long

    lastRow = 100 'Modify as needed this will be the last possible row to add a button

    For i = 3 to lastRow Step 4 
        Set rng = ActiveSheet.Cells(i, 2)  'Column 2, row i

        '## Create the button object and assign a variable to represent it
        Set btn = ActiveSheet.Buttons.Add(1, 1, 1, 1)

        '## use the btn variable to manipulate the button:
        With btn
            .Top = rng.Top
            .Left = rng.Left
            .Width = rng.Width
            .Height = rng.RowHeight
            .onAction = "offsetRelative"
        End With
    Next
End Sub

I modify your offsetRelative function to use the Application.Caller to determine the cell/range where the button resides, and get the row number from that. Then, instead of hard-coding in the range like "AE3", we can combine the column "AE" with the row number:

Sub offsetrelative()
Dim rowNumber as Long
'## Get the row number of the button:
rowNumber = ActiveSheet.Shapes(Application.Caller).TopLeftCell.Row
    Application.ScreenUpdating = False
     Sheets("sheet2").Range("AE" & Cstr(rowNumber)).Value = 1
     Application.Wait (Now + 0.000001)
     Sheets("sheet2").Range("AE" & Cstr(rowNumber)).Value = 0
     Application.ScreenUpdating = True
End Sub

Answer 2

You seem to already know that Buttons.Add() returns the new button and can be stored in a variable and used wherever it's getting Shapes("Button 24"), but I thought I should mention it for completeness.

For doing a different action from each button, you can assign them all the same subroutine, and write a single subroutine that uses ActiveSheet.Buttons(Application.Caller).TopLeftCell to determine the cell that the button is in, and decides what to do based on the address of the cell.

You can address cells relatively from the known cell with the .Offset(x, y) property or e.g. .Row instead of using Sheet.Range, to avoid having to dynamically calculate ranges, if every section the buttons are in is laid out the same way.

Sub offsetrelative()
    Dim myCell as Range
    Dim myRow as Long 
    myCell = ActiveSheet.Buttons(Application.Caller).TopLeftCell 
    myRow = myCell.Row - 2 ' D9 = 7
    Application.ScreenUpdating = False
    Sheets("sheet2").Range("AE" & CStr(row)).Value = 1
    Application.Wait (Now + 0.000001)
    Sheets("sheet2").Range("AE" & CStr(row)).Value = 0
    Application.ScreenUpdating = True
End Sub

Another thing you could try is encoding the row number in the button name. So you can set .Name = "Button_Row" & CStr(i) when you create the button, and then don't use myCell at all and use myRow = CLng(Mid(Application.Caller, 11)) in the offsetrelative function. The 11 is the first character position of the number, it depends on the length of your prefix which was "Button_Row" [10 characters long] in this example.