VBA automation error with MSForm objects

I am having an issue in Excel / VBA writing a custom event handler for form comboxes based on this example

I have defined a class

Public WithEvents aComboBox As MSForms.ComboBox

Open in new window


I can "take over" the event handling (ie. my code trigger as expected) but for some reason I can't seem to be able to call methods. For instance I would like to clear the combox and I get an "automation error":
VBE editor
What am I missing ?

Excel 2016
LVL 2
Alexandre TakacsCTOAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
Hard to tell, cause we

a) don't see the relevant code parts in your screenshot (hint: use the CODE button to embed code) and post concise and complete examples.
b) don't see in which line that error occurs.
0
John TsioumprisSoftware & Systems EngineerCommented:
Put a watch on FrmTest.Controls to check if you can actually iterate the controls or if you have any syntactical error,
0
Alexandre TakacsCTOAuthor Commented:
Apologies for unclear description

a) don't see the relevant code parts in your screenshot (hint: use the CODE button to embed code) and post concise and complete examples.
My code is
Dim comboBoxes() As New clsCombobox

Sub TestDialog()
    Dim itemCount As Integer
    Dim ctl As Control

'   Create the ComboBox objects
    itemCount = 0
    For Each ctl In frmTest.Controls
        If TypeName(ctl) = "ComboBox" Then
                
               itemCount = itemCount + 1
               ReDim Preserve comboBoxes(1 To itemCount)
               Set comboBoxes(itemCount).aComboBox = ctl
               comboBoxes(itemCount).aComboBox.Clear

        End If
        
    Next ctl
    
    frmTest.Show
    
End Sub

Open in new window


If I run this code without trying to use the clear method

comboBoxes(itemCount).aComboBox.Clear

Open in new window


It works fine and the events are being triggered  - for instance this works on click events
Private Sub aComboBox_Click()

    MsgBox "Hello from " & aComboBox.Name & " with tag " & aComboBox.Tag

End Sub

Open in new window


Put a watch on FrmTest.Controls to check if you can actually iterate the controls or if you have any syntactical error

Seems fine (I have only one item there)
debug
I have enclosed my test workbook - pretty simple stuff (I thought !)
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

NorieAnalyst Assistant Commented:
Alexandre

There's no workbook attached.

PS Can't you just use this?
ctl.Clear

Open in new window

0
Alexandre TakacsCTOAuthor Commented:
Hmm not sure why the file did not upload (maybe EE doesn't accept macro enabled files).

Try this https://www.dropbox.com/s/zhbcq2t8epveo01/Forms.xlsm
0
NorieAnalyst Assistant Commented:
Alexandre

Pretty sure you can upload macro enable files, I'll take a look at the file you uploaded to DropBox later - can't download it right now.
0
Alexandre TakacsCTOAuthor Commented:
Ok I found my issue !
The problem is that you can not "clear" a ComboBox which has a RowSource assigned to it... In retrospect pretty obvious.
My code works if I make sure the control does not have any RowSource.

Which leads me to a new issue... Knowing that I would like to check at runtime if my ComboBox does have a RowSource assigned to it (to avoid this very problem). Interestingly enough declaring the class as MSForms.ComboBox does not give me a RowSource property, ie
    aComboBox.RowSource = ""

Open in new window

fails whereas this works
   frmTest.cbxTest.RowSource = ""

Open in new window

I do get some properties exposed but not this one ?
missing property
Any idea ?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jan Karel PieterseExcel and VBA ExpertCommented:
Unfortunately, when you declare a msForms.combobox , you receive a subset of the properties. I *think* because under the hood the interface is not to the combobox type but to the more generic "Control" type. This also means you only get a subset of the events a combobox has inside the Userform class. So to have full access to the control, make sure you use a property set in which you assign the control to both a variable declared WIthEvents and one declared without WithEvents as a generic Object. So, in the class module:
Option Explicit

Private moMyCombo As Object
Private WithEvents Ctl As MSForms.ComboBox

Public Property Set MyCombo(ByVal oNewValue As MSForms.ComboBox)
    Set moMyCombo = oNewValue
    Set Ctl = oNewValue
    MsgBox moMyCombo.RowSource
End Property

Private Sub Ctl_Click()
    'To change a property, use:
    moMyCombo.RowSource = "B1:B3"
End Sub

Open in new window

1
Alexandre TakacsCTOAuthor Commented:
well I found my specific issue
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.