Buttons Recognizing Rows

  • Thread starter Thread starter S Cho
  • Start date Start date
S

S Cho

Hello, thanks in advance for your help.

Goal: I would like to have a button anchored on Row 10 on my
worksheet, running a macro that will copy values from one area, then
paste those values into cells on Row 10.

Problem 1: How do you get the button/macro to "recognize" that it's on
Row 10, and that the pasted values are then to be placed in the cells
on Row 10. I don't know how to make the button identify the row that
it's on, and how to make the button use that information.

Problem 2: The button/macro needs to be flexible enough to perform the
same function when another row is added above it e.g., I could insert
a new row, so that Row 10 becomes Row 11, and the button is smart
enough to recognize that it's now on Row 11 and can paste those values
into cells on Row 11.

Thanks again,

Steve
 
this could be done with buttons from the forms toolbar

for the macro assigned to the button, application.Caller will return the
name of the button. You can then do

Sub Btn_Click()
Dim btn as Button, sName as String, rw as Long
sName = Application.Caller
set btn = Activesheet.Buttons(sName)
rw = btn.TopLeftCell
msgbox sname & " is above cell " & btn.TopLeftCell.Address(0,0) _
& " and is on row " & rw
End sub
 
Just a typo:

I think Tom meant:
rw = btn.TopLeftCell.Row
instead of just:
rw = btn.TopLeftCell
 
Back
Top