excel - 变量在 UserForm_Initialize 事件中不可用

Question

我正在尝试将行号传递给 UserForm，因此它可以以用户友好的方式为最终用户显示数据，但在 Initialize 时刻无法捕获此变量。

工作表模块中的代码，它应该打开用户窗体并将行号作为变量传递：

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

Dim DataRange As ListObject
Dim xRow As Long

xRow = Target.Row
Set DataRange = Sheets("Forecast").ListObjects("ForecastTable")

If Application.Intersect(Target, DataRange.DataBodyRange) Is Nothing Or Target.Cells.Count > 1 Then
        Exit Sub
Else
    MsgBox xRow
    With FullInfo
        .MyProp = xRow
        .Show
    End With
    
End If
End Sub

这是用户窗体中的代码：

Property Let MyProp(xRow As Long)
    publicRow = xRow
End Property

Private Sub UserForm_Initialize()

Dim publicRow As Long

MsgBox publicRow
 
End Sub

从MsgBox我用于测试的过程中，我确定工作表模块中的代码返回正确的行号，但随后 UserForm 被初始化它显示为 0，因为没有接收到数据。有趣的是，我在用户表单中放置了一个按钮，用于使用以下代码进行测试：

Private Sub Save_Click()

MsgBox publicRow

End Sub

按下它后 - 它显示正确的行号，因此这意味着它通过但仅在 Initialize 事件之后。Initialize我应该如何将变量传递给 UserForm 以便在事件中可用？

标签： excelvba

Answer 1

我有一个解决方案给你。:) ...所以这是您的代码更正...

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

     Dim DataRange As ListObject
     Dim xRow As Integer
     Dim FullInfo As Object

     xRow = Target.Row
     Set DataRange = Sheets("Forecast").ListObjects("Tabela1")

     If Application.Intersect(Target, DataRange.DataBodyRange) Is Nothing Or 
     Target.Cells.Count > 1 Then
        Exit Sub
     Else
     Set FullInfo = New UserForm1
        
    With FullInfo
     .Label1.Caption = xRow
     .Show
    End With
    
    End If
    End Sub

...如果您想更进一步，我还有另一种方法可以将公共变量传递给 userForm

1. 你在工作表中编码

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   
    Dim DataRange2 As ListObject
    Dim xRow As Integer
   
    xRow = Target.Row
    Set DataRange2 = Sheets("Arkusz1").ListObjects("Tabela2")
   
    If Application.Intersect(Target, DataRange2.DataBodyRange) Is Nothing Or 
    Target.Cells.Count > 1 Then
    Exit Sub
    Else
   
    Call UserFormStart(xRow)
   
    End If
    End Sub
   

2. 将代码放入新模块（在工作表中不起作用）

    'Public rowSelection As Integer  'declare public variable
    Public Sub UserFormStart(ByVal rowRef As Integer)
   
      rowSelection = rowRef  
      UserForm1.Show
   
    End Sub
   

3. 在您的用户表单中

    Private Sub CommandButton1_Click()
    MsgBox rowSelection & " it's work"
    End Sub
   
    Public Sub UserForm_Initialize()
    MsgBox rowSelection
    End Sub
   

这个对我有用 ：）

您可以检查一个主题

Excel - VBA：将变量从 Sub 传递到 Userform