Excel magazine for the club - A simple warehouse program is needed

Hello.

I am looking for a simple macra written in Excel on the principle of a warehouse program.

1. First magazine - list of goods in the main warehouse. (Product name, index, unique name, model, serial number (may start from 0 np.00023445), quantity, user - main warehouse or user)
2. Second sheet - List of items (model, index number 2, serial number 1, serial number 2)
3. Third sheet - List of employees + place of work
4. Moving goods from the first and second sheets to individual employees and vice versa from the employee to warehouses. without limiting the amount.
5. List on the principle of a pivot table so that I could choose a given employee and it was nice to print what a given employee had downloaded.


Best wishes .

Jakubbukaj wrote:

I am looking for a simple macra written in Excel on the principle of a warehouse program.

Of course, professional solutions like https://weaversoft.pl/program-magazynowy/ (free version up to 100 articles) do not satisfy you

Hello.

I do not conceal that after analyzing a few programs I am not completely satisfied with their operation and service.
I will say yes, I even bought a license for the warehouse assistant program for PLN 663 and it did not meet my expectations to any extent.
After analyzing a nice macro written in excel by one electrode user (in the annex), I found that if it was a good conversion, it would be the best solution. Unfortunately my skills in excel are negligible and I can not do it alone, but recently I have started to discover the possibilities of excel science because I'm fascinated by its possibilities. Also if someone would have some free time and the will to help me, I would be grateful.


greetings

Jakubbukaj wrote:

after discovering the possibilities of excel, I start learning because I am fascinated by its possibilities

There are many real Excel and VBA masters in this forum. It is not known, however, if they will find time to prepare you a complete application dedicated to your needs

If you decide, however, to adapt yourself to the attached application - the best way to study intensively - you can count on a definite help in solving every problem you encounter in this forum

Start your buddy calmly from adapting to your needs dictionary , and the "Magazine" sheet (firstly, unblock cells in the sheet ) and try what will happen with the functionality of individual macros (see for yourself the "scientific" code of these macros - shortcut alt + F11)

Thank you for your interest and hints. Of course, I've already played a bit in this sheet as you mentioned.
The biggest problem is that even when you set your employee as a pallet place, I can assign only one item to one pallet place, and here a problem arises with the creation of a pivot table to show me what the employee has in stock, not to mention the image it somehow presented itself dignified to print At present, after two days of using excel I am proud of myself, I have already reached the stage of creating ready tables, charts for them and pivot tables.

I would like to organize the magazine as soon as possible so I hope that there will be some soul of good will which she would like to play with, but if it is not slow, I will try to get to it as much as possible by asking for advice from experienced forum members.

greetings

Jakubbukaj wrote:

after two days of using excel and I am proud of myself, I have already reached the stage of creating ready tables, charts for them and pivot tables

Congratulations it took me at least a few years

For starters, please note that this code and the entire application is not at all perfect - especially for the specific requirements of your application, e.g. it only covers the quantity register and pseudo assortment (in commercial practice the same product from another supplier or purchased at a different price - must be separately registered ), sheets "Shipment", "List", and macros that support these sheets, etc. are not needed in it

If you want to seriously approach this project, start by making a mock-up of your application - prepare designs for all sheets that should be included in it (main warehouse, records of goods issued to employees, etc.) - You know best what has to be there. They do not have to be "instrumented" in any formulas and macros in this model - but they will be the logical - substantive basis of the whole project. Each of these sheets with tables or functional forms try to describe - what they are to serve, what data will be included there and in what forms or macros will be used.

In a similar application of a commercial magazine, I created, apart from the basic "Magazyn" sheet, a number of other auxiliary ones, such as "Accounts" (suppliers and recipients), "Deposits", etc. I still have a model of that project from saved files.
2017-11...png Download (87.1 kB)

Someone will suggest how to edit the file from @Jakubbukaj, because there are a few errors, some things I would like to edit or throw away, entering alt + f11 opens VB but there, for example, I do not see the possibility of editing the first Menu tab. And does anyone know what the Version window is for and includes patterns? what do they give? also how to edit / delete.

What's your problem with editing this file? Unpack the zip file and run.

tabs are probably blocked by VB, I wanted to edit them, I did not play with VB, only with python, so asking for directions how to run to see the code and edit individual tabs and things that I think have bugs / are redundant

You go to the "Review" tab and click "Do not protect the sheet" (no password).

gienek1939 wrote:

entering alt + f11 opens VB but there, for example, I do not see the possibility of editing the first Menu tab

This entire storage file solution is based on the so-called sheet forms and several macros
2020-09...png Download (62.96 kB)
which are assigned to individual buttons in these forms
It is not enough to unlock a sheet and modify it freely because you have to analyze everything if the change does not affect the names used in the file and other macros

So it's a big deal, there are so many macros .... I did as you say and only 1 wonder how to edit everything that is in the Menu sheet? is everything macros there? because when I click on macros, it sometimes does not transfer to other sheets and I want to edit this Menu.

gienek1939 wrote:

just me 1 wondering how to edit everything that is in the menu sheet

You can edit and change entire sheets (including this Menu) after they are unlocked (these sheets ). After unlocking the sheet, in the Excel tab Developer you press (turn on ) Design mode , you select RMB the button or other object you are interested in and from the tool header you press View code
This way you can view the VBA code assigned to each button (or other object)
Almost all of these codes are stored in module 1 and you can modify them all with the MVBA editor adapt to your needs
However, I suggest at the beginning read some basics about programming in VBA, and then after each elementary change make copies of the file version and thoroughly test if your modification did not blow up the "code" - if so, you restore the last working version and keep trying

I am asking for more data for the warehouse management program from the user PROT, who in response # 6 09 Nov 2017 23:30 sent a section of the table with formulas in excel. I would appreciate your help and sharing the entire table.

awiecek111 wrote:

I am asking for more data for the warehouse management program from the PROT user, who in response # 6 09 Nov 2017 23:30 sent a section of the table

I could send you the whole file, buddy but you will have no use for it As you can see in this screenshot, it operates on files there type xlm - these are the old macro sheet formats that were used in the times Excel 5.0 :D

If you have this version of Excel, I can send you such a file, but on modern versions of Office these macros will no longer work
In post # 6, I only illustrated a methodical approach to building such an application in Excel

Prot wrote:

awiecek111 wrote:

I am asking for more data for the warehouse management program from the PROT user, who in response # 6 09 Nov 2017 23:30 sent a section of the table

I could send you the whole file, buddy but you will have no use for it As you can see in this screenshot, it operates on files there type xlm - these are the old macro sheet formats that were used in the times Excel 5.0 :D

If you have this version of Excel, I can send you such a file, but on modern versions of Office these macros will no longer work
In post # 6, I only illustrated a methodical approach to building such an application in Excel

I warmly welcome.
Please send me the entire file. If you had anything else related to warehouse management, I would be grateful. I can deal with the subject of the old Excel.

Thank you in advance.

awiecek111 wrote:

I can deal with the subject of the old Exel.

Congratulations on your well-being I hope. that you are already after reading the publications on certain dangers related to this type of macros (eg. A serious flaw in MS Excel ), and the differences in the structure of functions and macros in successive versions of Excel

awiecek111 wrote:

Please send me the entire file.

If you want to watch a bit (as it used to be with macros ) then I attach the aforementioned file - macro sheet in Excel 4.0 version.
Here you can see the syntax of commands, macro functions, and specific rules for building dialog boxes (now called forms ) etc.
Unfortunately, for probably obvious reasons, I cannot share any of the several xls files containing individual databases - therefore, even if you run macros, you will quickly encounter a whole string of errors resulting from the lack of connections with these databases.
PROT.zip Download (51.83 kB)Points: 3.5 for user