Excel VBA: getting row of clicked button

Question

I am trying to make a button in Excel which copies a certain range of cells from the active workbook to another workbook. The copying of this range works perfectly when I specify a fixed range, but I am stumped on how to figure out the row of the clicked button.

Every row contains 7 or so cells, and the 8th cell contains a shape with a macro attached to it (the button). When the user presses this button the 7 cells on the same row as the row containing the pressed button need to be copied.

Using ActiveCell is of no use, since pressing the button doesn't actually set that cell as active. I searched around a lot but I can't seem to find how to obtain this value. Once I have the row number of the clicked button I can figure the rest out on my own.

score 26 · Accepted Answer · answered Jun 05 '11 at 11:37

26

Each Shape has TopLeftCell property. It contains a cell within which the top left corner of the shape resides.

answered Jun 05 '11 at 11:37

GSerg

76,472
17
159
346

3

I see, thanks for the answer. I was also wondering how to get the name of the shape that was clicked, but after some searching around I found this: ActiveSheet.Shapes(Application.Caller).Name. I'll try working with the TopLeftCell property of the shape and let you know when it's working. Thanks again for pointing me in the right direction! – Jort Jun 05 '11 at 11:46
16

Got it fully working: MsgBox("Row of pressed button: " & ActiveSheet.Shapes(Application.Caller).TopLeftCell.Row). Thanks again! – Jort Jun 05 '11 at 11:51
How do I get the Colum letter? For example, column is 3 but I want 'C' returned. – user3020047 Apr 13 '23 at 16:57
@user3020047 https://stackoverflow.com/q/12796973/11683. How is it related? – GSerg Apr 13 '23 at 19:06

FraggaMuffin · Answer 2 · 2020-06-27T23:47:09.410

23

Try this:

Sub Mainscoresheet() 
     ' Mainlineup Macro
    Dim b As Object, cs As Integer 
    Set b = ActiveSheet.Buttons(Application.Caller) 
    With b.TopLeftCell 
        cs = .Column 
    End With 
    MsgBox "Column Number " & cs 
End Sub

edited Jun 27 '20 at 23:47

answered Feb 04 '14 at 06:46

FraggaMuffin

3,915
3
22
26

Very helpful with dynamicly generated buttons. – kolcinx Dec 01 '17 at 20:47
Is that hyperlink still good? – User1974 Jun 23 '20 at 19:22
Hmm, no, that linked post isn't relevant. I've removed it, thanks user – FraggaMuffin Jun 27 '20 at 23:48

score 8 · Answer 3 · edited Jan 29 '15 at 10:00

8

Excellent answer. Btw It also works for Rownumber!

'Same for rownumbers!
Sub Mainscoresheet() 
     ' Mainlineup Macro
    Dim b As Object, RowNumber As Integer 
    Set b = ActiveSheet.Buttons(Application.Caller) 
    With b.TopLeftCell 
        RowNumber = .Row
    End With 
    MsgBox "Row Number " & RowNumber 
End Sub

edited Jan 29 '15 at 10:00

Rodolphe

1,689
1
15
32

answered Jan 05 '15 at 12:23

Robert Nagtegaal

827
7
4

score -1 · Answer 4 · edited Jun 03 '19 at 19:36

-1

This works too! Selects the cell that the generated button resides in (knew it was in column "K" but that could be calculated too!.

ActiveSheet.Range("K" & ActiveSheet.Buttons(Application.Caller).TopLeftCell.Row).Select

edited Jun 03 '19 at 19:36

rene

41,474
78
114
152

answered Jun 03 '19 at 19:16

user3099993

1
1

Excel VBA: getting row of clicked button

4 Answers4

Linked