De Premor, I like your solution. This is an opportunity for me to learn new tips and polish my VBA skills. I will attempt to combine yours and Paul's; Next step would be to try to 'merge' my userforms. Instead of having 1 userform for data entry and another for search,I am thinking of using: a multipage userform or a single userform that will expand.
I will post an updated file later Once again thank y'all for your help. I really appreciated it. On Fri, Oct 17, 2014 at 12:41 AM, De Premor <d...@premor.net> wrote: > Hi All, > > I've writen a code for this case, i use a junk sheet for removing > duplicate and sorting, it seem more easy to use, much faster, flexyble, and > cut off unneeded code > > what i've done is on form initialize is : copy date to junk sheet, remove > the duplicate, sort it, and place it on combo > next, i do the same thing for showing the result data by using filter > feature from excel before it. > > PFA to have fun :D > > Rgds, > [dp] > > > On 16/10/2014 23:37, Paul Schreiner wrote: > > Here's my original version. > > the technique I used made use of the Initialize event for the userform > and the change events for the beginning/end date comboboxes. > > The other significant thing is the use of a Dictionary object. > I was introduced to this one several years ago. > > It's like having a two-dimensional array with a built-in unique key. > I've loaded over 100,000 records into the Dictionary Object, > using a document number as the key. > Then, using the Dict_Oject.exists method, you can check the entire record > set and IMMEDIATELY determine if the record is in the dictionary! > (without the need to loop from lbound to ubound of an array) > > So, once I load all the unique keys, I use: > > dArray = Dict_Oject.keys > to convert the keys to an array. > > I sort this array and use it to load the first combobox. > > I used the change event of the first combobox to load the second. > since the records are in order, > I started with the selected item of combobox1 and copied it and each > subsequent value to combobox2. > > Also, since you only have these two criteria to determine which records > get put into the listbox, and you can only select combobox2 after you've > made a selection in combobox1, then I decided to use the change event of > combobox2 to run the search (instead of requiring the use of a button). > > Something else I used that is especially nice while debugging a userform > is that I used vbModeless when using the .Show method. > > by default, a userform is displayed as "modal". > What that means is that it is tied closely to the workbook/worksheet and > macros. > Which means that, while the userform is displayed, you cannot make changes > to the worksheet or for the most part, the VBA code. > (there are some exceptions) > > but, by using: Userform1.Show vbModeLess > The userform is "free floating". > you can change the values on the worksheet and update the VBA macros while > the userform is displayed, then continue to test the userform. > > Now, you might not want to use this feature when you "go live", but it's > really great to use while testing and debugging. > > something else you might see periodically in my code is the use of > Debug.print and Debug.Assert > > sometimes, I need to see what the value of certain variables are while > debugging. > Debug.Print will allow you to print the value of variables to the > "Immediate" window in the VBA Editor. > > Debug.Assert lets you set a "breakpoint" in the editor for when something > specific happens. > > Say you're looping through 10,000 rows of data and you want to stop when a > specific row number or cell value is reached. > > You can use something like: > If (nRow = 6250) then debug.assert false > > When it reaches here, you can then step through the code. > > I also have been known to use a Flag_Debug variable. > > Then, in various places, I put: > > If (Flag_Debug) then Debug.Assert False > > Then, somewhere I can set the flag to "true" and my program will stop at > the Debug.Assert > > (I've been known to put a "debug" checkbox on a userform that only > displays if I am the one running the userform) > > Lots of tricks, but most won't be useful until you have a need for them. > > I've written and currently maintain over 120,000 lines of vba code in > 12-14 applications (workbooks). > But if you were to look at my early work, I know they would look juvenile. > We're always growing. Even the ones that don't think they have anything > else to learn! > > take a look and let me know how I can help. > > > *Paul* > ----------------------------------------- > > > > > > > > *“Do all the good you can, By all the means you can, In all the ways you > can, In all the places you can, At all the times you can, To all the people > you can, As long as ever you can.” - John Wesley * > ----------------------------------------- > > > -- > Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s > =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ > https://www.facebook.com/discussexcel > > FORUM RULES > > 1) Use concise, accurate thread titles. Poor thread titles, like Please > Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice > will not get quick attention or may not be answered. > 2) Don't post a question in the thread of another member. > 3) Don't post questions regarding breaking or bypassing any security > measure. > 4) Acknowledge the responses you receive, good or bad. > 5) Jobs posting is not allowed. > 6) Sharing copyrighted material and their links is not allowed. > > NOTE : Don't ever post confidential data in a workbook. Forum owners and > members are not responsible for any loss. > --- > You received this message because you are subscribed to a topic in the > Google Groups "MS EXCEL AND VBA MACROS" group. > To unsubscribe from this topic, visit > https://groups.google.com/d/topic/excel-macros/9KyrVJsLkUs/unsubscribe. > To unsubscribe from this group and all its topics, send an email to > excel-macros+unsubscr...@googlegroups.com. > To post to this group, send email to excel-macros@googlegroups.com. > Visit this group at http://groups.google.com/group/excel-macros. > For more options, visit https://groups.google.com/d/optout. > -- To be or not to be that is the IF THEN ELSE END IF of the Question -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
