Simulate ComboBox selection from another workbook

[PoggiPJ]

I would like to simulate a user selection in a ComboBox in WB2 from a macro that is running in WB1.

The name of the worksheet in WB2 that contains the ComboBox is "Inputs"
The ComboBox.name in WB2 is "inputPlanRateCountry"

In WB2 macros I can read the selected ComboBox value using (for example) CountrySelected = Inputs.inputPlanRateCountry.Value

But if when I try to assign something to the ComboBox .value or .text property from a statement in WB1, I encounter a "Method or data member not found" error.

Following is code from the macro in Workbook1....

Set WB2 = Workbook2.Worksheets("Inputs")

WB2.inputPlanRateCountry.Value = WB1.Range("custSelectedPlanRate").Value

Is there any way that I can enter a country value into that combo box, just as though a user had actually clicked/selected it?

 

[PoggiPJ]

gosh.. no thoughts on this one?

 

[mikerickson]

Have you tried the .ListIndex property?

 

[PoggiPJ]

Not yet - Is that a property that I can only read, or can I set it from the calling macro?

 

[mikerickson]

This syntax worked for me

With Workbooks("Book2").Sheets(1).OLEObjects(2).Object
    .Value = "a"
End With

 

[Jaafar Tribak]

Try this funcion :

Private Function SelectListBoxItem _
(ByVal ListBox As Object, Item As Variant) As Boolean

    Dim ListItems()
    Dim lIndex As Long
    
    ReDim ListItems(ListBox.Object.ListCount)
    ListItems = ListBox.Object.List
    On Error Resume Next
        lIndex = WorksheetFunction.Match(Item, ListItems, 0)
    On Error GoTo 0
    If lIndex <> 0 Then
        ListBox.Object.Value = Item
        SelectListBoxItem = True
    End If

End Function

Usage :

Sub Test()
    
    Set WB2 = Workbook2.Worksheets("Inputs")
    
    If Not SelectListBoxItem(WB2.OLEObjects("inputPlanRateCountry").Value, _
    WB1.Range("custSelectedPlanRate").Value) Then
        MsgBox "Value not found in the list."
    End If

End Sub

 

[PoggiPJ]

Gentlemen, thank you both. This was my first experience with OLEObjects and placing the statement in context like that helped tremendously.