如何让userform窗体位置跟随点击的单元格?

kjbwonder

如附件所示,我需要实现在点击第2列和第9列单元格时显示USERFORM1窗体,要求窗体显示在单元格旁边.
请各位高手帮忙解决下，谢谢！

项目树.rar (20.74 KB, 下载次数: 211)

2013-12-25 16:30 上传

点击文件名下载附件

wpxxsyzx

参考附件，

kjbwonder

wpxxsyzx 发表于 2013-12-25 22:28
参考附件，

非常感谢wpxxsyzx ，根据你提供的参考附件已修改完成达到我要的效果

mistbig

非常感谢，我通过你附件提供的方法，解决了问题。大神啊，给你点个赞。

freexun_zp

wpxxsyzx 发表于 2013-12-25 22:28
参考附件，

請教一下 下面代碼 那一段是控制單元格的
代碼是隨便點一個單元格都呼出窗體，  假如我只需要要在C列點擊單元格時才呼出窗體，這個代碼應該改哪里呢， 請教一下


Option Explicit
Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, _
        ByVal lpWindowName As String) As Long
Private Declare Function GetDC Lib "user32" (ByVal hwnd As Long) As Long
Private Declare Function GetDeviceCaps Lib "gdi32" (ByVal hdc As Long, ByVal nIndex As Long) As Long
Private Declare Function MoveWindow Lib "user32.dll" (ByVal hwnd As Long, ByVal x As Long, _
        ByVal y As Long, ByVal nWidth As Long, ByVal nHeight As Long, ByVal bRepaint As Long) As Long
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim lHwnd As Long
    Dim lDC As Long
    Dim lCaps As Long
    Dim lngLeft As Long
    Dim lngTop As Long
    Dim sngPiexlToPiont As Single
    Const lLogPixelsX = 88
    lDC = GetDC(0)
    lCaps = GetDeviceCaps(lDC, lLogPixelsX)
    sngPiexlToPiont = 72 / lCaps * (100 / ActiveWindow.Zoom)
    lngLeft = CLng(ActiveWindow.PointsToScreenPixelsX(0) + (Target.Offset(1, 0).Left / sngPiexlToPiont))
    lngTop = CLng(ActiveWindow.PointsToScreenPixelsY(0) + (Target.Offset(1, 0).Top / sngPiexlToPiont))
    UserForm1.StartUpPosition = 0
    lHwnd = FindWindow(vbNullString, UserForm1.Caption)
    MoveWindow lHwnd, lngLeft, lngTop, 240, 180, True
    UserForm1.Show 0
End Sub

huang1314wei

freexun_zp 发表于 2016-3-3 18:46
請教一下 下面代碼 那一段是控制單元格的
代碼是隨便點一個單元格都呼出窗體，  假如我只需要要在C列點 ...

请注意看红色代码部分
Option Explicit
Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, _
        ByVal lpWindowName As String) As Long
Private Declare Function GetDC Lib "user32" (ByVal hwnd As Long) As Long
Private Declare Function GetDeviceCaps Lib "gdi32" (ByVal hdc As Long, ByVal nIndex As Long) As Long
Private Declare Function MoveWindow Lib "user32.dll" (ByVal hwnd As Long, ByVal x As Long, _
        ByVal y As Long, ByVal nWidth As Long, ByVal nHeight As Long, ByVal bRepaint As Long) As Long
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim lHwnd As Long
    Dim lDC As Long
    Dim lCaps As Long
    Dim lngLeft As Long
    Dim lngTop As Long
    Dim sngPiexlToPiont As Single
    Const lLogPixelsX = 88
    lDC = GetDC(0)
    lCaps = GetDeviceCaps(lDC, lLogPixelsX)
    sngPiexlToPiont = 72 / lCaps * (100 / ActiveWindow.Zoom)
    lngLeft = CLng(ActiveWindow.PointsToScreenPixelsX(0) + (Target.Offset(1, 0).Left / sngPiexlToPiont))
    lngTop = CLng(ActiveWindow.PointsToScreenPixelsY(0) + (Target.Offset(1, 0).Top / sngPiexlToPiont))
    UserForm1.StartUpPosition = 0
    lHwnd = FindWindow(vbNullString, UserForm1.Caption)
    MoveWindow lHwnd, lngLeft, lngTop, 240, 180, True
    If Target.Column = 3 Then
        UserForm1.Show 0
    End If
End Sub

lcjy11185

这个非常好

wangchaocc

代码有点看不懂，我想要工作薄打开后窗体加载在a3单元格怎么写

dps

代码确实很好。
建议懂的大侠修改一下，点击在屏幕右边的单元格时，弹出的窗体靠左边显示一点，不然窗体显示不全。

boywin3

wpxxsyzx 发表于 2013-12-25 22:28
参考附件，

刚看到代码,非常棒!
后来在初始化事件中,添加了更改窗体大小的代码,发现随着单击不同的单元格,
窗体的大小也逐渐变小, 不知道原因所在...再次请教,谢谢!

1. 添加Initialize事件
Private Sub UserForm_Initialize()
   UserForm1.Width = 240: UserForm1.Height = 180
End Sub
2. 更改了此行代码:
    原先: MoveWindow lHwnd, lngLeft, lngTop, 240, 180, True
修改后:  MoveWindow lHwnd, lngLeft, lngTop, UserForm1.Width, UserForm1.Height, True

导致: 随着单击不同的单元格,窗体的大小也逐渐变小, 不知道原因所在...
窗体变小.rar (11.05 KB, 下载次数: 156)

2018-7-26 16:19 上传

点击文件名下载附件