Excel VBA ActiveX combobox change event infinite loop

[mthomas57]

Hi everyone this is Mike. I am using Excel 2010 and have a active x combo box that is looping the _Change event every time a cell is changed in the spreadsheet - in this case spreadsheet5. Spreadsheet4 has a combo box also but not active x (this one is firing the Worksheet_Change event) and it works fine. I have declared "Public EnableEvents as Boolean" in spreadsheet5 but the _Change event still loops.

The first loop occurs at line: Range("B5") = Rst!Print_Location. It calls the _Change event again from the top.
Then it runs through all the code until it gets to Exit Sub. Then the next loop does NOT call the _Change event again from the top but rather goes to line: Range("B6") = Rst!LOB_SME
After this the loop seems to be erratic.

Below is the code:

Private Sub Cmb_LetterID_Change()

'I N I T I A L I Z E
'--------------------------------------
    On Error GoTo ErrorHandler
    Const adOpenStatic = 3
    Const adLockOptimistic = 3

    Dim Rcnt        As Double
    Dim cConn       As ADODB.Connection
    Dim Rst         As ADODB.Recordset
    Dim SQL         As String
    Dim FilePath    As String
    Dim LetterID    As String
    
'S E T  V A L I D A T I O N
'--------------------------------------
    'User must select to execute.
    If IsNull(Me.Cmb_LetterID) _
    Or Me.Cmb_LetterID = "" Then
        Exit Sub
    End If
     
'S E T  D E F A U L T S
'--------------------------------------

    Me.EnableEvents = False

    LetterID = Me.Cmb_LetterID

    'Path to same file
    FilePath = Application.ActiveWorkbook.FullName

    Set cConn = New ADODB.Connection
    cConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
               "Data Source=" & FilePath & ";" & _
               "Extended Properties=""Excel 8.0;HDR=Yes;"";"
     
'E X E C U T E  P R O C E D U R E
'--------------------------------------

    'Display print location and RCSA
    '----------------------
    Set Rst = New ADODB.Recordset
    SQL = "SELECT Print_Location, LOB_SME FROM [All_Letter_Summary$] " & _
          "WHERE Letter_ID='" & LetterID & "'"
    Rst.Open SQL, cConn, , adLockOptimistic
    If Rst.EOF Then
    Else
        Range("B5") = Rst!Print_Location
        Range("B6") = Rst!LOB_SME
    End If

    'Display totals
    '----------------------
    Set Rst = New ADODB.Recordset
    SQL = "SELECT * FROM [Letter_ID_Totals$] " & _
          "WHERE Letter_ID='" & LetterID & "'"
    Rst.Open SQL, cConn, , adLockOptimistic
    If Rst.EOF Then
    Else

        'VOLUME
        Range("K3") = Rst!JAN_CurYear_Volume
        Range("K4") = Rst!FEB_CurYear_Volume
        Range("K5") = Rst!MAR_CurYear_Volume
        Range("K6") = Rst!APR_CurYear_Volume
        Range("K7") = Rst!MAY_CurYear_Volume
        Range("K8") = Rst!JUN_CurYear_Volume
        Range("K9") = Rst!JUL_CurYear_Volume
        Range("K10") = Rst!AUG_CurYear_Volume
        Range("K11") = Rst!SEP_CurYear_Volume
        Range("K12") = Rst!OCT_CurYear_Volume
        Range("K13") = Rst!NOV_CurYear_Volume
        Range("K14") = Rst!DEC_CurYear_Volume
        
    End If

ExitHere:
    On Error Resume Next
    Rst.Close
    Set Rst = Nothing
    cConn.Close
    Set cConn = Nothing
    
    Me.EnableEvents = True
    
    Exit Sub

End Sub

 

[Fluff]

Hi & welcome to MrExcel.
First of either remove or change this line

"Public EnableEvents as Boolean"

You should never use VBA keywords for variables.
Secondly on these 2 lines

Me.EnableEvents = False
Me.EnableEvents = True

Replace the word Me with Application

 

[mthomas57]

Thanks so much for the reply. Tried both ways it the result is the same and the active x combo box is still looping the _Change event every time a cell is changed.

 

[Fluff]

For that to happen the combo has to change, how is it being changed?

 

[mthomas57]

Below code is when it happens and it calls the event again at line "Range("B5") = Rst!Print_Location". I don't see how that is changing the combo box at all to call the event again.

Code:
Set Rst = New ADODB.Recordset
SQL = "SELECT Print_Location, LOB_SME FROM [All_Letter_Summary$] " & _
"WHERE Letter_ID='" & LetterID & "'"
Rst.Open SQL, cConn, , adLockOptimistic
If Rst.EOF Then
Else
Range("B5") = Rst!Print_Location
Range("B6") = Rst!LOB_SME
End If

 

[mthomas57]

I just confirmed the combo box value does not change when it recalls the event again. I'm using a variable to load the combo box value:
LetterID = Me.Cmb_LetterID

 

[Fluff]

That line is not loading the combo, it's reading the value from the combo.

 

[mthomas57]

True, I communicated that wrong. The variable is being loaded with the value from the combo box. Thanks!

 

[Fluff]

How does the combo get its values?

 

[mthomas57]

I have a list of values (Letter IDs) in column "Y" that is assigned to Defined Name: "Letter_ID". The Defined Name uses the following for its reference: "=Single_Letter_Detail!$Y$5:INDEX(Single_Letter_Detail!$Y:$Y,MATCH("*",Single_Letter_Detail!$Y:$Y,-1),1)". Finally, the ListFillRange property of the combo box is assigned to "Letter_ID". So the combo box gets its values from column "Y".