myFind Range not working

[Darren Smith]

Please can someone say why this myFindRng goes red in the code?

Private Sub Model_Chose_Change()

With ThisWorkbook.Worksheets("Job Card Master")

Me.Quote_Details.ListIndex = Me.Model_Chose.ListIndex
myValToFind = Make_Chose.Value
Set mySearchRng = .Columns("A")
End With

Set myFindRng = mySearchRng.Find(What:=myValToFind, _
           LookIn:=xlFormulas, _
           LookAt:=xlWhole, _
           SearchOrder:=xlByRows, _
           SearchDirection:=xlNext, _
           MatchCase:=False)

 

[RoryA]

And how does the data get from the sheet to the listbox?

 

[Norie]

Darren

If you are setting ColumnHeads to True it's more than likely you are using RowSource to populate the listbox.

Do you have any code in the UserForm_Initialize event or are you populating the listbox from other code?

 

[Darren Smith]

And how does the data get from the sheet to the listbox?

Through this code below

Private Sub UserForm_Initialize()

Dim QDetails As MSForms.ListBox
Dim ws       As Worksheet
Dim RngData  As Range

Set QDetails = Body_And_Vehicle_Type_Form.Quote_Details

Set ws = ThisWorkbook.Sheets("Quote Detail")
With ws
    Set RngData = .Range("A1:J" & .Range("A" & .Rows.Count).End(xlUp).Row)
End With

With QDetails
    .ColumnHeads = True
    .ColumnCount = RngData.Columns.Count
   
    Set RngData = RngData.Resize(RngData.Rows.Count - 1).Offset(1) 
    .RowSource = RngData.Address(0, 0, xlA1, xlExternal) 
   
    .ColumnWidths = "90;60;100;150;90;80;100;95;60"
   
End With

End Sub

 

[Darren Smith]

Darren

If you are setting ColumnHeads to True it's more than likely you are using RowSource to populate the listbox.

Do you have any code in the UserForm_Initialize event or are you populating the listbox from other code?

Your questions are answered above

 

[Norie]

Darren

As Rory already mentioned you can't use AddItem when you populate a listbox using RowSource.

You have 2 options really.

1 Use List to initially populate the listbox.

2 When 'filtering' put the data you want in the listbox on a sheet somewhere and populate from there.

With option 1 you wouldn't be able to have column headers in the listbox, option 2 would involve a fair amount of extra code to move data about.

 

[RoryA]

No it`s not bound to a range using row source

Through this code below


.RowSource = RngData.Address(0, 0, xlA1, xlExternal)

Um.

 

[Darren Smith]

Um.

Sorry thought you meant the row source on the User form Listbox properties. Which is empty??

 

[Darren Smith]

Darren

As Rory already mentioned you can't use AddItem when you populate a listbox using RowSource.

You have 2 options really.

1 Use List to initially populate the listbox.

2 When 'filtering' put the data you want in the listbox on a sheet somewhere and populate from there.

With option 1 you wouldn't be able to have column headers in the listbox, option 2 would involve a fair amount of extra code to move data about.

I think number 2 is what I have.
I take the values from a worksheet??
What could I use instead of add to match Combo & List box?

 

[Norie]

You aren't currently using either option, you are populating the listbox with a static range when the userform opens.

To use option 2 you would need to add code to copy data from that range to another range based on your criteria and then populate the listbox from the range you've copied to.

To use option 1 you would need to change the code in the userform Initialize event to use List instead of RowSource.

With QDetails
    .ColumnCount = RngData.Columns.Count
    Set RngData = RngData.Resize(RngData.Rows.Count - 1).Offset(1)
    .List= RngData.Value
    .ColumnWidths = "90;60;100;150;90;80;100;95;60"
End With

You should then be able to use AddItem to populate the listbox with the result of you search, but you will need to clear it first.

 

[Darren Smith]

I think what you said above has worked.
Seems to be this code below goes Yellow now.
Don`t think the List count coding is very happy?
The error says "Object variable or with block variable not set"???

.List(.ListCount - 1, 0) = myFindRng.Value