单击单元格时调出用户表格 [英] Bring up user form when clicking on cell

问题描述

我有一个电子表格，第一列中有一个人的名单，其余各行中有关于他们的各种信息.我有一个用户表单，该表单将填充并随后编辑每条记录，当用户单击此人的姓名时，我希望弹出该记录.

I have a spreadsheet with a list of people in the first column and various bits of information about them in the rest of each row. I have a userform which will populate and subsequently edit each record, which I want to have pop up when the user clicks on the person's name.

例如:

Name        Age    DOB        Postcode
John Smith  55     3/6/1958   RM3 5WO
Mary Jones  22     4/2/1991   RM2 6TP

因此，我希望能够单击John Smith(A2)来显示一个表格，其中包含他的年龄，DOB，邮政编码等字段.我也希望能够使用该表格添加条目，因此它将也可以在列表下方的至少一行上使用.

So I want to be able to click on John Smith (A2) to bring up a form with fields for his age, DOB, postcode etc. I also want to be able to use the form to add entries, so it will also function on at least one row below the list.

已为我提供了以下代码供使用:

I've been given the following code to use:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Target.Address = "$A$2" Then
        frm_tch_staffinfo.Show
    End If

End Sub

我已尝试在工作表中使用此功能，但它似乎不起作用-单击A2时没有任何反应.代码中有什么错误或我是如何实现的? (我已将其放入相关工作表的代码中.)

I've tried using this in the sheet, but it doesn't seem to be working - nothing happens when I click on A2. Is there some error in the code or how I've implemented it? (I've put it in the code for the relevant sheet).

此外，我想扩展目标地址以包括所有名称的范围(其名称可能会更改)和下面的至少一个单元格(但不包括A1中的标头).是否可以定义这样的范围?只需选择A2及以下的值，或者使用对已满的单元格进行计数并在末尾添加或添加更多单元格的过程即可.

Additionally, I want to expand the target address to include the range of all the names (the number of which may change) and at least one cell below (but not the header in A1). Is it possible to define such a range? Either simply selecting A2 and below, or using a process which counts the cells which are full and adds on or more at the end.

感谢您的帮助！

ETA:
我绝对需要Excel，因为我的用法是电子表格而不是数据库.这是一个简化的示例:数据实际上是全数字的，并在其他工作表中用于相对复杂的计算中.

ETA:
I definitely require Excel for this, as my usage is that of a spreadsheet rather than a database. This was a simplified example: the data is in fact all numeric and used in relatively complex calculations in other worksheets.

推荐答案

如注释中所述:代码的第一部分工作正常(选择更改).
我只是做了测试，所以没有任何问题.
请注意:

As mentioned in comment: the first part of your code works just fine (selection change).
I just did the test and there is nothing wrong with it.
Just note:

• 输入正确的表格名称；
• 将代码放在正确的工作表模块中

关于第二个问题，您正在寻找的代码:

As for your second question, the code you are looking for:

Set oRange = Range("A1:A10")
If Not Intersect(Target, oRange) Is Nothing Then
    'do something
End If

如果要动态设置范围，以前有很多相关文章.
例如查看:
获取实际使用范围

If you want to set the range dynamically, there have been many posts on this before.
Check out for example:
Getting the actual usedrange

这篇关于单击单元格时调出用户表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！