Excel - VBA : pass variable from Sub to Userform
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 WorksheetPublic provinceSugg as stringPrivate Sub userformBtn1_Click()MsgBox provinceSuggsMain.Range("J6").Value = provinceSuggEnd 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 WithEnd IfEnd Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)Dim selectColumnselectColumn= 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.ShowEnd 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