Names is a workbook object that doesn't need a sheet reference. The value
that is returned does require a sheet reference. You can see the names in
tweo places

1) Insert - Name - Define
2) File - Properties - Custom

from
MsgBox (Mid(Names("SetUp!Sheetlist").Value, 2))
to
MsgBox (Mid(Names("Sheetlist").Value, 2))

"JMay" wrote:

Still getting 1004;
My SheetList range name in it's RefersTo(Box) contains:

=OFFSET(SetUp!$A$1,,,COUNTA(SetUp!$A:$A),1)

My Code to date is:

Private Sub Worksheet_Activate()
With ActiveSheet.ComboBox1 ' A Control toolbox type CB
' .ListFillRange = Mid(Names("SheetList").Value, 2)
MsgBox (Mid(Names("SetUp!Sheetlist").Value, 2))
' .ListFillRange = Range("Sheetlist").Address 'SheetList is dynamic range
' .ListIndex = 0
End With
End Sub

Thanks for your help.



"Joel" wrote:

See what is being returned

msgbox(Mid(Names("Sheetlist").Value, 2))


Error 1004 is usually because the Sheet Name isn't found.

"JMay" wrote:

Joel,
Thanks.. the
.ListFillRange = Range("Sheetlist").address << works fine, but I pickup on
what you are saying about it maybe missing the sheetName, if required.

I also tried the
.ListFillRange = Mid(Names("Sheetlist").Value, 2)
but am getting a R/T error 1004
Application-defined or Object-defined error..

Thanks for your help
"Joel" wrote:

ListfillRange want a string like Sheet1!A1:B10. You can get the range by
using the method such as

.ListFillRange = Range("Sheetlist").address


This doesn't always work because address doesn't return the sheet name.

Instead you can use this

.ListFillRange = Mid(Names("Sheetlist").Value, 2)

The Names method return an equal sign at the beginning so you need to remove
the equal sign with the mid function.



"JMay" wrote:

Trying to get sys working, but below doesn't work (No Userforms here, only WS)

Private Sub Worksheet_Activate()
With ActiveSheet.ComboBox1 ' A Control toolbox type CB
.ListFillRange = Range("Sheetlist") 'SheetList is dynamic range via
=Offset(X,0,0,Counta(A:A),1)
.ListIndex = 0
End With
End Sub

Also tried .Rowsource inplace of ListFillRange, buy NO CIGAR, Why is that?

TIA,,,