What's the best way to trigger activeX control change event

[Jon von der Heyden]

I have a userform with a bunch of controls. Some are comboboxes and they are dependent, fetching an array from an external database.

At some point a user might choose to 'reset' the form, clear all controls for new record entry. When this happens I want all change events to fire because they are used to regenerate the combo lists.

Generating a combo list example:

Public Property Get Divisions() As Variant
    Dim strSql As String
            
    With Me
        If CBool(Len(.cbxCountry.Text)) Then
            strSql = "SELECT DISTINCT DIVISION FROM BU_TBL WHERE COUNTRY = '" & .cbxCountry.Text & "'" & _
                     " AND SECTOR = '" & .cbxSector.Text & "';"
            Divisions = Application.Transpose(m_clsDB.RunScript(strSql).GetRows)
        Else
            Divisions = VBA.Array(vbNullString)
        End If
    End With
End Property

The property is used to load the combobox in form initialize event and combo change event.

Private Sub UserForm_Initialize()
    Call HookUp
    Call LoadRecord
    
    '// Load the dropdown lists
    With Me
        .cbxCountry.List = .Countries
        .cbxSector.List = .Sectors
        .cbxDivision.List = .Divisions
        .cbxBU.List = .Businesses
    End With
End Sub

Private Sub cbxSector_Change()
    With Me
        .cbxDivision.List = .Divisions
    End With
End Sub

The problem is when I clear the form for entry mode:

Private Sub EntryMode()
    Dim ctl As msForms.Control
    
    For Each ctl In Me.Controls
        With ctl
            If CBool(Len(.Tag)) Then
                .Object = Empty
                .Object.Locked = CBool(Split(.Tag, ";")(4))
            End If
        End With
    Next ctl
End Sub

Clearing the control does not fire the change event. Wondering if there is a cool way to trigger the change event within the loop? I don't especially want to write a line for each control who's list must be regenerated.

 

[Jaafar Tribak]

Clearing the control does not fire the change event

Are you referring to the cbxSector combo ?

Setting the .Object=Empty should trigger the change event of the combo.

 

[Jon von der Heyden]

Are you referring to the cbxSector combo ?

Setting the .Object=Empty should trigger the change event of the combo.

Hi Jaafar

Yes that's one of them. The event isn't fired. Nor is setting the value to "".

 

[RoryA]

Was the value already ""?

 

[Jon von der Heyden]

Nope.

I thought it was because I was using forms control, rather than the actual control explicitly. But seems event doesn't fire even if I say cbxSector.Value = "". What's up with that?

 

[RoryA]

Then it ought to be working, as Jaafar said. Can you post a sample somewhere?

 

[Jon von der Heyden]

Do you mean upload sample workbook and DB?

 

[Sektor]

Can you call this event explicitly?

 

[Jon von der Heyden]

Yes

 

[RoryA]

Yes - if you can stick it on a file hosting site, or mail it to me if you can't provide it in public.