Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.newusers,microsoft.public.excel.programming
|
|||
|
|||
Buttons Recognizing Rows
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 |
#2
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.newusers,microsoft.public.excel.programming
|
|||
|
|||
Buttons Recognizing Rows
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 -- Regards, Tom Ogilvy "S Cho" wrote in message om... 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 |
#3
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.newusers,microsoft.public.excel.programming
|
|||
|
|||
Buttons Recognizing Rows
Just a typo:
I think Tom meant: rw = btn.TopLeftCell.Row instead of just: rw = btn.TopLeftCell Tom Ogilvy wrote: 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 -- Regards, Tom Ogilvy "S Cho" wrote in message om... 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Buttons/inserting rows | Excel Discussion (Misc queries) | |||
autofilter was not recognizing rows after row 999 | Excel Discussion (Misc queries) | |||
Recognizing sets of rows | Excel Discussion (Misc queries) | |||
Hide Buttons along with Rows | Excel Discussion (Misc queries) | |||
Why does the Sort function stop recognizing Header Rows? | Excel Worksheet Functions |