Strange one this

AdrianNeal

New Member
Joined
Mar 22, 2019
Messages
5
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

Code:
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
 
Last edited by a moderator:

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
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)

Code:
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
Code:
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
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
A
[/th][th]
B
[/th][th]
C
[/th][th]
D
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
1
[/td][td]Name[/td][td]RefersTo[/td][td]Sheet[/td][td]Ref[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
2
[/td][td]apple[/td][td] =Dropdowns!$F$2[/td][td] Dropdowns[/td][td]F2[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
3
[/td][td]IndexNos[/td][td] ='Employee Info'!$A$2:$A$4[/td][td] 'Employee Info'[/td][td]A2:A4[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
4
[/td][td]orange[/td][td] =Dropdowns!$F$3[/td][td] Dropdowns[/td][td]F3[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
5
[/td][td]pastry[/td][td] =Sheet1!$J$3[/td][td] Sheet1[/td][td]J3[/td][/tr]
[/table]
[Table="width:, class:grid"][tr][td]Sheet: Sheet20[/td][/tr][/table]
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,896
Messages
6,175,260
Members
452,627
Latest member
KitkatToby

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top