12

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 !

Improve this question
7
  • 1
    Worked fine for me. Is there any other code running - such as the UserForm Initialisation - interering with provinceSugg? Can you post your workbook (sanitised if necessary)? Commented Jun 25, 2013 at 5:06
  • @brettdj I have some updates: with this code in the button_click (" MsgBox provinceSugg sMain.Range("J6").Value = provinceSugg), I now have provinceSugg content displayed in the MsgBox but still nothing for the next line and the same error (object required/error 424), so the value is passed but can't be read in the second instruction... Commented Jun 25, 2013 at 5:50
  • 4
    If you try Sheets(x).Range("J6").Value = provinceSugg where x is the position of sMain does this work - I think this is your issue Commented Jun 25, 2013 at 6:37
  • 1
    Oh geez I was so focused on provinceSugg that I completely forgot to take care of other objects! Thanks so much brettdj, that was indeed it ! Commented Jun 25, 2013 at 6:54
  • 2
    provinceSugg isnt the cause of the error in your case. Its the range that is not set. Please see this for further details Commented Jun 25, 2013 at 7:10

3 Answers 3

Reset to default
13

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
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Do you have to reset or clear those public variables to prevent memory leaks or will those public variables go away when the form is closed?
3

If you have a hidden worksheet in your workbook, simply write the parameter to be passed to the User Form somewhere on the worksheet and go read it from there in the User Form.

Improve this answer

3 Comments

Dammit, just started answering this question and realised it's from Jun 2013! :p
I'd open an instance of the form and reference it from a normal module: Sub probaCity(): Dim provinceSugg As String: provinceSugg = "ABC": Dim myFrm As UserForm1: Set myFrm = New UserForm1: With myFrm: With .Label1: .Caption = "Do you mean some city in " & provinceSugg: End With: .Show: End With: End Sub
Pretty simple answer / suggestion but straight forward, me likes (y)
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

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

Improve this answer

1 Comment

This is a better solution when the variables need to be passed from a different project. (Otherwise the other projects needs to include a reference to the project with the userform to be able to access the public variables.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.