Set Active Cell on Command Button Click?

I have a macro assigned to a command button which is supposed to copy the contents of the cell directly above the cell with the command button in it, and paste the results to sheet1, cell A1. But it's not working the way I expect it to, because I think the cell with the button in it needs to be the active cell. I thought that by clicking on the button, it's cell would have focus, but that's not the case. Can anyone help? This is what I have so far:

Please Login or Register  to view this content.

Originally Posted by bheanloh

I think cadnauseum is referring to putting multiple command buttons in a worksheet, and assigned each command buttons with the same macro. I couldn't think of any way to solve that by using command button, except for creating multiple macros.

Exactly! I could hardcode the values into the macros, but that would mean hundreds of different macros. I thought it would be more efficient to have a single macro perform the task. It doesn't have to be a command button, can you think of any other method?

Originally Posted by Xer2

If bheanloh is correct, you could have a global variable and in the click_event for each button, set the value of the variable to a value specific to that button, then call the original sub. In the sub, use a select case statement to choose which button was clicked and which cell will be the active cell.

Forgive my newbie ignorance, but if I make the text label for each command button unique (equal to the string I'm trying to paste in A1), the common macro could capture this and write it to A1 cell, correct?

How do I go about capturing the text of the command button label?