VBA - 通过单击特定列中的任意位置打开用户窗体 [英] VBA - Open a UserForm by clicking anywhere in a specific column

问题描述

我想在VBA中构建一个makro，当我点击一个特定列中的单元格时，会打开一个UserForm，有关更多详细信息，请参阅此处。

使用此代码（来自 Mr.Burns ） ：

  Private Sub Worksheet_SelectionChange（ByVal Target As Range）
如果Selection.Count = 1然后
如果不相交（目标，范围（A1））不是然后
'userform的名称。显示
如果
结束If 
 End Sub 
  

我可以通过单击单元格A1打开用户窗体，但不能通过单击列A中的任何单元格来打开。 p>

我试图用这段代码解决这个问题：

  Private Sub Worksheet_SelectionChange （ByVal Target As Range）
如果Selection.Count = 1然后
 Dim check As Boolean 
 check = True 
如果检查然后
 Dim i As Long 
对于i = 1到100000 
如果不相交（目标，范围（A& i））不是然后
 UserForm1.Show 
 check = False 
 End If 
 Next 
 End If 
 End If 
 End Sub 
  

它实际工作正常，但是很慢，有什么更好的解决办法吗？

解决方案

要在列A中选择单元格时显示表单：

私人子工作表_SelectionChange（ByVal Target As Range）
'如果目标是一个单元格并且在列A
如果Target.Columns.count = 1和目标。 Rows.count = 1和Target.Column = 1然后
UserForm1.Show
End If
End Sub

I would like to build a makro in VBA which opens a UserForm when I click in a cell in a specific column, for more details look here.

With this code (from Mr.Burns):

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
       If Selection.Count = 1 Then
           If Not Intersect(Target, Range("A1")) Is Nothing Then
            'name of userform .Show
           End If
       End If
   End Sub

I was able to open the UserForm by clicking in the cell A1, but not by clicking in any cell inside the column A.

I tried to solve this problem with this code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Selection.Count = 1 Then
    Dim check As Boolean
    check = True
    If check Then
        Dim i As Long
        For i = 1 To 100000
            If Not Intersect(Target, Range("A" & i)) Is Nothing Then
                UserForm1.Show
                check = False
            End If
        Next
    End If
  End If
End Sub

It actually works fine, but it is very slow, is there any better possibility to solve this?

解决方案

To display the form when a cell is selected in column A:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  ' if target is one cell and in column A
  If Target.Columns.count = 1 And Target.Rows.count = 1 And Target.Column = 1 Then
    UserForm1.Show
  End If
End Sub

这篇关于VBA - 通过单击特定列中的任意位置打开用户窗体的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！