如何确定是否在 Excel VBA 中选择了一个范围 - 多个范围

how to determine if a range was selected in Excel VBA - Multiple ranges

提问by Hightower

I have created a userform in Excel 2012 whereby when the user double clicks on a specific range of cells the userform pops up.

我在 Excel 2012 中创建了一个用户表单，当用户双击特定范围的单元格时，用户表单会弹出。

This can be done by running the following vba code in the specific pages module.

这可以通过在特定页面模块中运行以下 vba 代码来完成。

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
            'condition to run when not clicked in range
         Cancel = True
            'condition to run if cell in range was clicked
         userform1.show
    End If
End Sub

This works perfectly.

这完美地工作。

The only problem is, since you are checking if not condition, I am unable to run many IF conditions to enable the functionality that will display a range of forms when the user clicks on different sections of the worksheet

唯一的问题是，由于您正在检查 if not 条件，因此我无法运行许多 IF 条件来启用当用户单击工作表的不同部分时将显示一系列表单的功能

Do you know how to enable an if statement that will check if different ranges are clicked, and for each range, show a different userform?

您是否知道如何启用 if 语句来检查是否单击了不同的范围，并为每个范围显示不同的用户表单？

thanks

谢谢

回答by user2140261

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
            'condition to run when not clicked in range
         Cancel = True
            'condition to run if cell in range was clicked
         userform1.show
ElseIf Not Intersect(Target, Range("B1:B10")) Is Nothing Then
        'condition to run when not clicked in range
     Cancel = True
        'condition to run if cell in range was clicked
     userform2.show
End If
End Sub

回答by Bernard Saucier

Not much needs to be modified in your code to achieve what you want to.

无需在代码中进行太多修改即可实现您想要的功能。

Try this!

尝试这个！

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

    If Not Intersect(Target, Range("A1:A10")) Is Nothing Then

        MsgBox "Column A"

    ElseIf Not Intersect(Target, Range("B1:B10")) Is Nothing Then

        MsgBox "Column B"

    ElseIf Not Intersect(Target, Range("C1:C10")) Is Nothing Then

        MsgBox "Column C"

    End If

End Sub

回答by Hightower

The Solution(s) are all correct as per the question, however, based on the fact that the userdialog is actually being called for a particular column header, I decided that a select case would work better for me (where the extension of the worksheet will not affect the forms which are shown)

根据问题，解决方案都是正确的，但是，基于实际上正在为特定列标题调用用户对话框这一事实，我决定选择案例对我来说会更好（工作表的扩展不会影响显示的表格）

hence the code below looks for the particular column name (where the column is the first record on the row) and shows the form based on that.

因此，下面的代码查找特定的列名称（其中该列是该行的第一条记录）并基于该名称显示表单。

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Check the columName to determine which Form to Show
  Select Case Cells(1, Target.Column)
      Case "Column1"
         UserForm1.Show
      Case "Column2"
        UserForm2.Show
      Case Else
        Cancel = True
  End Select
End Sub

回答by Alex P

You can multiple ranges in your Intersectarguments and then test for Columnto see which range was clicked. An example:

您可以在Intersect参数中包含多个范围，然后测试Column以查看单击了哪个范围。一个例子：

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Target, Range("A1:A10, C1:C10")) Is Nothing Then
        If Target.Column = 1 Then
            useform1.show //Show userform1 if A1:A10 clicked
        ElseIf Target.Column = 3 Then
            useform2.show //Show userform2 if C1:C10 clicked
        End If
    End If
End Sub

回答by Siddharth Rout

Like I mentioned in comments why not an ElseIf?

就像我在评论中提到的，为什么不是ElseIf？

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Target, Range("A1:A10")) Is Nothing Then

    ElseIf Not Intersect(Target, Range("B1:B10")) Is Nothing Then

    '
    '`~> And So on
    '
    End If
End Sub