Excel Userform VBA Help: Search and Listbox

[JDDMichael]

Good morning! While I continue to poke at this myself I thought it might be smart to see if anyone out there might be able to offer some guiding advice.

The Userform I am working on is simply meant to search for matches, display the data in appropriate text fields, allow the data to be updated if needed, and written back to the underlying sheet.

I've cobbled together a basic functioning form but I am stuck on implementing the following "advanced" 3 things and one BONUS feature I haven't even begun to investigate:

• Searching by any combination of 4 inputs (with the "Sold/Ship Name" input checking two columns... C and P specifically)
• Outputting the matching results to my ListBox
• Allowing for the user to select a result row in the ListBox to have that display in the text fields I have set for display and editing (where applicable)
• BONUS: Writing changes ALSO to a change log sheet (not currently present) with the user, the date, and the fields changed?

My guess is I have entered the land of Arrays and that is where I slammed face first into a wall of my own understanding. My code may be horribly inefficient right now, I am mostly working from YouTube videos and my own ignorance.

I attached an example Workbook with my progress thus far.

[torachan]

I have upload a couple of my recent submissions to this site that I hope you may find useful as they address many of the points that you mentioning.
In no specific order, do not hard code variable items (these are the items that you have loaded to your comboboxes) - the .Additem method is inefficient and is really only intended to add an item to an already loaded list.
List your variables on a separate page (preferably in 'structured tables' and load them to the comboboxes from the table via an array in one simple efficient action.
You will note that all the controls (textboxes & labels) retain their default names, this allows loading through a loop of only 3 rows of code (saves wearing out your fingers typing acres of code).
In the case of the Customer file the search technique is carried out in an array (in this case it is 3 field choices) merely by altering the range references this can extend to as many fields as required.
If you find these techniques helpful and you require further assistance post back on this site and I will endeavour to assist.

[JDDMichael]

Hrm, site appears to have eaten my first reply.

I really like the Customer form (the other one is erroring out for me). I played around with the design a bit to see what I can do, and for the most part if I tread lightly I can maintain functionality. But it isn't very hard for me to break it if I step out of line.

That is actually the really funny part... I actually think your code is so efficient it is even harder for me to keep my head above water (with my current knowledge). I tend to reverse engineer my way through things and perhaps there are just fewer signposts along the way for me to cheat my way to my destination.

I'll definitely keep your example around because I want to peel it apart until I understand it better, but I definitely think that I have less chance of code-hacking in something like expanded multi-input searching in your code than mine.

[torachan]

The one that is erroring is most likely that you have not got 'ListView' activated in your VBA toolbox - so at this stage ignore that file.
You need to check your search code - you use two methods to find 'irow' - one method needs removing, also with your present code it will only ever find the first entry for a customer, you need a secondary filter.
Either by sending all entries for a customer to the listbox and choosing, alternatively by using 'cascading' comboboxes.
I have attached a demo of 'cascading' filtering for reference - simply by the addition of comboboxes you can extend this method to as many levels needed to arrive at a unique data entry.

[JDDMichael]

Threw myself at this all night and I think I may have painted myself into a corner with my hack-job coding. The concept is sound but I suspect that, aside from profoundly inefficient code (which in fairness is just me copying from videos), I am chasing different methodologies here and they are beginning to fight.

My search now "works" in that I can select a criteria and then search against it, with results being displayed in the ListBox... But being able to select a result from the ListBox and have that selection populate all of the text fields display / editing seems like it would require that I take a different approach to how I obtained my ListBox results in the first place...

I've tried modifying other sheets to bring them closer in alignment to what I have built here but I inevitable break every single one in a way that I struggle to circumvent... I'll assume the code is just so tight it doesn't tolerate my changes.

*EDIT* - Pretty sure I stopped at 11pm at the point that my search result display wasn't actually showing the right data in the right places, so I know that is broken. Just haven't attacked it yet.

[torachan]

I have tried to keep it like something you will recognise.
Hints & tips - head your userform code first line 'Option Explicit' then immediately follow by declaring all your variables (this way they are availble throughout each Sub within the app - if you declare them within the Sub they are only available to that Sub).
You will see a variable 'shtrow' this is created in the array that populates the listbox and identifies the sheet/array row - as the array that populates the listbox is fragmented (therefore you cannot use the listindex to locate the sheet row) you have to use some other method - if you decide on filtering this gets slower as your data grows - so take advantage of the first filter and put a row marker in the array and you have an instant row reference.
you will have to check through as some of the cell column references may be incorrect as I got 'dizzy' and tired going back and forth for each textbox.

[JDDMichael]

OMG I see functionality! Okay, I apologize for any sudden silence as I dive into what you did and try to wrap my brain around how far off I was!

*EDIT* Amazing... I actually understand the code changes... Well, insofar as a beginner can understand these things. This is fantastic!

[torachan]

thanks for the feedback and added rep point - glad to have helped - hopefully you will practice with 'structured tables' and looped cell/textbox referencing.
The above coupled with using arrays for data management will pay dividends when your apps grow larger.