ActiveX ComboBox with Dependent Dynamic List Range Crash on Text Value Edit

Hello,

Thank you in advance. Anonymized copy of project attached.

I have two (2) combo-boxes in the sheet labled "Analysis". The first combo-box "cmBox_SelectSection" sets the .ListFillRange property of the second combo-box, called cmbox_SelectBidItem via the following code in during the Change() event of cmBox_SelectSection so that cmbox_SelectBidItem is only populated with those items that match the Section selected in cmBox_SelectSection:

Please Login or Register  to view this content.

This all works fine if and until the user attempts to edit the value of cmbox_SelectBidItem. Excel immediately crashes if cmbox_SelectBidItem gets focus and the user edits the text string of the value property.

I have set the Style property of both combo-boxes to 2 - fmStyleDropDownList and added the following to the KeyPress() event for both:

Please Login or Register  to view this content.

The above code solved the crash except for when backspace is pressed when the value of cmbox_SelectBidItem is highlighted.

The .Value property of cmBox_SelectBidItem is used in the Click() event of cmdbtn_RetriveBidItems and to set the value of cell $C$8 in the Analysis tab. It is used nowhere else in the project.

How to I solve this? Is there a way to entirely lockout keyboard commands from affecting a combo-box? My guess is this has something to do with how I'm populating the .ListFillRange property of cmBox_SelectBidItem but I'm not sure? Is there a better way I could assign the dynamic range to populate the .ListFillRange property? Open to any suggestions.

Appreciate your time and input.

I see. That would explain why I've been seeing Debug.print calls firing twice.

I think this pretty much tells me to find another way to accomplish what I want. I will mark this thread as solved and redesign around standard VBA Form Controls or good old dependent drop drowns.

Appreciate your time.

I just saw your edit. I'm trying to avoid the Worksheet_Change event as I've found it to be overly volatile and can really slow down calculation time. I appreciate the approach, through.

It won't slow you down. The procedure will only work if you change the value in column B or C of the "Sections" sheet. Try it.

The issue isn't with cmBox_SelectSection, that is populated from the a pretty much static range called lstrng_Sections.

Populating and using the dependent cmBox_SelectBidItem is the problem.

Am I missing something, how does your code suggestion populate cmBox_SelectBidItem with the bid items from the Bid Items sheet that match the Section selected in cmBox_SelectSection?