Help Required for Stores Inventory VBA

[MS13RRY]

Hello everyone,

Is there someone who could help me with a stock inventory?

I am trying to do an in-out inventory with quantities for each item with database.

[torachan]

As you are prepared to use macros/VBA is there any reason that you would be adverse to using a VBA UserForm rather than on sheet - it will give you far simpler data/code management and eliminate any need for on sheet formula.
Attached is a typical UserForm doing a similar process - this is far more complex than your initial requirement but illustrates the versatility that a userform gives you.

[MS13RRY]

Very good, but still would like the vba code to achieve my original aim.

Many Thanks

[torachan]

I take it your original aim is to have a simple in/out inventory, my aim was to make that as simple process as possible by offering guidance based upon years of experience and save you the heartache of problems I can see already with your approach.
Based upon what I can already see, your app has formula in it that will with more data added make the app progressively slower, with any addition/deletion to any sheet all calculations/functions are actioned.
Could you explain the purpose of the sheet 'Dynamic' it just seems to be a sheet of replicated data serving no purpose that I can understand, the basis of any efficient data handling is to keep it as simple as possible with minimum amount of replication.
Back to the initial question, are you prepared to use a bespoke userform or have you committed yourself to entering data via the sheet form.

[MS13RRY]

Thank you torachan,

I appreciate your help, i really do.
the dynamic sheet i used to auto filter item codes in the in-out sheet per line of entries.
if you see each column in dynamic links to a row in in-out.
i am leaning towards a bespoke userform but with all the formulas etc i am not sure about what the results will be if i change from entering data via sheet form.

[torachan]

I will do my best to put an example together for you by tomorrow afternoon.
forget your formula - there is no need for their hinderance.

[torachan]

sorry a bit late, I forgot I had a medical check today, nearly finished, be done by tomorrow morning.

[torachan]

Give the attached userform a trial - it is 'bare bones' at the moment as a framework to build on.
So that data entry is consistent I have included a user page for raw data input - to add data first press 'CLEAR' then input data and press 'ADD' - to edit data use search box and select from listbox - edit data and 'UPDATE' or 'DELETE'.
The search comboboxes are also 'typein' when your data becomes large this can be faster than selecting from a jumbled list (a sort routine is needed when the primary search key field is decided).
Have a play around with it and if you are convinced it is the way forward we can incorporate improvements/additions as required.
The simple report should be self explanatory - it is just produced for individual parts transactions between dates - this can be extended if you allocate to (customers/suppliers/product build etc.,)
Note the sheets are basic 'structured tables' - there are no on-sheet formula/data validation or conditional formatting to bother about.

[MS13RRY]

Hi torachan,

Been looking at and testing out your sheet with userform.. great work by the way.
I have started and working on a new workbook, after your advise on a userform option which has my brain cogs working flat out.

i did try reading your code and tried to understand it with my very little knowledge i have at the moment, i struggled to follow all of it.

I think i have done ok on my new workbook but don't know yet how to master the report and printing of said report as of yet.

I will send you what i have done to look at,

Cheers

[torachan]

I have had an in depth look through you app and I would be doing you a disservice if I just 'cobbled' together a means of extracting a report.
The app is far too busy - with on sheet formula firing at every change (with the action contained in the code it is restricted to bare minimum (the values that are changing)) - with 'database prinicipals' you only record the data once and couple to it by using 'keys' - other than your 'DashBoard' (frontend) if you have Product Master Database this can accommodate both your 'Inventory' & @ 'Pricing' - then an 'Audit Trial' sheet that records every change to the 'Master' it only requires the part number as the primary key - then columns aligned with the changing field.
As this app appears to be the makings of a tool you & your company would be relying on for ongoing control it is necessary to have it as 'bug' free as possible, so I can only give advice from experience, lastly through experience I never now put buttons (including ActiveX) on a sheet ((with the exception of a launch button) and this is always totally anchored with the cell boundaries, there is nothing as frustrating as a 'graphics shape' migrating off the visible sheet.
A couple of links below that can be very useful for development and troubleshooting.

https://www.snb-vba.eu/VBA_Fill_comb...istbox_en.html

https://www.thespreadsheetguru.com/l...ct-tables-vba/

[torachan]

@MS13RRY, As my PM, please try the attached, it gives me another avenue to approach Bill of Materials pick.
As you select from the multichoice listbox it populates the list view, after all choices are made you can slow double click on any quantity number and edit it (exploiting the quirk of the listview in that the first column is editable), after all quanties have been decided click on the quantity column header, this will update the changes and you can then print/save.

[torachan]

As second PM a more complex approach to multilevel assembly.

[MS13RRY]

Example of my basic bom pick form as mentioned in my PM

[torachan]

Referring back to post#10, I have put together an attempt to satisfy your 'visual dashboard' approach.
There are several points I must explain so that if you go forward with this project a concept is adhered to.
There are no on-sheet formula (the sheets are merely data stores populated by code actions) - on initial setup if you decide on a 'back door' approach for loading data 'en-masse', this must be values only - no embedded formula.
The two data stores (Movement & ProductMaster) are hidden sheets to prevent manual interference when the app is in operational use.
The buttons are loaded from code in the WorkBook_Open event - this is an attempt to ensure they stay anchored to 'A2' - if you manually adjust the column widths on the 'DashBoard' the buttons will change width, this is not permanent, so no need to adjust as they are resized every time the workbook is opened - any manual alteration to the 'DashBoard' sheet is not permnent as this is display only and is refreshed by the 'Current Stock' button.
Hopefully the 'Report Generator' gives enough info - at present I will leave the 'BOM Builder' until I hear back if you are proceeding further or whatever? If you use this I will finally annotate the code for better understanding.
Finally I have attached a PDF 'dump' of the code from 'VbaCodePrint' it is easier to see the flow - my code is not indented as I type it using word processor (old habit - easier for me to edit(copy/paste) etc.).
See if you can 'crash & trash' it so that trouble shooting is done early.

[torachan]

Updated file with glitches removed and as requested by your P.M. a page now with some basic charts.
There is now a lot of repetitious code which I will later condense.

[MS13RRY]

I have added the formula to re-order required on product master,
If this can be added to vba that would be preferred

Inventory Management for Stores (tora(v3)) with formula added for yes-no.xlsm

[torachan]

Update version#7, the edit all items keeps an old/new log, this can be interrogated via the userform by selecting the date range and options.
The updated price history also goes to this file and is also available as a comparison graph on the Graphics2 sheet.
Give it a trial and after any editing I will finally annotate the end result.