UserForm1.ComboBox1.RowSource = Sheets("Sheet1").Range("MyNames").Address

"Keith R" skrev:

I already have an auto-expanding named range (per Steve Bullen) to grab user
input from a worksheet. When the user clicks a toolbar button, it brings up
my userform and I'd like to use the named range to populate a set of
comboboxes. I thought I could replace the rowsource property with the named
range, but so far I've been unsuccessful- probably a simple syntax problem
(it has been a while since I've done Excel userform programming). I'm using
the loop below because I actually need to load up 6 comboboxes with the same
source.

Any assistance would be greatly appreciated- using XL2003 on WinXP.
Thank you!
Keith

MyNames = OFFSET(Lists!$A$2,0,0,COUNTA(Lists!$A$2:$A$1000),1 )

Relvant code:

Sub UF1Load()
Dim Ctl As Control
For Each Ctl In UF1.Controls
TempName = Left(Ctl.Name, 3)
If TempName = "txt" Then
'<snip
ElseIf TempName = cmb Then
'Ctl.List = MyNames
Ctl.RowSource = MyNames (or Sheet1.MyNames)
End If
Next

UF1.Show

End Sub