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 1
Alexandre TakacsCTOAsked:
Who is Participating?
 
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
 
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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
 
NorieVBA ExpertCommented:
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
 
NorieVBA ExpertCommented:
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
 
jkpieterseCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.