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]

Do those values get changed whilst the code is running?

 

[mthomas57]

A little more info on this. I did find out that if I use a user form control combo box (instead of active-x) this issue does not occur. Perhaps it's because the control is assigned to a module/macro? To check this I just hard-coded the letter id to pull the data to populate the form and used the combo box to fire the _Change event. Problem is I don't know how to read the value from the combo box to use it instead of the active-x.

Thanks again! Mike

 

[mthomas57]

The values in column "Y" are based on a pivot table. However, it recalls the event with "every" change to a cell. It does as follows:

When cell B5 changes it recalls the _Change event
When cell B6 changes it recalls the _Change event
When cell E3 changes it recalls the _Change event
When cell E4 changes it recalls the _Change event

And it continues this as it iterates through the code and does it every time a cell is changed.

Thanks again!

 

[Fluff]

Try changing the code to a click event

Private Sub Cmb_LetterID_Click()

 

[mthomas57]

No difference. Does the exact same thing. Weird!

 

[Fluff]

Unfortunately I'm out of ideas.

 

[mthomas57]

Can you possibly replicate my code in your Excel and see if it does the same thing? Add some values in column "Y" and use a Defined Name to load the drop down box. Then call my code and see if yours does the same thing or not. Maybe that would shed some light on what going on.

Thanks again!
Mike

 

[Fluff]

No, because I've never used Adodb connections, I've no idea what (if any) formulae you might have, or what if any event code you may be running.

 

[mthomas57]

Could you take a look at my other post below and perhaps shed some light on why I'm getting the error? Because I couldn't get the ActiveX control to work I have been trying to use a Form Control instead. It does not loop like the ActiveX so that is good, but I can't read the value from the drop down. I keep getting an error.

https://www.mrexcel.com/forum/excel-questions/1083061-error-user-form-control-combo-box.html

Thanks again so much for your help!
Mike

 

[mthomas57]

I did find a solution that is working for me. I dropped the ActiveX control and opted for a Form Control instead using the below code. I kept getting the error "Object doesn't support this property or method" when trying to use the Form Control. Turns out I just needed to Dim dd as an object. Thanks again so much for your help!

Code:

Dim ws As Worksheet
Dim dd As Object

WSName = ActiveSheet.Name
Set ws = ThisWorkbook.Worksheets(WSName)
Set dd = ws.DropDowns("Cmb_LetterID")
LetterID = dd.List(dd.ListIndex)