xlwings: How to write values to a combo box from python UDF

Question

I am new to using xlwings in excel VBA programming.

I need to send values from a list in python UDF to a combo box in excel sheet.

I found an example at Example 2 but i am unable to get it to work in my code.

Is there a simple example to write values from a list to combox, then read back a value and send to python UDF?

Answer 1

I think I know what you are trying to do - here's how I populate a combobox control (not ActiveX) on a spreadsheet:

def fill_name_cb():
    cb = ct.WS_MAIN.shapes("cbNames").api.ControlFormat
    cb.RemoveAllItems()
    cb.AddItem("Select Client")
    cb.AddItem("Add New Client")
    cb.AddItem("Update List")
    for name in ct.CLIENT_NAMES:
        cb.AddItem(name)
    cb.ListIndex = 1

The variable ct.CLIENT_NAMES is a simple list, like ["Name 1","Name 2", "etc"]

Then, in VBA, I have this macro that runs this using RunPython:

Sub fillComboBox()
    RunPython ("import helpers; helpers.fill_name_cb()")
End Sub

helpers is the name of the Python module. So this doesn't exactly use UDFs, but I think this does what you want (?). If not, let's keep at it!

Now, if I could only figure out how to get the actual name of the selected item, I'd be in great shape - but that seems to escape me.