combobox change event loop

[philfer]

Hello,

I have a userform with two comboboxes on it. The user can select an item from one or the other

I use the change event of the controls to look up the selection from a list and then return some values into two textboxes on the form

I also wanted to clear the first combobox if the user has first selected an item from here and then changes their mind and decides to make a selection from the second. In the second combobox change event I set the ListIndex of the first combobox to -1 and then lookup the value selected in the second combobox.

Setting the ListIndex of the first combobox to -1 fires the change event for that one and doesnt just clear it and run the rest of the sub

Is there a way around this?

I hope I have explained this clearly and not confused everyone!!!

Cheers
Phil

[Bob Phillips]

Post the workbook Phil. I am sure it is manageable, but good to see the actual.

[mikerickson]

Casscading UF events can be handled with with a disabeling argument.
For a single control to prevent itself from cascading, code like this can be used.

[vba]Private Sub ListBox1_Click()
Static abort As Boolean
If abort Then abort = False: Exit Sub
With ListBox1
If .ListIndex <> -1 Then
abort = True
.ListIndex = -1
End If
End With
End Sub[/vba]
This is similar code, without the IF clause. (abort has to be re-set to False in case setting the listindex to -1 doesn't trigger a Change.)
[vba]Private Sub ListBox1_Click()
Static abort As Boolean
If abort Then abort = False: Exit Sub

abort = True
ListBox1.ListIndex = -1
abort = False
End Sub[/vba]
That was similar to a more global solution that prevents one UF's event from causing another control's events to fire.
[vba]Dim UFEventsDisabled

Private Sub ComboBox1_Change()
If UFEventsDisabled Then Exit Sub

UFEventsDisabled = True
TextBox1.Text = ComboBox1.Value
ComboBox1.ListIndex = -1
UFEventsDisabled = False
End Sub

Private Sub TextBox1_Change()
If UFEventsDisabled Then Exit Sub

UFEventsDisabled = True
ComboBox1.Value = TextBox1.Value
UFEventsDisabled = False
End Sub[/vba]

[philfer]

xld,

Here is the workbook with my attempt at the code in it

I will also try the other suggestion when I get to work in the morning

Cheers guys
Phil

[Bob Phillips]

[vba]

Option Explicit

Private fByPass As Boolean

Private Sub cboAccMap_Change()

If Not fByPass Then

fByPass = True

'error handling
On Error GoTo Err_cboAccMap_Change

'freeze the screen
Application.ScreenUpdating = False

With Me

'reset the other combobox
Me.cboControlMap.ListIndex = -1


'lookup the value selected to return the debit and credit maps
.txtDebitMap.Value = Worksheets("AccNum").Range("C" & .cboAccMap.ListIndex + 2).Value
.txtCreditMap.Value = Worksheets("AccNum").Range("D" & .cboAccMap.ListIndex + 2).Value

End With

'unfreeze the screen
Application.ScreenUpdating = True

fByPass = False

'exit the proc
Exit Sub
End If

'error handling
Exit_cboAccMap_Change:
Exit Sub

Err_cboAccMap_Change:
MsgBox "The following error occurred " & Err.Number & Err.Description
Resume Exit_cboAccMap_Change

End Sub

Private Sub cboControlMap_Change()

If Not fByPass Then

fByPass = True

'error handling
On Error GoTo Err_cboControlMap_Change

'freeze the screen
Application.ScreenUpdating = False

With Me

'reset the other combobox
Me.cboAccMap.ListIndex = -1

'lookup the value selected to return the debit and credit maps
.txtDebitMap.Value = Worksheets("ControlMemo").Range("B" & .cboControlMap.ListIndex + 2).Value
.txtCreditMap.Value = Worksheets("ControlMemo").Range("C" & .cboControlMap.ListIndex + 2).Value

End With

'unfreeze the screen
Application.ScreenUpdating = True

fByPass = False

'exit the proc
Exit Sub
End If

'error handling
Exit_cboControlMap_Change:
Exit Sub

Err_cboControlMap_Change:
MsgBox "The following error occurred " & Err.Number & Err.Description
Resume Exit_cboControlMap_Change

End Sub
[/vba]