Button which runs macro assigned to cells in that row

Question

I'm looking to have a button assigned to a macro which when pressed will send an email which references data in cells in the same row as the button.

I have the code for sending the email but at the minute only know how to link it directly to certain cells, e.g. A1, B1, C1.

Sub Email_From_Excel_Basic()

Dim emailApplication As Object
Dim emailItem As Object

Set emailApplication = CreateObject("Outlook.Application")
Set emailItem = emailApplication.CreateItem(0)

' Now we build the email.

emailItem.to = Range("A1")

emailItem.Subject = Range("B1")

emailItem.Body = Range("C1")

emailItem.Display

Set emailItem = Nothing
Set emailApplication = Nothing

End Sub

What I want to be able to do is click a button assigned to a row which will then run the macro using the cell references just from that row.

Any help will be appreciated.

Thanks

See [this](https://stackoverflow.com/questions/6242605/excel-vba-getting-row-of-clicked-button) perhaps. — BigBen, Jan 10 '20 at 16:07

score 0 · Answer 1 · answered Jan 10 '20 at 15:51

My recommendation would be to have one button and have it trigger on the active row. You can select the row you want in the worksheet, and then press the button. The ActiveCell.row attribute will pull your selection. The ws.Cells function takes arguments for the row and column . Use the ActiveCell.row to specify the row while hard coding the columns to select the desired cells.

Using Active<anything> can get tricky with some macros, but as long as the button and the data are on the same worksheet, you should be okay.

Dim ws As Worksheet
Dim row As Integer

Set ws = ActiveSheet
row = ActiveCell.row

emailItem.to = ws.Cells(row, 1)
emailItem.Subject = ws.Cells(row, 2)
emailItem.Body = ws.Cells(row, 3)

Jawad · Answer 2 · 2020-01-10T16:23:39.387

ActiveSheet.Buttons(Application.Caller) gives you the properties of the button that you can use to look up the row it is under.

Sub Email_From_Excel_Basic()

 Dim emailApplication As Object
 Dim emailItem As Object

 Set emailApplication = CreateObject("Outlook.Application")
 Set emailItem = emailApplication.CreateItem(0)

 ' Now we build the email.
 Set n = ActiveSheet.Buttons(Application.Caller)
 Dim num As String
 num = n.TopLeftCell.Row

 emailItem.to = Range("A" & num)
 emailItem.Subject = Range("B" & num)
 emailItem.Body = Range("C" & num)

 emailItem.Display


 Set emailItem = Nothing
 Set emailApplication = Nothing

End Sub

Button which runs macro assigned to cells in that row

2 Answers2