excel或excel VBA:如何让用户单击某个单元格并将其用作与现有单元格的匹配结果 [英] excel or excel VBA: how can I let user click on a cell and use it as a match result to an existing cell

问题描述

想要

随附的是我要执行的操作的截图.我有一栏列出了所有选项，第二栏列出了一些项目.对于每个项目，我希望用户实际选择一个单元格，然后可以将该选择用作该项目的匹配项.例如，如果使用单击"user1"，那么我需要用文本"user1"填充单元格D4.我怎样才能做到这一点?谢谢！

Attached is a snip picture of what I want to do. I have one column that lists all the options, and on the second column some items. For each item I want the user to physically select a cell and I can then use that selection as the match to the item. For example if the use click on "user1" then I need to populate cell D4 with text "user1". How can I achieve this? Thanks!

推荐答案

您可以使用工作表事件处理程序.根据您的确切条件，使用SelectionChange或DoubleClick.

You can use a worksheet event handler. Depending on your precise conditions, use either SelectionChange or DoubleClick.

例如，在工作表代码模块中:

For example, in the worksheet code module:

此代码寻找用户在第1列中选择一个单元格，然后使用选择的值更新D4.

This code looks for a user to select a cell in column 1, then updates D4 with the selection's value.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Cells.Count = 1 Then
        If Target.Column = 1 Then
            Range("D4").Value = Target.Text
        End If
    End If
End Sub

请记住，如果用户用键盘选择一个单元格，这也会更新，这就是为什么您可能更喜欢使用双击之前事件的原因:

Bear in mind that this will also update if the user selects a cell with the keyboard, which is why you may prefer using the before double click event:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

(如果您使用此代码，则还需要在上面的代码中添加Cancel = True，否则它将允许用户编辑该单元格中的值)

(You will also need to add Cancel = True to the above code if you use this, or it will allow the users to edit the value in that cell)

希望这会有所帮助！

这篇关于excel或excel VBA:如何让用户单击某个单元格并将其用作与现有单元格的匹配结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！