Hello,

I am developing an Excel-Sheet in order to compare easily vehicle
dimensions.

I first defined an array by selecting the range (i.e. A3:B60) and
naming it (i.e. List1). Then, in a separate Worksheet, I inserted a
ComboBox and entered "List1" as ListFillRange. I specified the
following properties as well:

BoundColumn = 1
ColumnCount = 2

So far, so good. I can select any row of the array "List1" through
the ComboBox.

Then, I need to insert a row within the array "List1". I simply
select a row and choose "insert cells". The range of the array is
automatically updated (i.e. A3:B61). And I can select the inserted row
with the ComboBox. So far, so good!

The problem is that the ComboBox does not display the new last row of
the array (B61). Somehow, the display needs to be updated. Saving the
document doesn't help.

I first need to close the document and open it again, such that the
ComboBox displays the entire range of the array "List1" (B3 to
B61). Does anybody know how this update can occur without having to
close the Excel-Sheet first?

Excel is doing something specific when opening a document, something
which updates everything. Maybe I just need to simulate this update via
a macro.... Does anybody know how to do that?

I would really appreciate any help. Thanks a lot!

Guillaume

In the view code for your ComboBox use this to update the list whenever the
ComboBox gets focus.

Private Sub ComboBox1_GotFocus()
Me.ComboBox1.ListFillRange = "List1"
End Sub

Mike F
wrote in message
oups.com...
Hello,

I am developing an Excel-Sheet in order to compare easily vehicle
dimensions.

I first defined an array by selecting the range (i.e. A3:B60) and
naming it (i.e. List1). Then, in a separate Worksheet, I inserted a
ComboBox and entered "List1" as ListFillRange. I specified the
following properties as well:

BoundColumn = 1
ColumnCount = 2

So far, so good. I can select any row of the array "List1" through
the ComboBox.

Then, I need to insert a row within the array "List1". I simply
select a row and choose "insert cells". The range of the array is
automatically updated (i.e. A3:B61). And I can select the inserted row
with the ComboBox. So far, so good!

The problem is that the ComboBox does not display the new last row of
the array (B61). Somehow, the display needs to be updated. Saving the
document doesn't help.

I first need to close the document and open it again, such that the
ComboBox displays the entire range of the array "List1" (B3 to
B61). Does anybody know how this update can occur without having to
close the Excel-Sheet first?

Excel is doing something specific when opening a document, something
which updates everything. Maybe I just need to simulate this update via
a macro.... Does anybody know how to do that?

I would really appreciate any help. Thanks a lot!

Guillaume

It works !!!
Thank you so much!

Guillaume

Mike Fogleman schrieb:

In the view code for your ComboBox use this to update the list whenever the
ComboBox gets focus.

Private Sub ComboBox1_GotFocus()
Me.ComboBox1.ListFillRange = "List1"
End Sub

Mike F
wrote in message
oups.com...
Hello,

I am developing an Excel-Sheet in order to compare easily vehicle
dimensions.

I first defined an array by selecting the range (i.e. A3:B60) and
naming it (i.e. List1). Then, in a separate Worksheet, I inserted a
ComboBox and entered "List1" as ListFillRange. I specified the
following properties as well:

BoundColumn = 1
ColumnCount = 2

So far, so good. I can select any row of the array "List1" through
the ComboBox.

Then, I need to insert a row within the array "List1". I simply
select a row and choose "insert cells". The range of the array is
automatically updated (i.e. A3:B61). And I can select the inserted row
with the ComboBox. So far, so good!

The problem is that the ComboBox does not display the new last row of
the array (B61). Somehow, the display needs to be updated. Saving the
document doesn't help.

I first need to close the document and open it again, such that the
ComboBox displays the entire range of the array "List1" (B3 to
B61). Does anybody know how this update can occur without having to
close the Excel-Sheet first?

Excel is doing something specific when opening a document, something
which updates everything. Maybe I just need to simulate this update via
a macro.... Does anybody know how to do that?

I would really appreciate any help. Thanks a lot!

Guillaume