VBA UserForm双向文本同步及弹窗前置问题求助

问题说明 #

我有包含TextBox1的UserForm1，因表单空间不足，创建了带TextBox2的UserForm2作为扩展输入弹窗。需求：

• TextBox1和TextBox2内容双向实时同步
• UserForm2始终保持在UserForm1前方，不会被遮挡

当前问题：

1. 仅能实现TextBox1到TextBox2的内容同步，反向同步失效
2. 点击UserForm1时，UserForm2会被遮挡

尝试过的代码 #

UserForm1模块代码 #

Private Sub commandbutton1_Click() 'userform1 module
    Dim n As userform2
    Set n = New userform2
    n.Show 'works
    userform2.textbox2.Text = userform1.textbox1.Text 'not working
End Sub

UserForm2模块代码 #

Private Sub Userform_Initialize() 'userform2 module
    userform2.textbox2.Text = userform1.textbox1.Text 'not working
End Sub

同步尝试代码（分别在两个模块中试过） #

Private Sub textbox2_Change() 
    userform1.textbox1.Text = userform2.textbox2.Text 'not working
End Sub

Private Sub textbox1_Change() 
    userform2.textbox2.Text = userform1.textbox1.Text 'not working
End Sub

编辑后的代码（仍有问题） #

UserForm1模块 #

'code in userform1 module
Private Sub commandbutton1_Click()
    Dim N As userform2
    Set N = New userform2
    N.Show
 End Sub

Private Sub textbox1_Change() 'Works! If I type in textbox1
    userform2.textbox2.Text = Me.textbox1.Text
End Sub

UserForm2模块 #

'code in userform 2 module. Not able to keep userform2 in front of userform1 if I 'touch' or 'click' on userform1
Private Sub UserForm_Initialize()
    Me.textbox2.Text = userform1.textbox1.Text
    Me.StartUpPosition = 0
    Me.Left = Application.Left + (0.5 * Application.Width) - (0.5 * Width)
    Me.Top = Application.Top + (0.5 * Application.Height) - (0.5 * Height)
End Sub

Private Sub textbox2_Change() 'If I type in textbox2, it does NOT appear in textbox1
    userform1.textbox1.Text = Me.textbox2.Text
End Sub

解决方法 #

1. 修复双向同步问题 #

核心问题是实例引用错误：你创建了UserForm2的新实例N，但后续同步代码调用的是默认实例userform2，两者不是同一个对象。修改如下：

UserForm1模块修改

' 声明模块级变量保存UserForm2实例
Private uf2 As UserForm2

Private Sub CommandButton1_Click()
    Set uf2 = New UserForm2
    ' 非模态显示，避免阻塞UserForm1操作
    uf2.Show vbModeless
    ' 初始化同步内容
    uf2.TextBox2.Text = Me.TextBox1.Text
End Sub

Private Sub TextBox1_Change()
    ' 确保UserForm2实例存在且已加载
    If Not uf2 Is Nothing And uf2.Visible Then
        uf2.TextBox2.Text = Me.TextBox1.Text
    End If
End Sub

UserForm2模块修改

Private Sub TextBox2_Change()
    ' 直接引用显示中的UserForm1实例
    UserForm1.TextBox1.Text = Me.TextBox2.Text
End Sub

Private Sub UserForm_Terminate()
    ' 释放UserForm1中的模块级变量
    Set UserForm1.uf2 = Nothing
End Sub

2. 实现UserForm2始终前置 #

VBA原生UserForm没有置顶属性，需通过Windows API实现窗口置顶。在UserForm2模块中添加以下代码：

' 声明API函数
Private Declare PtrSafe Function SetWindowPos Lib "user32" (ByVal hwnd As LongPtr, _
    ByVal hWndInsertAfter As LongPtr, ByVal x As Long, ByVal y As Long, _
    ByVal cx As Long, ByVal cy As Long, ByVal wFlags As Long) As Long

Private Const HWND_TOPMOST = -1
Private Const SWP_NOMOVE = &H2
Private Const SWP_NOSIZE = &H1

Private Sub UserForm_Activate()
    ' 设置窗口置顶，不改变位置和大小
    SetWindowPos Me.hwnd, HWND_TOPMOST, 0, 0, 0, 0, SWP_NOMOVE Or SWP_NOSIZE
End Sub

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