Excel 2010 VBA ActiveX Resizing

Question

Hi I am using excel 2010 with activex controls. I know activex controls can cause corruption and random resizing of the controls. This problem starts when I change screens.

I need to be able to add elements to a form control through a button being clicked and I also need to access elements in the form through VBA.

My question is how do you add an element into a form control listbox and how do you access it and how do you delete it.

My code uses all active x components but I want to switch them up. I am not sure how to set a name on a form control listbox to even access it in VBA.

Any help would be awesome!

PS. I am using windows 7, excel 2010

AcsErno AcsErno · Accepted Answer · 2018-09-24T11:00:07

This method creates a listbox runtime. You just need to leave space on UserForm1 for it.

Dim lb As msforms.ListBox
Set lb = UserForm1.Controls.Add("forms.listbox.1", "MyListBox1")

This is also a good demostration on how to refer to an (ActiveX) object. A workaround to avoid accidental/unexpected resizing you can intentionally run the following snippet regularly from within the code:

If lb.width <> 100 Then   ' or you can check other properties, too
    lb.Top = 120
    lb.Left = 40
    lb.Width = 100
End If

This can be applied to other ActiveX objects, too, e.g.:

If UserForm1.Width <> 200 Then
    UserForm1.Width = 200 
    ...

You just need to find a good place in your code where this snippet is run often, and also definitely in UserForm_Initialize. You can also set other properties like caption or tabstop, etc. this way.

For adding and removing items you need to use indices like this:

lb.AddItem "First item", 1
lb.AddItem "Second item", 2
lb.AddItem "Third item", 3

lb.RemoveItem 2

Excel 2010 VBA ActiveX Resizing

1 Answers