I have a cbo which is linked to a named ranged on another sheet.

Using code, the named range gets deleted and the recreated, but the cbo does
not update it list of items to reflect the new named range content. How can
I refresh its list?

QB

If I close the workbook and reopen it, it refreshes the item list properly?!
So why doesn't it adjust when I redefine the named range using vba?

QB





"QB" wrote:

I have a cbo which is linked to a named ranged on another sheet.

Using code, the named range gets deleted and the recreated, but the cbo does
not update it list of items to reflect the new named range content. How can
I refresh its list?

QB

Hi,

And this :
'----------------------------
With Worksheets("Sheet1")
'Redefine your name
.Range("b1:b75").Name = "toto"
'You add this line
.OLEObjects("ComboBox1").ListFillRange = "toto"
End With
'----------------------------



"QB" a écrit dans le message de groupe de discussion :
...
I have a cbo which is linked to a named ranged on another sheet.

Using code, the named range gets deleted and the recreated, but the cbo does
not update it list of items to reflect the new named range content. How can
I refresh its list?

QB

Use the gotfocus method to repopulate

Private Sub ComboBox1_GotFocus()
ComboBox1.ListFillRange = Application.Names("Stuff").RefersToRange.Address
End Sub
--
HTH...

Jim Thomlinson


"QB" wrote:

If I close the workbook and reopen it, it refreshes the item list properly?!
So why doesn't it adjust when I redefine the named range using vba?

QB





"QB" wrote:

I have a cbo which is linked to a named ranged on another sheet.

Using code, the named range gets deleted and the recreated, but the cbo does
not update it list of items to reflect the new named range content. How can
I refresh its list?

QB

Are you using the combobox from the Forms commandbar or from the
Controls commandbar? Also, what exactly are you deleting? Are clearing
the values within the named Range (e.g.,
Range("TheList").ClearContents), or are you deleting the cells
referenced by the name (e.g., Range("TheList").Delete) or are you
deleting the name itself (e.g.,
ThisWorkbook.Names("TheList").Delete)???

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]



On Wed, 20 Jan 2010 12:45:01 -0800, QB
wrote:

I have a cbo which is linked to a named ranged on another sheet.

Using code, the named range gets deleted and the recreated, but the cbo does
not update it list of items to reflect the new named range content. How can
I refresh its list?

QB