Combobox change event messing up

[percy83]

Hi,

I've got two dependent comboboxes. Depending on value selected in "cmbExpTransportSlag" the second one "cmbExpDest" gets filled with a destination list.

Private Sub cmbExpTransportSlag_Change()
Dim selection As Long

Application.EnableEvents = False
Application.ScreenUpdating = False

selection = cmbExpTransportSlag.ListIndex
'Debug.Print selection

'kollar vilken produkt man valt
If selection = 1 Or selection = 2 Then
With cmbExpDest
.ListFillRange = "rSjödest"
.ListIndex = 0
.Value = " — Välj destination —"
End With

Else
'om man valt flyg
With cmbExpDest
.ListFillRange = "rFlygdest"
.ListIndex = 0
.Value = " — Välj destination —"
End With

End If

If cmbExpTransportSlag.Value = "Sjöfrakt FCL" Then
    With cmbFCLUtr
    .Visible = True
    .ListFillRange = "ExportFCLAlt"
    .Value = "— Välj containertyp —"
    End With
    Range("B15").Value = "Containertyp"

Else
On Error Resume Next
cmbFCLUtr.Visible = False
Range("B15").ClearContents

End If

Application.EnableEvents = True
Application.ScreenUpdating = True


End Sub

When I then select a destination on the second box and go to another sheet tab and then back (for example) above macro triggers again without any apparent reason (atleast according to me ) and resets cmbExpDest to value "— Välj destination —".

Here is the code for cmbbox no 2:

Private Sub cmbExpDest_Change()
Application.EnableEvents = False
Application.ScreenUpdating = False

If cmbExpTransportSlag.Value = "Flygfrakt" Then

Worksheets("AE Rates").Range("AEDest").Value = cmbExpDest.Value

End If

Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

How to work around this issue?

Is it better to use another event?

 

[Jerry Sullivan]

Hi,

I mocked this up and didn't have the same problem that you are describing of cmbExpDest resetting when returning to the sheet.

A few things to check:
Do you have any other events on this worksheet such as Worksheet_Activate or Worksheet_Selection?

Do you have a LinkedCell tied to either Combobox?

Are your named ranges used for the Combox Lists dynamic ranges?

BTW, you might reconsider using "selection" as a user variable name since it is a reserved word in VBA.

Good luck!

 

[percy83]

Hi,

I mocked this up and didn't have the same problem that you are describing of cmbExpDest resetting when returning to the sheet.

A few things to check:
Do you have any other events on this worksheet such as Worksheet_Activate or Worksheet_Selection?

Do you have a LinkedCell tied to either Combobox?

Are your named ranges used for the Combox Lists dynamic ranges?

BTW, you might reconsider using "selection" as a user variable name since it is a reserved word in VBA.

Good luck!

I'll check with other events in the worksheet.

No linked cells. They always mean trouble in my opinion.

All named ranges are dynamic.

It seems like excel is going into calculate mode for like 5 sec and then the value is re-set. I just noticed that it also re-set even if stay in the same sheet.

Thanks for your input!

BR
Percy

 

[percy83]

Hello again,

I just stepped through the code again and if I remove all code in the second (depending) combobox it works fine without a hitch. It must be that code that triggers the change event for the first combobox and therefore re-sets everything getting stuck in an infinite loop.

Should I be using another event type or another approach?


Thanks in advance!

 

[Jerry Sullivan]

Try using static named ranges instead of dynamic ranges and see if you are able to use your code for both comboboxes.

If it works, then the problem could be due to the dynamic ranges for you lists triggering a combobox change event. If that is the problem, you could use the static ranges, or try non-volatile dynamic ranges.

 

[RoryA]

I'd suggest not using ListFillRange - assign the range to the List property.