View Full Version : combobox change event loop
philfer
11-20-2010, 12:22 PM
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
11-20-2010, 12:33 PM
Post the workbook Phil. I am sure it is manageable, but good to see the actual.
mikerickson
11-20-2010, 08:17 PM
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.
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
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.)
Private Sub ListBox1_Click()
Static abort As Boolean
If abort Then abort = False: Exit Sub
abort = True
ListBox1.ListIndex = -1
abort = False
End Sub
That was similar to a more global solution that prevents one UF's event from causing another control's events to fire.
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
philfer
11-24-2010, 11:34 AM
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
11-24-2010, 11:49 AM
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.