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