Excel UserForm点击ListBox外部隐藏控件的实现方案咨询

这问题我太熟了！你之前用UserForm的Click/MouseUp事件没生效，核心原因是ListBox控件会“抢走”点击事件——当你点击ListBox本身或者窗体上其他控件时，这些事件会被对应控件捕获，根本不会传到UserForm的事件里，只有点击UserForm完全空白的区域时，你的代码才会触发，这显然不是你想要的效果。

下面给你两种可行的解决方案，你可以根据自己的场景选择：

方法一：用底层透明触发控件（简单易实现） #

这种方法适合窗体上控件不多的场景，原理是用一个覆盖整个窗体的控件放在ListBox下方，当ListBox显示时同时显示这个控件，点击它就隐藏ListBox。

步骤： #

1. 在你的UserForm上添加一个Frame控件，命名为FrameBackground。
2. 设置Frame的属性：
   • 把Visible设为False
   • BorderStyle选fmBorderStyleNone（去掉边框）
   • SpecialEffect选fmSpecialEffectFlat（和窗体风格统一）
   • 调整大小和UserForm完全一致，覆盖整个窗体
   • 右键点击Frame，选「置于底层」，确保它在ListBox下面，不会遮挡ListBox
3. 给FrameBackground添加Click事件代码：

   Private Sub FrameBackground_Click()
       ' 隐藏ListBox和底层触发控件
       ListBoxSearch.Visible = False
       FrameBackground.Visible = False
   End Sub
   

4. 当你需要显示ListBox时，同时激活这个Frame：

   ' 比如在TextBox的Enter事件里触发ListBox显示
   Private Sub TextBoxSearch_Enter()
       ListBoxSearch.Visible = True
       FrameBackground.Visible = True
   End Sub
   

注意：如果窗体上还有其他可点击控件（比如按钮、文本框），点击这些控件时不会触发Frame的Click事件，所以这种方法更适合窗体上只有ListBox和静态元素的场景。

方法二：用Windows API+定时器（全面覆盖场景） #

这种方法可以捕获任何点击——不管是窗体内部ListBox之外的区域，还是窗体外部，都能自动隐藏ListBox，适合复杂窗体场景。

步骤： #

1. 在UserForm的代码模块顶部，添加API声明和类型定义（兼容32/64位Excel）：

   #If VBA7 Then
       Private Declare PtrSafe Function GetCursorPos Lib "user32" (lpPoint As POINTAPI) As Long
       Private Declare PtrSafe Function WindowFromPoint Lib "user32" (ByVal xPoint As Long, ByVal yPoint As Long) As LongPtr
       Private Declare PtrSafe Function GetParent Lib "user32" (ByVal hWnd As LongPtr) As LongPtr
       Private Declare PtrSafe Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As LongPtr
       Private Declare PtrSafe Function ScreenToClient Lib "user32" (ByVal hWnd As LongPtr, lpPoint As POINTAPI) As Long
   #Else
       Private Declare Function GetCursorPos Lib "user32" (lpPoint As POINTAPI) As Long
       Private Declare Function WindowFromPoint Lib "user32" (ByVal xPoint As Long, ByVal yPoint As Long) As Long
       Private Declare Function GetParent Lib "user32" (ByVal hWnd As Long) As Long
       Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
       Private Declare Function ScreenToClient Lib "user32" (ByVal hWnd As Long, lpPoint As POINTAPI) As Long
   #End If
   
   Private Type POINTAPI
       x As Long
       y As Long
   End Type
   
   Private WithEvents tmrCheckClick As MSForms.Timer
   

2. 在UserForm的初始化事件里设置定时器：

   Private Sub UserForm_Initialize()
       Set tmrCheckClick = CreateObject("MSForms.Timer")
       tmrCheckClick.Interval = 100 ' 每100毫秒检查一次鼠标位置
       tmrCheckClick.Enabled = False
   End Sub
   

3. 显示ListBox时启用定时器：

   Private Sub TextBoxSearch_Enter()
       ListBoxSearch.Visible = True
       tmrCheckClick.Enabled = True
   End Sub
   

4. 添加定时器的触发事件，判断点击位置：

   Private Sub tmrCheckClick_Timer()
       Dim cursorPos As POINTAPI
       Dim targetHWnd As LongPtr
       Dim parentHWnd As LongPtr
       Dim formHWnd As LongPtr
       Dim clientPos As POINTAPI
       Dim x As Single, y As Single
   
       ' 获取当前鼠标的屏幕坐标
       GetCursorPos cursorPos
       ' 获取鼠标点击的窗口句柄
       targetHWnd = WindowFromPoint(cursorPos.x, cursorPos.y)
       ' 获取当前UserForm的句柄
       formHWnd = FindWindow("ThunderDFrame", Me.Caption)
   
       ' 判断点击的窗口是否属于当前UserForm的一部分
       parentHWnd = GetParent(targetHWnd)
       Do While parentHWnd <> 0 And parentHWnd <> formHWnd
           parentHWnd = GetParent(parentHWnd)
       Loop
   
       If parentHWnd = formHWnd Then
           ' 把屏幕坐标转换为UserForm内部的缇单位坐标
           ScreenToClient formHWnd, cursorPos
           x = cursorPos.x * Me.ScaleX(1, vbPixels, vbTwips)
           y = cursorPos.y * Me.ScaleY(1, vbPixels, vbTwips)
   
           ' 判断点击位置是否在ListBox区域外
           If Not (x >= ListBoxSearch.Left And x <= ListBoxSearch.Left + ListBoxSearch.Width And _
                  y >= ListBoxSearch.Top And y <= ListBoxSearch.Top + ListBoxSearch.Height) Then
               ListBoxSearch.Visible = False
               tmrCheckClick.Enabled = False
           End If
       Else
           ' 点击了UserForm外部，直接隐藏ListBox
           ListBoxSearch.Visible = False
           tmrCheckClick.Enabled = False
       End If
   End Sub
   

5. 别忘了给ListBox添加点击事件，选中选项后自动隐藏：

   Private Sub ListBoxSearch_Click()
       ' 这里可以添加选中后的逻辑，比如把值填入文本框
       TextBoxSearch.Text = ListBoxSearch.Value
       ListBoxSearch.Visible = False
       tmrCheckClick.Enabled = False
   End Sub
   

这个方法不管你点击窗体上的任何其他控件，还是点击窗体外部，都会自动隐藏ListBox，完美解决你的需求。

内容的提问来源于stack exchange，提问作者plocks