ListFillRange needs refreshing

[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

 

[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

 

[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