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]

If this doesn't work then I don't know.

Try changing the cbxSector change event as follows - leaving your initial code as is.

Private Sub cbxSector_Change()

    With Me.cbxDivision
        If .Locked = True Then
           .Locked = False
           .List = .Divisions
           .Locked = True
        End If
    End With
    
End Sub

Do the same with the other combos.

 

[Jon von der Heyden]

Thanks Jaafar. I don't think it has to do with the locked property because the event fires up just as long as I don't clear control. Here I toggle the control value and the event fires up:

Private Sub EntryMode()
    Dim ctl As MSForms.Control
    
    m_objRECORDSET.MoveLast
    
    For Each ctl In Me.Controls
        With ctl
            If CBool(Len(.Tag)) Then
                .Object.Locked = CBool(Split(.Tag, ";")(4))
                'bug here
                Dim varval
                varval = .Object.Value
                .Object = Empty
                .Object.Value = varval
            End If
        End With
    Next ctl
End Sub

 

[Jaafar Tribak]

Thanks Jaafar. I don't think it has to do with the locked property because the event fires up just as long as I don't clear control. Here I toggle the control value and the event fires up:

Private Sub EntryMode()
    Dim ctl As MSForms.Control
    
    m_objRECORDSET.MoveLast
    
    For Each ctl In Me.Controls
        With ctl
            If CBool(Len(.Tag)) Then
                .Object.Locked = CBool(Split(.Tag, ";")(4))
                'bug here
                Dim varval
                varval = .Object.Value
                .Object = Empty
                .Object.Value = varval
            End If
        End With
    Next ctl
End Sub

Hi jon.

I am afraid I couldn't replicate the problem. I can see this routine is part of a greater project so it is difficult to pinpoint where the problem is. Maybe , if you can break it down into a small example and replicate the same problem then it will become clearer why the change event doesn't fire.

 

[Jon von der Heyden]

Thanks so far anyway Jaafar. I'll see if I can simplify and then I'll post back.