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.

 

[Sektor]

That worked for me: cbxSector.Value = vbNullString

 

[Jaafar Tribak]

I think the problem is the line :

.Object.Locked = CBool(Split(.Tag, ";")(4))

The locking seems to happen before the change event has a chance to run.

Try delaying the change event.

In a Standard module :

Public Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" ( _
pDest As Any, pSrc As Any, ByVal ByteLen As Long)


Public Sub DelayedChangeEvent(ByVal Ptr As Long)

    Dim oTempObj As Object
    
    CopyMemory oTempObj, Ptr, 4
    With oTempObj.Parent
        .cbxDivision.List = .Divisions
    End With
    CopyMemory oTempObj, 0&, 4
    
End Sub

Change the cbxSector_Change event as follows :

Private Sub cbxSector_Change()

    Dim lPtr As Long
    
    lPtr = ObjPtr(ActiveControl)
    Application.OnTime Now, "'DelayedChangeEvent " & lPtr & "'"
    
End Sub

Follow a similar approach with the rest of your controls .

 

[Jon von der Heyden]

How you figure this stuff out is a complete mystery to me Jaafar!

I'm falling over at the first hurdle though. The intialize event should also (and does) trigger the cbxSector control change event. However now I get the infamous 'Object variable or With variable not set'.

Public Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" ( _
pDest As Any, pSrc As Any, ByVal ByteLen As Long)


Public Sub DelayedChangeEvent(ByVal Ptr As Long)

    Dim oTempObj As Object
    
    CopyMemory oTempObj, Ptr, 4
    [COLOR=Red]With oTempObj.Parent[/COLOR]
        .cbxDivision.List = .Divisions
    End With
    CopyMemory oTempObj, 0&, 4
    
End Sub

What should oTempObj be set as?

 

[RoryA]

As a matter of interest, can't you just call the Initialize sub again?

 

[Jaafar Tribak]

You can set a module level boolean flag to see who is triggering the change event

[B][COLOR=Red]Private bInitializing As Boolean[/COLOR][/B]

Private Sub UserForm_Initialize()

    [B][COLOR=Red]bInitializing = True[/COLOR][/B]
    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()

    Dim lPtr As Long

    [B][COLOR=Red]If bInitializing Then[/COLOR][/B]
        With Me
            .cbxDivision.List = .Divisions
        End With
      [B][COLOR=Red]  bInitializing = False[/COLOR][/B]
   [B][COLOR=Red] Else[/COLOR][/B]
        lPtr = ObjPtr(ActiveControl)
        Application.OnTime Now, "'DelayedChangeEvent " & lPtr & "'"
    [COLOR=Red][B]End If[/B][/COLOR]

End Sub

 

[Jon von der Heyden]

@Rory. The initialze loads the 1st record from the DB. I suppose I could use a module boolean variable sismilar to what Jaafar has suggested to determine whether or not to load the record when initialized.

Thanks chaps. Just going to have a play with your suggestions now.

 

[Jaafar Tribak]

@Rory. The initialze loads the 1st record from the DB. I suppose I could use a module boolean variable sismilar to what Jaafar has suggested to determine whether or not to load the record when initialized.

Thanks chaps. Just going to have a play with your suggestions now.

Will this avoid the problem ( no need to use the boolean flag )

Replace ActiveControl with cbxSector as follws :

Private Sub cbxSector_Change()

    Dim lPtr As Long

        lPtr = ObjPtr([COLOR=Red][B]cbxSector[/B][/COLOR])
        Application.OnTime Now, "'DelayedChangeEvent " & lPtr & "'"

End Sub

 

[Jon von der Heyden]

Hi Jaafar

It doesn't fall over at initialize, but the event still doesn't fire up.

 

[Jaafar Tribak]

Hi Jaafar

It doesn't fall over at initialize, but the even still doesn't fire up.

Are you saying that the code i posted works for the EntryMode routine but doesn't work for the form initialize event ?

 

[Jon von der Heyden]

Sorry I should have been clear. It works for the Initialize, but not for the EntryMode. In otherwords it appears that the control lock is still causing disruption.