Can't debug "find" function in UserForm

[PinkMafia14]

Ok, I fess up... I tried to modify a UserForm that does a bunch of cool stuff I could never write myself... As a consequence, a lot is not working so I need some help! Attached is the example document, and so far I have spotted the following glitches:

"Find" function
- message is not always correct (ie: says there are "24 instances of x" when it's only 2)
- when data is found, the form textboxes and comboboxes are not populated correctly
- the Listbox throws back more data than just what is relevant

"Amend" function
Just likes to bug - don't know why!

Selecting item from listbox
Doesn't seem to work at all

Hmmm...

[royUK]

I've had a look & you seem to have made changes to range references & sheet references. I've made somer changes but I neeed to get back to work now. I'll try to look later.

[royUK]

You haven't changed the ListBox ColumCount Property to reflect the colums that you have in the data

You seem to have removed the filter code in Findall

[royUK]

I fixed most of the problems but you need to compare the different versions for the remaining errors which are mostly down to the ListBox ColumnCount property

[PinkMafia14]

Already very helpful - thanks I'm pretty new to VBA... I can usually work out what's going on with templates and through searching answers from other threads but I'm afraid I went a bit too far tweaking this one... The original was from the DabaseForm link (which I believe you posted for me a couple of days ago).

I will keep at it but if you find any more obvious errors, that would be great. If you don't have the time to hand-hold an amateur like me, that's also ok!! Worst-case scenario, I can try from the start again and work my way back

Thanks again - nice to know someone is out there with all these answers swimming around in their brain!!!

[PinkMafia14]

Ok... Messed around with it but still coming up with the following problem:

The FindAll only allows me to have 10 columns in the ListBox.
Which means when I click on an item in the list, it obviously only populates the first 10 text/combo boxes but doesn't do anything with the remaining ones.

If you click on "find" the list appears. You will see that in the listbox when you scroll over to the right it stops at column K in sheet "DataEntry"

I've tried to add these, but for some reason the moment I add a 11th column it doesn't like it...

Thanks for all the help, by the way - learned so much today!

[royUK]

I did say that you need to check the ColumnCount Property of the ListBox. It should be set to the number of Columns to display. Also, you then need to expand the code that populates the TextBoxes etc

[PinkMafia14]

I did say that you need to check the ColumnCount Property of the ListBox. It should be set to the number of Columns to display. Also, you then need to expand the code that populates the TextBoxes etc

I tried that and it didn't work. I put extra columns in the ListBox for good measure. Then when I added the code to populate the TextBoxes it stopped me after 10 columns. I must be missing something else but I don't know what.

[royUK]

Have you changed this code, it won't populate all colums as it is

Please Login or Register  to view this content.

like this

Please Login or Register  to view this content.

[PinkMafia14]

instead of
.List(.ListCount - 1, 5) = c.Offset(0, 10).Value
.List(.ListCount - 1, 6) = c.Offset(0, 11).Value
.List(.ListCount - 1, 7) = c.Offset(0, 12).Value
.List(.ListCount - 1, 8) = c.Offset(0, 13).Value
.List(.ListCount - 1, 9) = c.Offset(0, 14).Value

I had:

.List(.ListCount - 1, 10) = c.Offset(0, 10).Value
.List(.ListCount - 1, 11) = c.Offset(0, 11).Value
.List(.ListCount - 1, 12) = c.Offset(0, 12).Value
.List(.ListCount - 1, 13) = c.Offset(0, 13).Value
.List(.ListCount - 1, 14) = c.Offset(0, 14).Value

Those didn't work as it threw back .ListCount -1, 10) as invalid

Same problem lower down when I tried to amend ListBox (Click)

.ComboBox11.Value = ListBox1.List(r, 10)
.ComboBox12.Value = ListBox1.List(r, 11)
.ComboBox13.Value = ListBox1.List(r, 12)
.TextBox14.Value = ListBox1.List(r, 13)

.List(r, 10) and below comes up invalid.

[royUK]

I've corrected it

Please Login or Register  to view this content.

[PinkMafia14]

That's exactly what I put and it bugs. See attatchment - try clicking on find and you will see... Is it possible that excel limits the maximum number of possible columns to 10 in a ListBox??


I tried adding a second listBox and it solves the problem. But if anyone knows of a way to expand the column count to beyond 10 so I can have just one ListBox, that would be a better solution.


Thanks