从Excel中的其他工作表中获取所选单元格的范围 [英] Getting the selected cell's range from a different worksheet in Excel

问题描述

我正在尝试设置Excel，以便将在第一个工作表中选择的单元格的值设置为在另一个工作表中双击的单元格的值.到目前为止，我的代码如下:

I'm trying to set up Excel so that the cell's value that is selected in the first worksheet is set to the value of a cell that's double clicked in a different worksheet. So far my code looks like this:

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



Dim c As Range

For Each c In Sheet1.Range("M11:M24")
    If IsEmpty(c) Then
        c.Value = Target.Value
        Exit For
    End If
Next c
End Sub

这是将m11:m24范围内的第一个空单元格设置为另一个工作表中双击单元格的内容.我想要的不是静态的"M11:M24"范围，而是让用户通过单击它在第一个工作表中选择一个单元格，移至另一个工作表，双击该工作表中的一个单元格并显示值在第一个工作表上选定的单元格中.我想我可以这样做，以便设置一个变量来保存在第一个工作表中选择哪个单元格，然后从另一个工作表中访问它.但是我更喜欢Excel中没有内置功能来选择所选的单元格.

What this does is sets the first empty cell in the range m11:m24 to the contents of the double clicked cell in the other worksheet. What I want though is not a static "M11:M24" range, but instead have the user select a cell in the first worksheet by clicking on it, move to the other worksheet, double click a cell in that worksheet and have the value appear in the selected cell on the first worksheet. I think I could have it so that there is a variable set up to save which cell is selected in the first worksheet and then just access that from the other worksheet. But I'd prefer if there was away built in to Excel to just choose the selected cell.

是否可以在Excel中获取选定的单元格/范围?

Is there a way to get the selected cell/range in Excel?

推荐答案

我很容易解决了这个问题.代码是:

I solved this easily. The code is:

Sheet1.Activate
ActiveCell.Value = Target.Value

如果您想进行全部选择，请尝试

If you want to do a whole selection, try

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

    Sheet1.Activate

    Dim r As Range
    Set r = Selection

    r.Value = Target.Value

End Sub

这篇关于从Excel中的其他工作表中获取所选单元格的范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！