Assign Macro with Multiple Arguments to Button VBA

Question

I am working on a VBA macro which I would like to add buttons to each row. I would like to assign the same macro to each button, with different arguments on each row. The macro the button runs is a simple one which generates an email from information on the row the button is on and saves it to the user's outlook drafts folder.

The issue I am having is that when I have the email code classified as a function, it immediately runs through instead of assigning it to the button. When I have it classified as a sub, I receive a compile error which states 'Compile error: Expected Function or variable'

The code for the main macro follows:

Sub addButtons()
    Dim lastCol As Integer
    Dim lastRow As Integer
    Dim r As Range
    Dim btn As Button
    Dim uid As String
    Dim rDate As String
    Dim i As Integer

    lastCol = Cells(1, Columns.Count).End(xlToLeft).Column + 1
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    rDate = Str(Date)
    rDate = Replace(rDate, "/", "D")

    For i = 2 To lastRow
        Set r = ActiveSheet.Range(Cells(i, lastCol), Cells(i, lastCol))
        Set btn = ActiveSheet.Buttons.Add(r.Left, r.Top, r.Width, r.Height)
        uid = ActiveSheet.Cells(i, 1).Text
        With btn
            .OnAction = newhireEmail(i, rDate)
            .Caption = "Email " & uid & "?"
            .name = "btn" & (i - 1)
        End With
    Next i

    r.EntireColumn.ColumnWidth = 15
End Sub

I can post the code for the email as well, but I don't believe it should be relevant as I would ideally like that code to not be run at all unless the button is being clicked.

Thank you for your time!

I've recommended this in the past, but instead of using form buttons, I sometimes prefer to use cells that are formatted to look like buttons and capture the `Selection_Change` event of the worksheet. Then you can have a single macro that uses the `Target` to grab the particular row you're on. — user1274820, Jan 18 '18 at 20:21
See previous answer: https://stackoverflow.com/a/41026906/1274820 — user1274820, Jan 18 '18 at 20:21
Isn't OnAction supposed to be set to a string? See https://stackoverflow.com/questions/25097756/vba-pass-arguments-with-onaction — Darryl, Jan 18 '18 at 20:26
I didn't say it in the previous answer, but you can also use `Application.Caller` to get the name of the button and have each of your buttons point to the same macro - it's cleaner than creating a new sub for each button. — user1274820, Jan 18 '18 at 20:27
Just to rethink your project, as mentioned before I would have one button for the email and use a cell in the row for selection. 1st comment is right because too many buttons in the rows will be a file size and screen refresh monster. It's not that you can't do it, it is that you shouldn't do it. Make a selection and then act on that target using re-usable code. — Wookies-Will-Code, Jan 18 '18 at 20:47

score 0 · Answer 1 · answered Jan 18 '18 at 21:42

This may not be an answer to your question as posed, but I do want to present a couple of different ways of tackling this task.

One of the more lightweight ways of doing it is to subscribe to an event that the worksheet extends (and that you don't otherwise need). For example, when I had a surprisingly similar process, I subscribed to the worksheet double click event. When I Double Clicked on a cell, it compiled information from that row, and passed it to an email subroutine.

If you want a more visual clue, but you don't want the over head of a button, consider using Hyperlinks. This is a two step process, but it when I've done this, it has worked more quickly and more efficiently than a ton of buttons. (NOTE: you are limited to 65,530 hyperlinks per worksheet. If you need more than that, this won't work).

First, generate the Hyperlinks:

Sub AddHyperlinks()
    Dim lastCol As Long
    Dim lastRow As Long
    Dim s As Worksheet

    Set s = ActiveSheet


    lastCol = s.Cells(1, Columns.Count).End(xlToLeft).Column + 1
    lastRow = s.Cells(s.Rows.Count, 1).End(xlUp).Row

    For i = 2 To lastRow
        s.Hyperlinks.Add Anchor:=s.Cells(i, lastCol), _
        Address:="", _
        SubAddress:=s.Cells(i, lastCol).Address, _
        TextToDisplay:="Send Email"
    Next i
End Sub

Next, subscribe to the FollowHyperlinks event in the appropriate worksheet, and generate your code there. The Hyperlink Target will have a reference to the cell address of the hyperlink, from which you can get any needed data:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    'Submit whatever code you want here
    MsgBox Target.SubAddress
End Sub

If I were to use this, will it affect other hyperlinks on the sheet? I am in need of a clearly visible marker to begin the email process, so I can't just use double clicking, but the sheet in question already has a hyperlink on each row. — Cameron, Jan 18 '18 at 21:54
@Cameron That is a trade off, but one that can be managed as necessary. For example, you can check the subaddress property of the Hyperlink, and only perform the email if it is in a certain column. If you'd rather not use the hyperlink, you can create a "floating button" of sorts, so that there is only one button on the sheet, but it moves with activecell. Of course, this necessitates that the desired row is selected before the button is pressed. — basodre, Jan 19 '18 at 14:02

Assign Macro with Multiple Arguments to Button VBA

1 Answers1