ListFillRange needs refreshing

G

Graham

I have a ComboBox, not in a Userform, where the ListFillRange property is a
named range Favorlist which is a dynamic range,
=OFFSET(Forms!$DX$102,0,0,COUNTA(Forms!$DX$102:$DX$500),1).
However every time a new value is added to the named range, it does not show
up in the list from the ComboBox. That is not until I go into the box
properties and put = in front of Favorlist. the = of course does not show up
and all that shows is the word Favorlist as it was when I started. The list
however is then updated, until the next entry, Should I have some event code
somewhere to "refresh" the ListFillRange or am I doing something way out of
line. I am on Excel2002.
I would value any guidance.

Kind regards,
Graham Haughs
Turriff, Scotland
 
D

Dave Peterson

Maybe you could use a worksheet_change event:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Range("$DX$102:$DX$500")) Is Nothing Then Exit Sub
Me.ComboBox1.ListFillRange = Me.Range("FavorList").Address(external:=True)
End Sub

If the listfill range and combobox are on two different sheets, change the
combobox "me." references to point at the correct worksheet:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Range("$DX$102:$DX$500")) Is Nothing Then Exit Sub
me.parent.worksheets("notForms").ComboBox1.ListFillRange _
= Me.Range("FavorList").Address(external:=True)
End Sub
 
G

Graham

Belated thanks Dave. That has sorted the problem perfectly and thanks for
the detail of another situation with the data in another worksheet, as this
is involved as well.

Graham
 
Top