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
– user1274820, Commented Jan 18, 2018 at 20:21
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
– user1274820, Commented Jan 18, 2018 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
– Wookies-Will-Code, Commented Jan 18, 2018 at 20:47

basodre · Accepted Answer · 2018-01-18 21:42:11Z

0

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

answered Jan 18, 2018 at 21:42

basodre

5,7901 gold badge18 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Cameron Over a year ago

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.

basodre Over a year ago

@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.

Collectives™ on Stack Overflow

Assign Macro with Multiple Arguments to Button VBA

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related