0

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
1
  • 5
    Yes - use Form buttons, not ActiveX, assign the same macro to all of the, and refer to Activesheet.Buttons(Application.Caller).TopleftCell.Row to get the row number. Commented Jun 20, 2016 at 13:32

3 Answers 3

1

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.

Sign up to request clarification or add additional context in comments.

1 Comment

I agree that 350 buttons is a bad idea. You can however, create a WithEvents Forms Button class that hooks all button clicks and calls a subroutine. Here's a John Walkenbach post: spreadsheetpage.com/index.php/tip/…. And here's a crazy one I wrote: yoursumbuddy.com/userform-event-handler-class-multiple-controls. They both apply to Form controls on actual forms, but the same idea can be used in a worksheet. Again though, I agree with you that I wouldn't. I'd use RibbonX to add a ribbon or right-click control.
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

Comments

0

A neater way for a sub for each button.

Private Sub Delete0_Click(): DeleteRow (0): End Sub
Private Sub Delete1_Click(): DeleteRow (1): End Sub
Private Sub Delete2_Click(): DeleteRow (2): End Sub
Private Sub Delete3_Click(): DeleteRow (3): End Sub
Private Sub Delete4_Click(): DeleteRow (4): End Sub

Sub DeleteRow(row As Integer)
    Debug.Print "DeleteRow(" & row & ")"
End Sub

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.