Simple small database to hold companies wire and cabling info

**h0b02000** · 03-10-2020, 06:13 PM

Hello all,

Warning, I'm fairly new to this stuff so you'll likely get annoyed with my dumb questions. I'm having some weird problems with my UserForm and sheets. I'm making a simple small database to hold all my companies wire and cabling info and to generate orders through simple clicking the entry.

I've had so many problems so far and to be honest I seem to fix some stuff then somehow it doesnt work the next time I do it. I likely change a small property or something on the userform or in the excel sheet and dont realize I did so.

Anyways my problem is this: I have 3 Listboxs, im working on 2 right now, I use 1 to pull all the master list of entries so the user can select it and add it to the Order list (2nd list box). I populate stuff fine and had so many issues with my dynamic ranges so far but that's another post.

Right now I was using the dblclick on the list event to add the selected entry to my orderlist with the following code:

Please Login or Register  to view this content.

Oddly this was working perfectly a day ago and I didnt' back up after this until i messed it up. I can add files and stuff if it helps as well.

The error I get is "Could not get the List property. Invalid argument." but never had this a day ago and it worked 50 times at random times. I understand this is something to do with addressing or referencing the tables or lists but I can't figure it out.

Thanks so much!

**torachan** · 03-16-2020, 05:02 AM

Demo of using multi-select listbox to fill your order listbox.
Select/de-select in Master List adds and removes items without resorting to extra command buttons.
So that your database table is contiguous I have removed section headings and blank lines.
To deal with separate sections I have added a column 'Group'.
As you develop your app. you would have found file handling unpredictable with your existing database format.
Sorry I had to cut down sizing to see everything on the screen of my old steam driven computer.
Hope the attached is of some assistance.
torachan.

**h0b02000** · 03-16-2020, 04:20 PM

Thank you for helping me torachan I appreciate your time here. I do also like what you did with the efficiency of the listbox and how adding it is just dbl clicking the entry and it keeps it selected to show what youve added, really like that. I also will do away with add entry button. I have been working on this for the last week since I posted and I got pretty far myself once again. I have changed a few things but the one thing I left for last was the adding entry thing and you just helped me tons!

I do need to keep certain aspects of the layout as Im just a pleb and I take my orders from "much smarter people" haha.
For now I have made everything into dynamic ranges using offset incase it gets bigger or smaller. I have options to select types and all that so it's not a giant list of stuff to go through.

Ill add it here in case you wanted to review stuff i've done since, don't feel obligated youve done enough already!

Thanks again for the help torachan!

PS. I did run into one problem while trying to use your method with my filtered lists in the new one I attached. Once the filter switches ranges from say Single Wires to Multicables the main list will change and reset the selections. This then resets the counter to the first row again and over writes the order data from row 1 down again. I'll poke at it when I can unless you see a quick fix. Maybe I'll do some sort of Do Until I hit the clear button to clear the order form.

**torachan** · 03-17-2020, 04:15 AM

I will watch your progress with interest.
Unfortunately "the smarter people" will make your life extremely frustrating if they insist upon the 'pretty' layout you have at the moment.
The sole purpose of a 'database' is to contain data, not, to be graphically pleasing to the eye, to do this efficiently there has to be structure, the simpler the better.
In the event of using a 'proper database' the developers do not allow you near the 'storage structure' for a very good reason.
I note you mention using 'offset' & 'dynamic ranges' looking at the 'name manager' you have 22 ranges to maintain ????
Now look at the 'name manager' in my sample, it has one 'name' and that is a 'Table' which by its structure is self-maintaining (it expands/contracts automatically).
Lastly you mention selection by 'double clicking' this was not the case in my submission, the choice is made by the 'mouse-up' event, there is a very subtle reason for this.
Whilst the mouse is held key-down you can scroll the choices without making the scroll a separate operation, the choice is then made on release of mouse-key.
All small points that add up to time saving, less clunky app.
torachan

**h0b02000** · 03-18-2020, 08:36 PM

Hey torachan, I agree with what youre saying but I can also give you good reasons for most (not all) of these decisions.

The reason we cant have a giant list of all wires to choose from is because this is such a small part of the inventory when it's all put into the spreadsheet it will be 10X or longer than it is now, and to make it usable for everyone at the shop it has to be easily navigable. Another reason for the separation is we're having tons of issues with people ordering wrong types and voltages and other problems with them ordering so this allows for the easy ENTRY of new entries to the master list thru picking exact options from the sorting list. It is also a matter of code that we use the correct wires in certain applications. Eventually the ranges will be dynamically linked together so that you can only pick specific options for certain selections, eg: If I pick 300V wires, it would only allow me to pick from the 2-3 specific suppliers of that type, whereas we have 50 vendors of wire to sort through with one big sheet.
Secondly, the reason I have it set up this way is because there is no direct access to the database and only through the userform, to do this it has to be able to add entries to the master list and modify the order directly which is why you can add and delete from the master list and create new entries but not modify existing ones, unless you have the passowrd to do so which will also be eventually set up lol.
As for the mouse up thing, I must have not seen that correctly I do now however, and it makes sense why you do it that way.

I hope some of these reasons arent too rediculous and make some sense.

Thanks!

**torachan** · 03-19-2020, 11:37 AM

Hi.
I can only advise from my extensive experience (i am of an age where I have forgotten more than I have learned)

I only started contributing to the forum after I retired and sought cerebral challenges as my physical prowess waned.
Although I have been programming since the mid '60's and using spreadsheets since the advent of Visi-Calc.
As you will realise as you battle with filtering your data storage structure will become your biggest obstacle.
I have thrown together another form which does the primary filters on a number of primary keys before loading the selection listbox.
With a bit more advanced coding you could do your filtering with cascading combos (this requires the search order to be dictated first).
The strength of Excel is its weakness, you can do so much with it and everybody has their favorite methods.
Best of Luck.
torachan.

second upload has a line of code altered to ensure order sheet is positively selected before process is started.