0

I am looking to build an Excel worksheet that has a list of items in column A, each paired with a command button in column B. Ideally it would look like this:

Example

When pressing a button, the value in the adjacent column A would be copied to a different worksheet. I understand this is possible to do with a CommandButton_Click() sub, but I will have around 200 buttons and from my understanding, I'd have to do a different sub for each one. Is there a way to avoid this? Is there some code that would apply to all buttons and if a button is pressed it would copy the adjacent column? An example of my current sub is:

Sub CommandButton_Click()
Dim rs As Integer
rs = ActiveSheet.Buttons(1).TopLeftCell.Row
Worksheets("Sheet1").Range("A" & rs).Copy _
Worksheets("Sheet2").Range("A" & rs)
End Sub

Hopefully you guys understand what I'm trying to do here. Any help is appreciated!

Community
  • 1
  • 1
Eli Greenberg
  • 311
  • 1
  • 7
  • 16
  • Better to use a single button and have it operate on the selected row. Managing a bunch of buttons and keeping them organized one on each row is a pain. If you *must* have multiple button, use "Forms" controls and not ActiveX: point them all to the same Sub and within the sub you can check `Application.Caller` for the name of the button to find out which one was pressed (of couse you need to name your buttons accordingly...) – Tim Williams Sep 17 '13 at 23:19
  • I'm going to have at least 100 buttons so it would make it really inefficient to do that I think :/ – Eli Greenberg Sep 17 '13 at 23:21
  • I have a subroutine that creates a buttons for whatever cells I've selected. It can be found here: http://pastebin.com/raw.php?i=Nzur2ZAQ Is there a way to leverage that to create button names that can be tracked easily? – Eli Greenberg Sep 17 '13 at 23:27
  • 1
    Looks like it already names them with the address of their location, so you should be all set. Eg: put this in a sub and attach all the buttons to that sub `ActiveSheet.Range(Application.Caller).Offset(0, 1).Value = "Hello"` – Tim Williams Sep 17 '13 at 23:42

1 Answers1

1

Your button-creating code:

Sub AddButtons()
On Error Resume Next
Dim c As Range, myRange As Range
Set myRange = Selection
For Each c In myRange.Cells
ActiveSheet.Buttons.Add(c.Left, c.Top, c.Width, c.Height).Select
With Selection
.Characters.Text = "ADD"
.Name = c.Address ' names with cell address
End With
Next
myRange.Select
End Sub

Handler:

Sub ButtonClicked()
   Dim c as Range  
   Set c = Activesheet.Range(Application.Caller)
   c.entirerow.copy sheets("Sheet2").cells(c.row,1)       
End Sub
Tim Williams
  • 154,628
  • 8
  • 97
  • 125