Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Excel - VBA : pass variable from Sub to 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".

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 :

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).

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

Thanks in advance for your help !

like image 370
Phalanx Avatar asked Jun 25 '13 04:06

Phalanx


People also ask

Can you use Vlookup in UserForm?

Excel VBA with Userform Vlookup In this tutorial I'll be showing how you can create an automatic date block in a userform with the Vlookup function. To accomplish this I'll be using the lookup function right within the code of the userform itself.

How do I create a drop down list in UserForm?

Add code to create the lists To create the drop down lists, you can loop through a list on the worksheet, as described below. Or, enter the list's range name in the combo box properties, as described on the Excel VBA ComboBox Match page. In the VBE, select the UserForm, and choose View | Code.


1 Answers

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

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

Within the Userform, declare public variables for both objects.

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
like image 113
Dan Avatar answered Sep 16 '22 14:09

Dan