Different results for ComboBox.RowSource property in BeforeUpdate Event

**asha3010** · 09-28-2010, 07:26 AM

Greetings,

I have the following code in the beforeupdate event of combobox.

Please Login or Register  to view this content.

While debugging, I noticed that the line result for

Please Login or Register  to view this content.

is different from

Please Login or Register  to view this content.

The former is always one row less than the latter. Can someone please explain what could be the cause even though I have a WITH statement to identify the range?

Many thanks
Asha

**romperstomper** · 09-28-2010, 08:44 AM

Try this:

Please Login or Register  to view this content.

or better still, don't use the RowSource at all.

**foxguy** · 09-28-2010, 08:49 AM

Hi Asha;

I'm guessing that Range("test") is a dynamic range.

When your code encounters "With Range("test")" it sets the range that will be used for all the "." statements following, and doesn't reevaluate Range("test") again.

But when your code encounters

Please Login or Register  to view this content.

it reevaluates Range("test") again and it now contains one more row than when the macro started.

**asha3010** · 09-28-2010, 10:16 AM

Thanks very much to both for your prompt response.

@romperstomper: You recommended not using Rowsource property. Would the other option be using the "Additem" method?

@foxguy: Thanks for the clarification. You are correct - it is a dynamic range.

I had encountered the 're-evaluate' issue when using the SET statement. I thought I could avoid it using the WITH statement but that doesn't seem to be the case.

Is there any other way to get excel to re-evaluate the dynamic range or just that we have to very careful while dealing with dynamic ranges in VBA.

Many thanks
Asha