Userform Search (Listbox) Help

[NewYears1978]

Hi All! TGIF!

This one is complex - in my eyes anyway - so I apologize and thank you in advance!

---Backstory part you can skip if you only want to read the problem---

I have a huge Customer List form that I made for all our sales people since we do not have a CRM. I have made many posts about the form before
The main purpose of the form is that the sales reps can import a list of their current customers and it will update the existing list removing and adding customers that have changed (ie they are no longer their customers or adding new customers).

I decided to add a data entry form of sorts for easier reading of the notes fields and searching for records, and it's going well so far however I have hit a brick wall with add the search function.

Basically when you open the userform (You can run the mDataEntry macro or click the Show Data Entry button in the ribbon) there is a listbox that shows all the data from the database sheet. Double clicking a record will bring that record up (or selecting it and clicking Load Record). That part is working fine.

---End Backstory---


The part that I am trying to fix is he Search area on the bottom right. User can select a search field Syspro ID, Web ID or Customer Name. Then type in the criteria and hit search and it will bring up that list in the listbox.
Customer Name is working fine, as a "contains" search so if I type "Customer" and there are customers called "Test Customer" or "Test Customer 2" they will come up.

However, for some reason my Syspro ID and Web ID searches are not working. I have been at this for 3 days and cannot figure out what is up with it.

There is some code attached to the form itself (listbox, buttons, etc) and then there is a module called mod_DataEntry that has the code for the search (Sub SearchData).

I am hoping someone can find what a did wrong..it's driving me nuts. Sample with small dataset is attached.

Sorry this is so long and detailed but hopefully it makes sense.!



PS
Just for credit sake, the UserForm came from a YouTuber TheDataLabs, I only modified it as I went along to fit my needs, following some of his videos.

[XL-Dennis]

Hello there,
I took a look on your code and the data itself.
First of all, the two first columns SysPro ID and Web ID are numbers and not text numbers while the column Customer Name is text.
In the code (SearchData) the variable sValue is declared as datatype String.
Basically You ask Excel to search for String values but it only exist Long values in the two first columns.
The easiest way to resolve it is to convert the values with the built-in VBA function CStr(Expression).

[NewYears1978]

Hello there,
I took a look on your code and the data itself.
First of all, the two first columns SysPro ID and Web ID are numbers and not text numbers while the column Customer Name is text.
In the code (SearchData) the variable sValue is declared as datatype String.
Basically You ask Excel to search for String values but it only exist Long values in the two first columns.
The easiest way to resolve it is to convert the values with the built-in VBA function CStr(Expression).

Hmm I wonder why that is. I have those values as Txt/Numbers in PowerQuery so they should be strings not actual numbers?

Also, I changed it to Long and it still did not work, if that was the issue wouldn't that have worked (but also breaking the Customer Name search)?

[XL-Dennis]

You can test it by yourself by using the worksheetsfunctions ISTEXT() and ISNUMBER() in the data worksheet.
What you do in PowerQuery does not affect how Excel interpretate the underlying datatypes.
What you are dealing with is an old issue and this is not the place to delve into it. But you need to convert the search string in your VBA-code as per my recommendation:

Add the following code where you do your search:

Please Login or Register  to view this content.

It will not harm the Custom name searches but resolve the issue.

[NewYears1978]

Thanks for the explanations, much appreciated.

So this line:

Please Login or Register  to view this content.

I changed to

Please Login or Register  to view this content.

Or did I misunderstand? That did not work so I assume I understood wrong lol.



Side Note:
I did use the ISTEXT and ISNUMBER to show that yes those columns are number as you said, I had forgotten about those useful functions, thanks

[XL-Dennis]

Sorry for being unclear and to some degree being wrong but I am a bit rusty. At the same time I want to make it more easier for you.

The autofilter accept criteria of the datatype Variant and that may be the best approach to simple declare the variable as Variant:

Please Login or Register  to view this content.

Ask you test it and let us know the outcome :-)

[NewYears1978]

That's actually the very first thing tried, changing to Variant but that did not work. This must mean something else in my code is wrong

I think I got it working at SOME point by having full values and not the "*" & sValue "*" but then I had to enter the whole number. I need contains not equals.


This line

shDatabase.Range("A1:AA" & iDatabaseRow).AutoFilter Field:=iColumn, Criteria1:="*" & sValue & "*"

was

shDatabase.Range("A1:AA" & iDatabaseRow).AutoFilter Field:=iColumn, Criteria1:= sValue

[XL-Dennis]

No, it's not possible to use wildcard (text expression) with numerics (which I should have remembered in the first place).
Let me write a solution enabling you to convert all data in the first two field to text and I will post back asap!

[jolivanes]

Wow, have not seen you around for a long time xlDennis.
Good to see you around again to give your valuable solutions.

[NewYears1978]

No, it's not possible to use wildcard (text expression) with numerics (which I should have remembered in the first place).
Let me write a solution enabling you to convert all data in the first two field to text and I will post back asap!

Ahhh - that explains a lot. I previously had IF statements in there for if it was the Web ID or Syspro ID columns it would do an exact search otherwise the contains. If I recall that one worked however it would only find the value if it was in the first row of data..and I moved on to trying to do just the contains because most people will want to search for partial numbers.

Wow, have not seen you around for a long time xlDennis.
Good to see you around again to give your valuable solutions.

Ohh cool to be involved in the return of a valuable member


(This is an unrelated rant and I am probably going to have to make a new post, one of my columns (F, YTD Sales) is now blank. I think at some point I deleted the data, but now when I update my PQ (I see data in that column in PQ) it will not fill that column..no clue why..this happened randomly. ughhhh this form is my death haha.)

[XL-Dennis]

Hello again,

Now let us see if we can resolve it once for all

Instead of adding additional code to your present code add the following sub routine to the mod_DataEntry:

Please Login or Register  to view this content.

You should implement it in a way so it only runs one time.

Good Luck!

PS: I am surprised that any one remember me at all... I have hard time to do it myself

[NewYears1978]

This worked like a charm. So I assume I need to rerun this every time I update my PowerQuery which is going to update all those columns, but I will do some testing. Thank you!

Now I just have to figure out why Column F in my PQ decided to completely stop working for no reason It is right in PQ but doesn't put it on the Database sheet it just stays blank. I have no clue when this happened... ughhh.


Update:
Ehhh now for some reason the other one is not working, the Customer Name one is now throwing an error on search. I am going to lose my mind lol. Unable to get the Match property of the WorksheetFunction class on

Please Login or Register  to view this content.

This makes no sense we did not mess with that part?


So I backtracked a little and I reran your code and now it's pasing all the data from Column B into Column C? I am so confused lol. Why is it doing that?

[XL-Dennis]

Great you got this major issue resolved. Yes, since all data is cleared when You update the underlying query in PowerQuery you need to run it.
You need to take a break and do other stuff. Then with new fresh eyes you take a closer look into it. Enjoy that we all know a little bit more :-)

FYI, PowerQuery is an add-on to Excel. It means it is outside Excel's core. So when you import data to PowerQuery it manage it only inside itself. When you in the next step dump the data to a worksheet it is like copy and paste from another program.

[NewYears1978]

Great you got this major issue resolved. Yes, since all data is cleared when You update the underlying query in PowerQuery you need to run it.
You need to take a break and do other stuff. Then with new fresh eyes you take a closer look into it. Enjoy that we all know a little bit more :-)

FYI, PowerQuery is an add-on to Excel. It means it is outside Excel's core. So when you import data to PowerQuery it manage it only inside itself. When you in the next step dump the data to a worksheet it is like copy and paste from another program.

Yeah I used to do this with a macro. The user updates their list of 3000+ customers by importing a new list. I used to compare them with a macro and that could take 10 minutes or more, so I switched to PowerQuery which takes 5 seconds instead. But yeah it's a pain to work with. This new error came out of nowhere and I can't figure out why column F is blank for absolutely no reason. I can see the data on hidden IMPORT sheet and in the PowerQuery it's there but on the output on Database it is not there...???

Now with your code you wrote me for some reason when I ran it again, it's pasting column B into Column C...why?

[XL-Dennis]

I made a typo in my first version and then updated the code. Please copy the subroutine and test again. Do you have any hidden column between the first columns?
I will retest it again by copying the data from your sample book to make sure it works.

Interesting with PowerQuery vs VBA. Usually it's the other way around. I try to work with the database either directly in Excel or with integrated Python solutions.

[XL-Dennis]

I must admit that I feel stupid. I posted the code and then I thought it didn't looked correct so I changed it.
Please accept my apologize for fooling around. I have now updated it (i e restored the code) above.

[NewYears1978]

Okay, this is a rewritten post. The other post was long-winded, and confusing. The new post is still long-winded, but hopefully I removed all the unnecessary stuff and made it make more sense.

----------New Message--------

1.
So - I am at home now and tested your code and it works fine. Not sure why it was not working at work on my laptop. Maybe something is wrong with it. I will know Monday when I get back in office and see if the code doesn't work there.

Also - all three searches are now working. The Customer Name one that was giving me an error was my fault for changing the Header name of that column.

So - Yay! Search is working now. Thank you!

2.
I have been thinking about what you said about using VBA and not Power Query. So in this form I did used to use a VBA routine and as stated it would take forever. 10-15 minutes on first run. This is fine on my home computer, but these work computers are not great.
If I am able to use VBA instead of Power Query though, I can eliminate a bunch of extra code I have and also eliminate 4 or 5 major problems I have.

I don't know if you're willing to help on this, I may need to pay someone (which I would gladly do at this point) but I will break down the form and what it does and what the code does and see what you think as well as show you what the old code was.

On the old form I have a sheet named "Current" (which is the same as the one named "Database" in the new form you've seen).
This "Current" sheet is not quite the same as the newer one named "Database", but the basic premise. It is a database of customers that our sales agents use to keep track of their customer base, since our company does not have a CRM.
Each sales agent has about 3,000 customers each. This list changes daily as they get new customers or remove customers from their service. The point of the form is so that each sales agent can keep a current list of all their customers, and keep notes for them.
The agents are able to export a current list of their customers from our systems...however they have no way of putting notes (notes are columns at the end) in their old file and keeping them in the new file, or adding / removing customers that should be removed or added.

So hidden is a sheet called "New" (which is called "Imported in the newer file you've seen), I have a macro that brings up a file picker and they are able to pick their exported list that they've exported and the macro imports that file onto the "New" sheet.

So the macro in question that I used before using Power Query comes into play now. What the macro does is as follows:

Column A (Syspro ID) is the key value that is going to be checked as it is always present, never blank, etc.
The macro will check Column A in the "Current" sheet vs Column A in the "New" sheet.
If a record is found in "New" sheet that doesn't exist in the "Current" sheet, it will be added to the "Current" sheet.
If a record is found in the "Current" sheet that is not in the "New" sheet, it will be removed from the "Current" sheet.
Lasty, If a record is found in both sheets, Column A stays the same since it is the key value, but all other columns B:T will be updated with the newer data in the "New" sheet.
Columns after T are untouched and need to remain so that notes they have typed in will not be erased.

So the original values from B:T in "Current" would be overwritten by the values from "New" but the values in U:AA in "Current" would be retained since those are user inputted notes, not something from the "New" file.

I hope that makes sense
Here's the code that was used for that last part (the comparing part)

Please Login or Register  to view this content.

This code was written by user here named xladept, he helped me a great deal many years ago. It's kind of magical. Small code and works great - but not sure if there is a faster way or something? That's why I switched to PQ because it was so much faster.

This code worked well for me over the years, but it was super slow because the computers we have at work are typically older HP computers, with slower CPUs and only 8GB RAM.
I just ran an update test on my home computer, which is a Ryzen 5900X, SSD drives, 32GB Ram - and updating from 0 rows to 4,000 rows took 3-4 minutes.
That's not too bad but at work this would take 10-15 minutes or so and sometimes crash their computers (because of the slow CPU and low RAM).

One other note, I had an even older version that columns B:T were INDEX MATCH or VLOOKUPS so that all the macro had to do was check Column A and then paste Column A only.
This worked also but having VLOOKUPS or INDEX/MATCH for 3-4,000 rows was also slow and problematic when sorting and such so it was not ideal.

Not expecting you to necessarily help on this one, I know it's a lot, but if you think that doing this with VBA instead of PQ is the way to go and it's possible without too much trouble it would really save me a heck ton of stress lol.
I am attaching this verison of the older file.

Regardless, I really appreciate all your help and kindness!

[Sintek]

@NewYears1978...

Here is a different approach...Not making use of an extra sheet or filter...
This option is type as you go...
Also, no need for all selection as all is entire database anyway...Have not tested to full capacity as yet...
Make a [SearchBy] selection and then start typing in [SearchFor]...
If later you want to add all the other columns and formats to the listbox...that can be added
Some advice when building this type of Interface...Always keep the control names as default...
It is an absolute nightmare to code individual named controls...also so dificult to loop as one would have to store these names in an array to loop whereas with default one can just iterate over numbers 1 through whatever...
BeyondExcel has another excellent option by making use of Rowsource only...He believes in this method...
Torachan also has extensive examples of this...

[XL-Dennis]

Hello again,
Nice to see other members chim in

You seems to have been working with this tool a lot which also mean you have a clear idea what you want to achieve. That means that you are capable of writing a specification for the tool. I suggest you to get in touch with the commercial service on this forum for further help.

After more then 40 years of running my own consultant business (MS Excel, SQL Server et al) I would say that your situation is very common. I am retired so I can only help you by replying on specific questions here but are no longer available for hiring.

It is an absolute nightmare to code individual named controls...also so dificult to loop as one would have to store these names in an array to loop whereas with default one can just iterate over numbers 1 through whatever...

Based on my knowledge and experiences I have to disagree. A well-structured code, comments in the code and naming convention with a proper documentation is always the best approach

Your recommendation is to use the generated names in order to iterate through the controls. In that very specific case one may agree with it but in the long run it's a too much of a "quick & dirty" solution. At least to me. Listbox1, Listbox2, Listbox3 and so on do not carry any information at all. The key is to make the code understandable in order to bring clarity. As long as we are consequent with our naming convention we can always iterate through a collection of items

Sorry if I sound as an old teacher (which I am not) but I have seen too much poor solutions through the years.

[torachan]

@NewYears1978, coding is a subjective matter, we all have our opinions based upon our individual experiences, most of the time we do not notice the code speed on relatively small apps such as this, however if you were building something larger that ran to ten of thousand of row/ops you would notice a difference as the translator interacts with the default/renamed controls, it is simple, the shorter the code the faster it will work and this goes down to the base level of the character count in the code.
My experience leans very much in favour of @sintek's advice, especially wasting time bouncing back and forth to multiple modules, I have just concentrated on demonstrating with the multi-option filter there is no need for search routines, all your data is contained in the array populating your listbox, follow that process through you will see a row marker placed in the last column of the array, this gives you an instant access to your data row (no search procedures required).
If you keep the total app within the code envelope of one userform common references are carried through all subs by virtue of declaring your variable at the head immediately after Option Explicit - you will see such as 'tbl1row' populated from the listbox and carried to 'Update' thus eliminating searches.
To aid ease of navigation I have upload a PDF of the code as well - this gives a better view of flow/structure.

[NewYears1978]

Hey all!

@Sintek: I agree that building a new sheet using filtering was not a great option, but it was the only option at the time I found that did what I wanted. I looked at Torachan's stuff but had not found one with the searching functions. But the sample he's posted in this thread looks perfect actually! Thank you for your post Oh also, your sample was awesome. WAY better. This does exactly what I need, much like Torachan's example.

@XL-Dennis - Thanks for all the help, I did not even realize we had a commercial forum here, I will have to check that out I typically try to name all my controls and such and only more recently have been trying to use naming a lot more frequently and never leave anything their default values. (the file posted here is a bad example because I had
not renamed many things yet lol) Anyway, thanks again for your help and comments, I will likely start my file completely over pulling in previously written code that I need while leaving out bloat. I will have to start back at the start though if I am to use the VBA method for the comparison part of my form rather than Power Query. I am just not sure if that code can be written to run quickly on older machines. I will check out commercial forum.

@torachan - This is perfect, I really do like this a lot better. I had looked at your work in the other thread I Posted about something else (but same form I was working on) and I did not notice one that did search and listbox, I must have missed it. I will dive into this further, ONCE I get the old part of my VBA / Power Query code solved. Thank you very much! This looks so much cleaner!
The sample you posted only finds exact matches, is there a way to modify it to be contains instead of exact matches? So like if I type "test" and there are customers called "Test Customer" it would pop up? Same for Syspro and Web ID?
Also, is there a way to have the entire listbox populated when the form is open with all records like my form was doing or is this not recommended for speed or other reasons?

The reason I was having the listbox load all records instead of the dropdown is because the files will have 3,000 - 4,000 rows and having those in a dropbox, is not ideal
That's why I had it show all records in the listbox and then when a user searches the listbox would changes to show search results instead.

Otherwise this seems so much better. No need for extra sheet, much cleaner and faster!

Side note @sintek and @torachan - Are you guys suggesting that I should not create different modules for stuff? I always though this was good practice and helped me find code I was looking for. Haha. I am always learning and trying to streamline coding practices. I went through all torachan's code in his sample and it's so streamlined with very minimal charactesr, but also because of that it was very hard for me to understand a lot of it haha. But has me rethinking how I code I guess. I did not know character count mattered that much.

You're all the best, thanks for all the help. I will repost my long-winded VBA portion of this thread over in commercial area.

[XL-Dennis]

Good luck!

In professional development we usually use three logical tiers, business, data and presentation where the first is the bridge between the two others. This approach also control how we code and how we store all code.

The more we learn the better code we write. It's all about knowledge and experiences.

Later on I suggest you make a new post about how to retrieve data from database into Excel in VBA. I think it will be of general interest, especially when using slow computers 🙂

[torachan]

@NewYears1978, there is a time and place for a module/class module, rule of 'thumb' if the control is in the UserForm keep its code in the UserForm, modules have their usage at various levels, e.g. if a process is multi-called from several sources or in the case of the class module if you need to work with multiple controls all serving the same process.
From experience once a UserForm reaches 254kB it becomes unstable (however for some reason that I do not understand, I have been able to use a 6 page multipage embedded on a single userform well in excess of 500kB).
The combination of structures again is personal experience/choice, the problem is with greater flexibility comes greater choice, which I embrace, as one that had to work with confines of Cobol & Lisp back in the 60's.
As far as your listbox search is concerned my approach is if you are going to type in a search you already know the reference therefore there should be no need to filter, however if you have multiple records for one customer my method enables that search to appear in the listbox and it does not matter how disjointed the datasource is it will find its location when you update/delete.

[NewYears1978]

Good luck!

In professional development we usually use three logical tiers, business, data and presentation where the first is the bridge between the two others. This approach also control how we code and how we store all code.

The more we learn the better code we write. It's all about knowledge and experiences.

Later on I suggest you make a new post about how to retrieve data from database into Excel in VBA. I think it will be of general interest, especially when using slow computers 

Yeaaah we have no access to the databses that we use though. If we did, I wouldn't have had to make this form as it is haha. =D

@NewYears1978, there is a time and place for a module/class module, rule of 'thumb' if the control is in the UserForm keep its code in the UserForm, modules have their usage at various levels, e.g. if a process is multi-called from several sources or in the case of the class module if you need to work with multiple controls all serving the same process.
From experience once a UserForm reaches 254kB it becomes unstable (however for some reason that I do not understand, I have been able to use a 6 page multipage embedded on a single userform well in excess of 500kB).
The combination of structures again is personal experience/choice, the problem is with greater flexibility comes greater choice, which I embrace, as one that had to work with confines of Cobol & Lisp back in the 60's.
As far as your listbox search is concerned my approach is if you are going to type in a search you already know the reference therefore there should be no need to filter, however if you have multiple records for one customer my method enables that search to appear in the listbox and it does not matter how disjointed the datasource is it will find its location when you update/delete.

I love your approach and it works so fast, however won't work for what I am doing with this. I need the search to work with containing values. When the account manager is searching by Customer Name, they don't know the exact name in the databsae this is because customers can type their own info and sometimes they type it wrong, or different. (Updated post below on contains solution I came up with)

I originally asked and wanted for the listbox to be filled when the form was open, but after thinking about it with your approach, you're right - it's redundant. They already have the data in the sheet itself. So populating only searched values makes more sense!
So the only changes I really need to figure out are how to make the searches containing and not exact. The point of search is for them to find a wide variety of customers in a huge list of 3 to 4,000 records so I needed a containing match, at least for the Customer Name part. Maybe exact match for Syspro ID and Web ID is fine as if they are editing those they likely already have that info. But sometimes they are searching for a customer name that may be typed differently so they will not have this exact value. So if that could be contains and also not case sensitive that would be nice.

Also a side note, since these lists will be 3,000 to 4,000 records should that be populating in the dropbox? Is that too much for a dropbox?

One other issue isused my sample form and only took visible columns but not the hidden ones, so if I were to move that to my own file things would break, but I think I can manage to figure out what the code is doing and modify it to work for me
The file actually had columns A through AA while you've got A through R. But also I don't want to include all those in the userform. But I can see from your code how I can limit the ones shown so I think I can sort that out

For now, I have posted on Commercial forums to fix one of the main macros in my file and then I am going to start from scratch and try to streamline this whole file, eventually working back around to the userform. So maybe I can get your help then - much appreciated to all who have replied and helped me!

For the record I am not complaining about what you have done if it came off that way, you are amazing! I am so appreciative. Thank you again!




Update:
I changed a couple lines from

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

and that gave me contains, do you see anything wrong with using this method?

I am slowly going through all that code and learning how it works and how I can modify it once I get around to it. I won't need add/delete buttons, users will never add or delete records that's all done but the other macro that I have posted in the commercial forum (the one I mentioned a few posts back). The form is only to update the NOTES and Search for records. So the field on the left, do not need to be editable (that's why I had them locked before). All things I can fix up on my own, just posting updates for the sake of documenting my learning process haha. This form is going to work out great for me though once I get everything else done and add this in again

It feels so weird to me to have nothing named anything that makes any sense to me lol. I am so used to things being named for what they are, where they are, so that any time I need to go back and reference them it is easy.

[torachan]

many thanks for the feedback and added rep points - glad to have helped.
your search alteration is fine and I can not see any reason that it would cause any problems when file size becomes large.
the only thing I will point to is because it is not obvious at first sight is my use of an extra column in the array(this is important as it is what gives the search speed when finding the filtered row for updating).
Curiosity compels me to ask, does all the extra/hidden columns serve a purpose if they are not viewed ?

[NewYears1978]

@torachan - You are very welcome! The info provided about the extra array is helpful, thank you! I am sure when I get back to this again I may need your help, hehe The reason there are so many columns is because the exported file that each sales person gets has all the columns. In my finished form I had another userform where users could click checkboxes to show or hide the columns. The columns have to all be present on the initial import of the new file so that the macro that I talked about before (the one I posted in commercial about) will work.

It's pretty hard for me to explain in text. Basically the one sheet that has the database is like a running list that gets updated by another sheet that gets imported in. It works like a database in that old records (in first sheet, not second) are deleted and new records are added (not in first sheet but in second sheet) and any existing records that show up again, are updated (such as their email address, sales figures, etc)

So that's why all the columns must be there. Some users don't use some columns so they hide them (they would always delete them which would break the update macro, that's why I added code to prevent deleting columns and instead put in userform to show/hide columns)

Not sure if that was the best approach, but that's how I was doing it.

[torachan]

I now partially understand, my concern was if there is a vast amount of 'never used' data present just because it is part of the import then it has the potential of slowing searches, however this will show as you develop your app and can be explored at a later date. You are the best judge of this as you have lived with the process, we can only form our view from the detail here.
Good luck with your endeavours.

[NewYears1978]

Thank you

And yes, there is a bit of data that probably half the sales people do not use, but because of how our company is I have to use this report as it is and so I am making my form to accomodate. I would prefer to streamline and delete a bunch of those unused columns but I've been asked to leave all the columns there and just have them hideable.

Thanks again appreciate you!

[NewYears1978]

Hey @torachan - I have finished (well not finished, but getting there) updating my form and now I am trying to reimplement your code for the Userform (data entry) but I can't seem to get it to work with my 24 columns.

You can see below the code I changed (I commented out your lines and changed to mine) but I must have done something wrong. Help! I am adding a sample file.

All I did was change all the 18s to 24s and then I saw some where you have 19 (1 more than the column count 18) to 25. But I guess that was not correct =D
I also added in all the other labels and textboxes and named them accordingly (Label-24) and renamed the Texbox from 18 to 50 and changed textbox18 to textbox50 in the code. Etc.


Heeeeelp =D

I know I have all the seperate modules and code that you don't like but I had to keep it that way or I was getting lost. I hope to shorten and streamline it later =D

Hrm, I think I may have fixed it. ok it's ALMOST fixed but the notes1-4 are not right. if I use the update button it adds it to the data but when I load those records it's not loading the right thing...any tips?

Please Login or Register  to view this content.

[NewYears1978]

Ahhh I got it now. Sorry for spamming this thread lol. Solutions:

Let me know if you see anything wrong?

Please Login or Register  to view this content.

[torachan]

Hi, been a busy day or two, I will look at this over the weekend and post back here with any suggestions.

[torachan]

Basically O.K. - the only comment I make is purely my opinion/preference - If I produce UserForm interface solutions I do not allow manual intervention with the sheet therefore using WorkSheet_Change events are also a non-starter - the final process in the development of a app would be the sheet protection by a combination of sheet protect/unprotect events with the sheets in hidden state.

[NewYears1978]

Yeah the reason I add that is because users change the data and when the update is later run their changes get overwritten. I could use protect.
Was that the only issue? =D I think I have now finally finished it just need to clean up the code =D

I had one other question for you on something I could not figure out. Is it possible to select the row of the selected listbox item? So like when I do a search, and then I click a row to bring it up in the entry form - is there a way to pass that onto the actual data and select A1 of that row?

The reason is I have some complex macros that work around some Excel bugs, that open some code/links and I have to be in that row. Those macros are normally just run within the data itself - a user would select the row and then click the ribbon button to run that code and it runs the code based on the row selected. I want to add the same macros to the dataentry form.

Just to show you want they are, these are the macros that I am referring to:

Please Login or Register  to view this content.

I came close with the code below, but this will only select the row number related to the row number of the list box - ignoring any other rows not showing in the list box. I need it to account for rows that are hidden (After using search that is)
So if my database has 5 rows, and I search for something and it returns 2 rows in the list box. If I click row 2 in the listbox but that's actually row 5 in the database (sheet) I need to select row 5, but it will select row 2 since it's row 2 in the listbox.

Please Login or Register  to view this content.

Thanks so much for all your help!!!

[torachan]

refer back to post#25 - the answer is there - because the list is a fragmented portion of the original table you cannot use any of listbox properties as a direct row reference.
So what are the alternatives - you could introduce a separate (additional) search routine - however that introduces a functional overhead that will slow the app as the data source grows.
The simple method I have used - the process that develops the fragmented list to populate the listbox with filtered data adds a row counter as an extra array column which becomes 25th column in the listbox.
On selection you listbox row of data you automatically read the last element so eliminating any need for searches as you already know the row location.
Look through the listbox code you will see I have included a message box that identifies your selected row - you can remove the message box and merely use the row reference in your additional codes.

[NewYears1978]

Thanks my friend, late last night I got it all figured out (except for one minor issue where there is always one blank row at the end of the list box..but everytime I fixed that I broke something else - lol.

I think it's all working now though! Here was my final code

Please Login or Register  to view this content.

This worked for selecting the row of data in the sheet (although hidden since I turned off screen updating). It's really fast too, the only thing that take a second is UPDATING a record when there are 3000+ rows, but it's only like a few seconds.

Thanks for all the help

[torachan]

The reason the updating is slow is that you have turned screen updating off and do not turn it on until you close the form therefore the operation can not complete until you release the screen.
With screen updating turned on it is virtually instantaneous with 30,000 rows of dummy data.

I could not figure out the purpose of the code below, it appears to have no use within the app.

Please Login or Register  to view this content.

[NewYears1978]

Heya, Ahh I will turn back on screen updating. The reason I turned it off is because when clicking an item in the listbox the page jumps all around to that record (the code I added).

The function is what allows me to go to the cell record in the actual data, when clicking in the listbox. So even though I have searched and only show maybe 5 records, if I click the 5th record in the listbox it won't just go to the 5th record of data but the actual corresponding data in the database sheet. This was what I was asking about earlier.
Actually, that's not the code that did that.. I have no clue what this code is either...lol I disabled it and nothing happened. I must have added that in at some point when I was working on something that did not work and forgot to remove the function? I believe this was my first attempt to make clicking on a listbox item go to the cell row in the sheet. I was storing the value in an array..but I must have failed and forgot to remove the function. Lol.

I think I then ended up using this code instead

Please Login or Register  to view this content.

I should post my sample for you, I will do that tomorrow when I am back at the office.

As always, thanks for your help!

Update:
I did re-enable screen updating, that did not make the UPDATE button (only updates Notes, Notes2, Notes3, Notes4) go any faster, it only takes like 5 seconds though to update with 5,000 rows. I wonder if I did something else to slow it down. I can't upload the file tonight as the one I have has all the sensitive data and my dummy files are at work. I will upload it tomorrow morning =D