using one sub for multiple buttons in excel vba

Question

I have a spreadsheet containing client data, one client per row and each row has a button that launches a userform showing the data for that client, you can then update the data for that client and write it back to the row for that client. Before we start using the spreadsheet each case worker will filter so that only their clients are shown.

I wondered whether there is a way of having one command button procedure for the buttons on each row ie when you press the button in row 6 it runs a procedure for CommandButton6 to call the data in that row, when you press the button in row 8 it runs a procedure for CommandButton8 to call the data in row 8. However the procedure for both is the same so can I have a CommandButtoni sub where I is the row number.

It is a very simple procedure but I don't want to have to copy it 350 times!

Private Sub CommandButton1_Click()

UserForm1.TextBox1.Value = Worksheets("Sheet1").Range("C2").Value
GetData

UserForm1.Show

End Sub

Answer 1

After implementing Rory's suggestion, I think it is the simplest and most elegant code solution. Allows copy and paste of the buttons without any further configuration if done uniformly with the code.

Sub AddVote()

' This is a simple vote tally counter.  Each time a button is clicked,
' the vote count in the third column is incremented by one.
' Imagine: first column = candidate name; second column holds form button;
' Thid column = vote count
' Create a Form button and assign this macro. Copy and paste button!

Dim rngTally As Range

' Use rngTally to point to the cell in the third column (3, in the formula)
' in the same row as the top left corner of the button.
Set rngTally = Cells(ActiveSheet.Buttons(Application.Caller).TopLeftCell.Row, 3)

' Increment the value of the cell pointed to by rngTally by 1
rngTally = rngTally + 1

End Sub

If you don't want/need to design a custom user form, you could use the Form... command:

1. Click the Customize Quick Access Toolbar drop-down menu in the Excel Title Bar.
2. Select More Commands.
3. If the document will be distributed to other users, change the "For all documents (default)" option to the document name.
4. Select to Choose commands from: Commands Not in the Ribbon.
5. Select the Form... command.
6. Click Add to add the button to the right side menu.
7. Click OK.

With any cell in a table selected, click the Form button. Edit and press Enter. Could also use Criteria to filter on a value. Unfortunately no way to edit the form, but useful for narrow cell contents. No coding required.

https://support.office.com/en-us/article/Add-edit-find-and-delete-rows-by-using-a-data-form-9443c80d-66c6-4c17-b844-64aac5ae841d

Answer 2

You would need a parameterized Click handler, which you can't have in VBA. You could assign 350 form buttons to the same handler procedure, but they would all assign UserForm1.TextBox1.Value whatever is in Worksheets("Sheet1").Range("C2").Value, and actually reading your question you seem to want to have some "row parameter".

Having 350 form/ActiveX buttons on a worksheet is generally not a very good idea: I'd try to think of a different approach instead.

Is the Selection in the row you need it to be when a button is clicked? You could make the current/active row highlight somehow (handle SelectionChanged worksheet event), and have one button somewhere (in the Ribbon?) that works off the Selection:

UserForm1.TextBox1.Value = Sheet1.Range("C" & Selection.Row).Value

Other than that, Rory's suggestion would work... but form buttons weren't intended to be used like this.