Refresh a Combobox

  • Follow


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
0
Reply Utf 1/20/2010 8:45:01 PM

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
0
Reply Utf 1/20/2010 9:19:01 PM


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" <QB@discussions.microsoft.com> a écrit dans le message de groupe de discussion : 
0FDE2633-6C4D-4DEB-8B71-EF91A3BCDED2@microsoft.com...
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 

0
Reply michdenis 1/20/2010 9:30:25 PM

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
0
Reply Utf 1/20/2010 9:33:01 PM

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 <QB@discussions.microsoft.com>
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
0
Reply Chip 1/20/2010 10:33:57 PM

4 Replies
2734 Views

(page loaded in 0.075 seconds)


Reply: