Strange one this

[AdrianNeal]

I have a userform (TagForm) with a listbox that has the Rowsource controlled by the code below

basically the code finds the range for the Rowsource, by first finding the Column (Colon) then the Last Row (Lastr) of data in that column, the top row of the Range is always 5,

then the range.name is set as the Rowsource

This all works with no problem, in the first Sub below,

but i then created the the second sub, that finds a different value in the “find” from the first sub, all works ok down to the range.name line which fails with an “Application Defined or Object Defined Error”
I don’t get why, the code is the same, in the same Module, run from the same sheet, and UserForm

Function_Data is a UserForm
Tag1Attribute is a test box that is the criteria of the find

Private Sub Tag1Value_enter()
 
Dim Colno As Long
Dim LastR As Long
 
Colno = Sheets("Tags").Range("A1:DD1").Find(Function_Data.Tag1Attribute, SearchOrder:=xlByColumns, SearchDirection:=xlNext).Column
 
LastR = Sheets("Tags").Cells(Rows.Count, Colno).End(xlUp).Row
 
TagForm.ListBox.RowSource = Range(Sheets("Tags").Cells(5, Colno), Sheets("Tags").Cells(LastR, Colno)).Name
 
TagForm.Show
 
End Sub
----------------------------------------------------------------
 
Private Sub Tag2Value_enter()
Dim Colno As Long
Dim LastR As Long
 
Colno = Sheets("Tags").Range("A1:DD1").Find(Function_Data.Tag2Attribute, SearchOrder:=xlByColumns, SearchDirection:=xlNext).Column
 
LastR = Sheets("Tags").Cells(Rows.Count, Colno).End(xlUp).Row
 
TagForm.ListBox.RowSource = Range(Sheets("Tags").Cells(5, Colno), Sheets("Tags").Cells(LastR, Colno)).Name
 
TagForm.Show
 
End Sub

 

[Yongle]

Are you sure that the NamedRange returned in Tag2 code actually exists ?

Test Tag2 with something that works in Tag1
- the code is identical and should return the same named range

Run this code which creates a new sheet containing a list of all named ranges in the workbook
- "Ref" column will not make sense for any named range where RefersTo is NOT a range (simply ignore those as they are not relevant)

Sub LoopNames()

Dim n As Name, s As String, r As String, sh As String, ref As String
With Worksheets.Add
    .Range("A1:D1") = Split("Name RefersTo Sheet Ref")
    For Each n In ThisWorkbook.Names
        r = " " & n.RefersTo
        sh = Replace(Split(r, "!")(0), "=", "")
        ref = Replace(Split(r, "!")(1), "$", "")
        .Cells(Rows.Count, "A").End(xlUp).Offset(1).Resize(, 4) = Array(n.Name, r, sh, ref)
    Next n
End With

End Sub

Add an extra line in Tag2 to get the range

MsgBox Range(Sheets("Tags").Cells(5, Colno), Sheets("Tags").Cells(LastR, Colno)).Address(0,0)
IMMEDIATELY BEFORE:
TagForm.ListBox.RowSource = Range(Sheets("Tags").Cells(5, Colno), Sheets("Tags").Cells(LastR, Colno)).Name

The list of names looks like this
The message box range probably does not tie in to the named range RefersTo for one or more named range

Excel 2016 (Windows) 32 bit

	A​	B​	C​	D​
1​	Name	RefersTo	Sheet	Ref
2​	apple	=Dropdowns!$F$2	Dropdowns	F2
3​	IndexNos	='Employee Info'!$A$2:$A$4	'Employee Info'	A2:A4
4​	orange	=Dropdowns!$F$3	Dropdowns	F3
5​	pastry	=Sheet1!$J$3	Sheet1	J3

Sheet: Sheet20