在Excel VBA中为多个按钮使用一个子 [英] using one sub for multiple buttons in excel vba
问题描述
我有一个包含客户数据的电子表格，每行一个客户，每行都有一个按钮，该按钮启动一个显示该客户数据的用户窗体，然后您可以更新该客户的数据并将其写回到该行中客户.在我们开始使用电子表格之前，每个案例工作人员都会进行过滤，以便仅显示其客户.
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.
我想知道是否有一种方法可以使每行上的按钮具有一个命令按钮过程，即，当您在第6行中按下按钮时，它会为CommandButton6运行一个过程，以便在您按下按钮时调用该行中的数据在第8行中，它为CommandButton8运行了一个过程以调用第8行中的数据.但是，两者的过程相同，因此我可以有一个CommandButtoni子程序，其中我是行号.
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.
这是一个非常简单的过程，但我不想将其复制350次！
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
推荐答案
在实现Rory的建议之后，我认为这是最简单，最优雅的代码解决方案.如果使用代码统一完成操作，则无需任何进一步配置即可复制和粘贴按钮.
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
如果您不想/不需要设计自定义用户表单，则可以使用Form ...命令:
If you don't want/need to design a custom user form, you could use the Form... command:
- 在Excel中单击自定义快速访问工具栏"下拉菜单标题栏.
- 选择更多命令.
- 如果将文档分发给其他用户，请将对于所有文档(默认)"选项更改为文档名称.
- 选择以从以下命令中选择命令:功能区中没有的命令.
- 选择表单..."命令.
- 单击添加"将按钮添加到右侧菜单.
- 单击确定".
在选择表中的任何单元格的情况下，单击表单"按钮.编辑并按Enter.也可以使用条件"对值进行过滤.不幸的是，没有办法编辑表格，但是对于狭窄的单元格内容很有用.无需编码.
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.
