R/T Error - Why?

[JMay]

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,,,

 

[Joel]

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]

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]

See what is being returned

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


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

 

[JMay]

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]

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]

Well, Joel --- I GIVE UP !!
I've been on this for 3+ hours and I'm getting very strung out,,,,grrrrrr
The revised line
MsgBox (Mid(Names("Sheetlist").Value, 2)) still yeilds the 1004 error

Thanks for your time, and concern.
Jim

 

[Dave Peterson]

I'd use:

..ListFillRange = Range("Sheetlist").address(external:=true)

 

[Joel]

Ther are two reasons you have the problem

1) You have more than one workbook opened and it is looking for the named
range in the wrong workbook.

Try MsgBox (Mid(workbooks("book1.xls").Names("Sheetlist").Value, 2))
2) You are spelling the Name Range incorrectly. Check the worksheet menu

Insert - Name - Define

Make sure you are spelling the named range exactly like it is spelled in the
menu.