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

I have read and applied solution I found on similar topics but nothing seem to work in my case.

我已经阅读并应用了类似主题的解决方案，但在我的案例中似乎没有任何效果。

So, I want to pass a variable from one sub of my Module1 to a userform. It's a string called "provinceSugg".

所以，我想将一个变量从我的Module1的一个子传递给一个userform。这是一个名为“provinceSugg”的字符串。

Here is the relevant part of my code :

这是我的代码的相关部分：

Public provinceSugg As String

Sub probaCity()
[...]
If province = "" And city <> "" Then
provinceSugg = sCurrent.Cells(p, db_column).Offset(0, 1).Value
UserForm2.Label1 = "Do you mean " & city & " in " & provinceSugg & " ?"
UserForm2.Label1.TextAlign = fmTextAlignCenter
UserForm2.Show
Else
End If

End Sub

And then in my userform code :

然后在我的userform代码中：

Private Sub userformBtn1_Click()

MsgBox provinceSugg
sMain.Range("J6").Value = provinceSugg

End Sub

When I run my program :

当我运行我的程序时：

1/ I have the content of provinceSugg showing in the MsgBox called from my sub (so there is a provinceSugg, it's not an empty variable).
2/ The MsgBox called from the userform is empty (so passing the value failed) and my program crashes when running " sMain.Range("J6").Value = provinceSugg" with something like "Error 424 Object Required" (so the variable failed to pass to the userform).

1 /我有来自我的sub调用的MsgBox中显示的provinceSugg的内容（所以有一个provinceSugg，它不是一个空变量）。 2 /从userform调用的MsgBox是空的（因此传递值失败）并且我的程序在运行“sMain.Range（”J6“）时崩溃。值= provinceSugg”，类似于“Error 424 Object Required”（所以变量未能传递给userform）。

I tried all the stuff I found on forum and here (different ways to indicate that provinceSugg is a public variable but still crashing...).

我尝试了我在论坛和这里找到的所有东西（不同的方式来表明provinceSugg是一个公共变量，但仍然崩溃......）。

Thanks in advance for your help !

在此先感谢您的帮助！

2 个解决方案

#1

You would be able to create public variables within the Userform that can be set by the Module.

您可以在Userform中创建可由模块设置的公共变量。

These variables are only accessible within the Userform as it is loaded.

这些变量只能在加载时在Userform中访问。

Within the Userform, declare public variables for both objects.

在Userform中，声明两个对象的公共变量。

Public sMain As Worksheet
Public provinceSugg as string

Private Sub userformBtn1_Click()

MsgBox provinceSugg
sMain.Range("J6").Value = provinceSugg

End Sub

Within the Module, you can assess both of those variables.

在模块中，您可以评估这两个变量。

Sub probaCity()
[...]
If province = "" And city <> "" Then

    provinceSugg = sCurrent.Cells(p, db_column).Offset(0, 1).Value

    With UserForm2
        .provinceSugg = provinceSugg 
        Set .sMain = sMain 
        .Label1 = "Do you mean " & city & " in " & provinceSugg & " ?"
        .Label1.TextAlign = fmTextAlignCenter
        .Show
    End With

End If

End Sub

#2

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim selectColumn
selectColumn= Split(Target.Address(1, 0), "$")(0)

Call UserFormStart(selectColumn)
End Sub

Inside Main Module

内部主模块

Public columnSelection As String
...
Public Sub UserFormStart(ByVal columnRef As String)
    'MsgBox "Debug columnRef=" & columnRef
    columnSelection = columnRef
    UserForm1.Show
End Sub

Inside UserForm

在UserForm中

Private Sub UserForm_Initialize()

'MsgBox "Debug UserForm_Initialize =" & columnSelection
...

End Sub

Worksheet_SelectionChange calls a sub on the module where columnSelection is declared as public and visable from the UserForm. I used three different variables for the Column Reference to show that there is where the UserForm has access to the Module. The above all works and took ages to find and work out hence the submission. Happy hunting folks

Worksheet_SelectionChange调用模块上的sub，其中columnSelection从UserForm声明为public且可见。我使用列引用的三个不同变量来显示UserForm可以访问模块的位置。上述所有工作并花了很长时间才找到并解决了提交。快乐的狩猎伙计

#1