Return ComboBox Value From Another Workbook

[jstevens]

Is it possible to return a ComboBox1.Value from a form in another workbook. Workbook One points to Workbook Two. Both workbooks are open.

Example:
Workbook("One") VBA = Msgbox Workbook("Two").MenuForm.ComboBox1.Value 'Should return "Hello" from the UserForm in the workbook named two.

Workbook("Two") MenuForm.ComboBox1.Value = "Hello"

[HansV]

You could do the following:

1) The userform should either be shown modeless, or it should be hidden after having been shown in the normal way. This is because all external code is suspended while a modal userform is open.

2) In workbook One, create the following code in a standard module:

Code: Select all

Function GetCombo()
    GetCombo = MenuForm.ComboBox1.Value
End Function

Sub SetCombo(v)
    MenuForm.ComboBox1.Value = v
End Sub

3) In workbook Two, use code like the following to retrieve the value of the combo box:

Code: Select all

Sub Test1()
    Dim v
    v = Application.Run("One.xlsm!GetCombo")
    MsgBox v
End Sub

4) In workbook Two, use code like this to set the value of the combo box:

Code: Select all

Sub Test2()
    Application.Run "One.xlsm!SetCombo", "Hello"
End Sub

[jstevens]

Thank you Hans!