Using a named range as a source for a combobox on a userform

[Keith R]

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

 

[Guest]

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

"Keith R" skrev:

 

[Guest]

or just :

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

"excelent" skrev:

 

[Keith R]

excelent- thank you for your responses. I'm partway there, but now I'm more
confused than before.

Both versions you suggested work, but only when the sheet that contains the
data referred to in the named range (e.g. Lists) is visible. When I hide
that sheet (so other users don't get confused or mess it up) the named range
that gets pulled into the combobox using =Range("MyNames").Address suddenly
becomes the same range on whatever sheet is active, and not the Lists
worksheet.

Is there a way to ensure that the named range always refers to the Lists
sheet, even when it is hidden?
Currently my named range is:
MyNames = OFFSET(Lists!$A$2,0,0,COUNTA(Lists!$A$2:$A$1000),1)

which includes the source sheet name, so I'm not sure why I'm getting ranges
from the active sheet instead. I'm using XL2003 on WinXP

Thank you,
Keith

When I tried the long version to include the

 

[Guest]

Well y that was strange, didnt no this would happens but think this vil fix it

Sub Strange()
UserForm1.ComboBox1.RowSource = Sheets("Lists").Name & "!" &
Range("MyNames").Address
UserForm1.Show
End Sub


"Keith R" skrev:

 

[Keith R]

Beautiful! I never would have thought of this solution. Works like a charm.
Thanks,
Keith