Hi.

I'm using a combobox to display a parts list. In the
spreadhseet, there is a range named rgParts, and in the
code, there is a range variable anmed rgParts .

When a part is added, the spreadsheet range name is being
expanded, and the code range variable is being reassigned.

Yet, the parts list displayed in the combo box does not
display the new part until the form has been unloaded and
reloaded.

Is there a way to refresh the list displayed in the
combobox without unloading and reinitializing the form?

Thanks.
Mark

Mark

If you have the combobox directly in the spreadsheet from
the control toolbox (ie not on a form which is loaded),
then you can easily have it updated.

Have the part names in a named range which is dynamically
updated. Link the combobox to the named range. Right
click on the sheet tab, select view code and insert the
following in the worksheet change event

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
ComboBox1.ListFillRange = "validnames"
End Sub

This will update the combobox with the updated range
listing.

Tony
-----Original Message-----
Hi.

I'm using a combobox to display a parts list. In the
spreadhseet, there is a range named rgParts, and in the
code, there is a range variable anmed rgParts .

When a part is added, the spreadsheet range name is being
expanded, and the code range variable is being reassigned.

Yet, the parts list displayed in the combo box does not
display the new part until the form has been unloaded and
reloaded.

Is there a way to refresh the list displayed in the
combobox without unloading and reinitializing the form?

Thanks.
Mark
.

Thanks for the suggestion, but the list box is in a form.

I would have thought the rowsource property might have
updated it dynamically each time that the drop box button
is pressed, but apparently, I would have thought wrong.


-----Original Message-----
Mark

If you have the combobox directly in the spreadsheet from
the control toolbox (ie not on a form which is loaded),
then you can easily have it updated.

Thanks for the suggestion, but the list box is in a form.


I really want the listbox in a form, not in the
spreadsheet itself, so I have had the 'Add Part' button
call a code module which unloads and reloads the form...
which in turn is repopulating the list box as desired.

In this specific application, that happens so quickly that
it is not even detectible to the eye, but if anyone knows
of a better way to do it, I'd still be happy to know.

Thanks.

Mark

One option that you have is to make a separate sub in your Userform code
module like this

Private Sub MyInit()

'do initialize stuff here including setting the RowSource property

End Sub

Then in the Initialize event, call this sub

Private Sub UserForm_Initialize()

MyInit

End Sub

Then in your AddPart_Click event, you can call MyInit that will essentially
reinitialize the form without closing and reopening it.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"mark" wrote in message
...
Thanks for the suggestion, but the list box is in a form.


I really want the listbox in a form, not in the
spreadsheet itself, so I have had the 'Add Part' button
call a code module which unloads and reloads the form...
which in turn is repopulating the list box as desired.

In this specific application, that happens so quickly that
it is not even detectible to the eye, but if anyone knows
of a better way to do it, I'd still be happy to know.

Thanks.

ok. thanks.


-----Original Message-----
Mark

One option that you have is to make a separate sub in
your Userform code
module like this

Private Sub MyInit()