Manipulating userforms using xlwings

Question

I was trying to find out the methods of the pywin32 object of a userform ComboBox in Excel, but I honestly has no idea what I'm doing and got nowhere.

VBA Code (Sending combobox object to python):

Private Sub ComboBox1_Change()
    s = test(ComboBox1)
End Sub

Python Code :

@xw.func
def test(obj):
    print(obj._dict__)

So, the print above returned this :

{'_oleobj_': <PyIDispatch at 0x03957A90 with obj at 0x01218C8C>, '_username_': 'IMdcCombo', '_olerepr_': <win32com.client.build.LazyDispatchItem object at 0x03FB0FD0>, '_mapCachedItems_': {}, '_builtMethods_': {}, '_enum_': None, '_unicode_to_string_': None, '_lazydata_': (<PyITypeInfo at 0x03957B50 with obj at 0x0121919C>, <PyITypeComp at 0x03957B68 with obj at 0x012196F4>)}

I guess I was expecting to see the same methods/properties found in VBA, but I have no idea what to take from this.

Anyone knows a way to manipulate userform/controls directly from python using xlwings?

Specifically I'm looking for dynamically adding new controls to the userform, reading/modifying controls attributes, and ideally modifying their events, all through python.

Answer 1

I guess I was expecting to see the same methods/properties found in VBA, but I have no idea what to take from this.

You can take anything from this, but this isn't a real Combobox nor something from COM environment - it's just a "wrapper" object over a COM object, implemented via IDispatch interface, and it's possibily thanks to the win32com dependency.

Because of that there's no an "intellisense"-like feature, but you're still able to use properties/methods:

@xw.func
def test(obj):
    #   accesing method
    obj.AddItem('Hello world!')
    #   accesing property
    obj.Enabled = False

also you can pass an UserForm as obj to add a new control to it:

@xw.func
def test(obj):
    #   add new label
    control = obj.Add('Forms.Label.1')
    #   accesing property
    control.Caption = 'Hello world!'

Answer 2

When looking under the documentation for xlWings under the Shapethere does seem to be access to all properties.

Under missing features, you can find a workaround using .api to access all vba methods. Through this you could create and modify controls, just like you would do in VBA.

Also what you could do is using the macro(name)-function you could create functions in VBA to modify, create comboboxes and pass values to the function, i.e to create a combobox at position x, y , width, height and pass these parameters trough python.

As it seems, you cannot access events trough xlWings. But i've found IronPython, it uses the .NET interop facilities to access the excel object and events. As you can see under the documentation, you can work with the excel object as you would do in C#, VB.NETetc..

So as a conclusion, i would suggest you looking up the documentations of both. As they both reveal the excel object to python you should be able to do what you want with one of them.