VBA 2007 user form combo box dynamic range row source problem

Hi

I have created 3 combo boxes on a user form. I have set up dynamic ranges in excel that change depending on the user's selection ie - choose an option for combo box 1, the ItemList for combo box 2 will then update, and so on. I am using row source in each combo box to reference the relevant dynamic range.

I am finding that there can be a time delay in ItemList updating for subsequent combo boxes and if you click too quickly into the next combo box, you actually see the items relevant to the original selection in the previous combo box.

Have I done something wrong? Is this a known problem? More importantly, is there a way to make this work properly?

Thanks

Hi

I have tried your suggestions but it hasn't worked (the cursor doesn't even change to an egg timer as the code runs too quickly). I have tried adding a 'sleep' command but this slows the performance of the form and I still occasionally get the problem of the 'old' list populating the combo box.

I have tried a few other options as well but am still getting the problem intermittently. At the moment, the code is working as follows:
1. get ComboBox 1 value from the user
2. save this value to a worksheet
3. calculations in the worksheet examine the data to determine the unique values that match ComboBox 1 selection
4. a dynamic range name in the worksheet (using Offset and Count) defines the list for ComboBox 2 (List2)
5. the VBA code populates ComboBox 2 with the List2
6. the user chooses a value from ComboBox 2
7. save this value to the worksheet
8. calculations in the worksheet examine the data to determine the unique values that match ComboBox 1 and ComboBox 2 selection
9. a dynamic range name in the worksheet (using offset and count) defines the list for ComboBox 3 (List3)
10. the VBA code populates ComboBox 3 with the List3

I have changed the dynamic range to use Max rather than Count and moved it to the code ie - the List to populate the combo boxes is calculated in VBA using Evaluate. This has improved the issue with the combo boxes populating with the most recent data but it is still not 100% reliable.

The problem definitely seems to be the delay between saving the data to the worksheet and defining the new combobox List, and then populating the combobox with the List.

What do I need to do? Should I forget dynamic ranges and revert to static lists? Do all the calculations need to be moved to VBA (I'm not keen to do this as I am relatively new to programming but far more confident using worksheet formula)?

I am desperate for a solution as this part of the form is key to the application and needs to be 100% reliable (particularly given that the current data set is relatively and will be increasing significantly).

Thanks