Accounting for the sale of finished products Excel spreadsheet. Warehouse accounting in Excel. A simple program for automating warehouse accounting

Today there is a large selection of specialized tools for maintaining warehouse accounting: from unpretentious free programs to expensive full-featured WMS systems. Despite this, many entrepreneurs initial stage select Excel spreadsheets for inventory management. At first, this seems like an affordable and convenient solution, but as the number of orders in your organization grows, using Excel for inventory tracking is fraught with many problems. The more storage facilities The more items, the higher the probability of their occurrence. What difficulties do users face when working with Excel?

  • Data entry errors. Due to a single input error, it becomes necessary to double-check all the data in the tables.
  • Inconvenience joint work. Often a situation arises when, when editing data by one user, they are overwritten by another. Thus, accounting in Excel may not be correct.
  • Performing many manual operations. Excel program not designed as a warehouse management solution, the program combines many basic functions, and therefore often you will have to spend time on a monotonous manual labor.
  • Difficulty in validating data. When using Excel, a lot of effort goes into checking the correctness of the entered data and bringing the information into the appropriate form. If you have a large warehouse, it is better to download Excel for other tasks, and keep inventory records using more advanced programs.

These problems may not occur so often, however, they take a lot of work time. Warehouse program in Excel Corrections of errors and typos, lengthy checking of the correctness of balance data and forced manual labor when working with Excel are the main reasons why entrepreneurs eventually come to use specialized warehouse accounting solutions. Even experienced professionals who know how to keep inventory in Excel still choose other Excel-based programs or completely new solutions.

How to keep inventory records in Excel?

Whether you choose Excel spreadsheets, MySklad or another specialized retail solution, there are a few basic principles to follow. If you download the program and do not use special rules, over time, work with accounting will become more complicated at times:

  1. Correct design of reference books. A directory with unmarked goods creates guaranteed accounting problems, inconvenience to the seller and difficulties in reporting. When entering data, it is necessary to fill in as much information as possible in various cells (for example, codes, articles, prices, VAT). If the inventory of the sold goods in the warehouse is carried out using the MySklad service, all the necessary data on the goods are automatically inserted into the documents, which saves a lot of time when processing orders.
  2. Entering initial balances in quantitative and total terms. Correct data at the beginning of work will give adequate reports in the process of work, and those who make sales with " clean slate' should not be counted on.
  3. Compliance with chronology in the preparation of documents. It is necessary to register the arrival of goods at the warehouse from the supplier strictly before it is shipped to the buyer. Otherwise, the cost price will “go” and you will not be able to accurately calculate how much you have earned. In MySklad for each purchase and sale of goods, you can specify an arbitrary date.
  4. Structured storage of additional information. When maintaining warehouse records, it is important to collect additional information about sales (for example, method of delivery and payment, date of shipment, customer name, manager name) in separate fields. All this data will be useful in the future for compiling route sheets for couriers, calculating salaries for managers, and analyzing statistics. In MySklad, you can easily and without programming create required amount free fields for use in directories and documents.

Users of the Internet service MySklad have access to free support by phone and e-mail. Support staff not only help with the program, but also share tips on optimal inventory management and setting up basic business processes. Download the free version and try our new solutions.

Inventory management in Excel is suitable for any trade or production organization, where it is important to take into account the amount of raw materials and materials, finished products. To this end, the company maintains inventory records. Large firms tend to buy turnkey solutions for accounting in in electronic format. Today there are a lot of options for various directions activities.

Small businesses control the movement of goods on their own. For this purpose, you can use Excel spreadsheets. The functionality of this tool is quite enough. Let's get acquainted with some of the possibilities and independently create our own warehouse accounting program in Excel.

At the end of the article, you can, which is analyzed and described here.

How to keep inventory records in Excel?

Any custom warehouse management solution, whether built on your own or purchased, will only work well if you follow the basic rules. If you neglect these principles at the beginning, then later the work will become more complicated.

  1. Fill in the handbooks as accurately and as accurately as possible. If this is a nomenclature of goods, then it is necessary to enter not only the names and quantities. For correct accounting, you will need codes, articles, expiration dates (for individual industries and trade enterprises), etc.
  2. Initial balances are entered in quantitative and monetary terms. It makes sense to conduct an inventory before filling in the relevant tables.
  3. Observe chronology in the registration of transactions. Data on the receipt of products at the warehouse should be entered earlier than on the shipment of goods to the buyer.
  4. Do not disdain additional information. To draw up a route sheet, the driver needs the date of shipment and the name of the customer. For accounting, the method of payment. Every organization has its own characteristics. A number of data entered into the warehouse accounting program in Excel is useful for statistical reports, accruals wages specialists, etc.

It is impossible to unequivocally answer the question of how to keep inventory records in Excel. It is necessary to take into account the specifics of a particular enterprise, warehouse, goods. But we can draw general recommendations:

  1. For correct warehouse accounting in Excel, you need to create directories. They can take 1-3 sheets. This is the directory "Suppliers", "Buyers", "Points of accounting for goods." AT small organization, where there are not so many counterparties, directories are not needed. It is also not necessary to compile a list of goods accounting points if the enterprise has only one warehouse and / or one store.
  2. With a relatively constant list of products, it makes sense to make the product range in the form of a database. Subsequently, income, expenses and reports should be filled in with references to the nomenclature. The "Nomenclature" sheet may contain the name of the product, product groups, product codes, units of measurement, etc.
  3. The receipt of goods at the warehouse is recorded on the "Incoming" sheet. Retirement - "Expense". The current state is “Remainders” (“Reserve”).
  4. Results, the report is generated using the PivotTable tool.

So that the headings of each warehouse accounting table do not run away, it makes sense to fix them. This is done on the "View" tab using the "Freeze Areas" button.

Now, regardless of the number of entries, the user will see the column headings.



Excel table "Warehouse accounting"

Let's look at an example of how the warehouse accounting program in Excel should work.

We make Handbooks.

For supplier data:


* Shape may vary.

For customer data:


* Please note: the title bar is pinned. Therefore, you can enter as much data as you like. The column names will be visible.

For auditing points of issue of goods:


Once again, it makes sense to create such directories if the enterprise is large or medium.

You can make a product range on a separate sheet:


In this example, we will use drop-down lists in the table for warehouse accounting. Therefore, Directories and Nomenclature are needed: we will make references to them.

Name the range of the "Nomenclature" table: "Table1". To do this, select the range of the table and enter the appropriate value in the name field (opposite the formula bar). You also need to assign a name: "Table2" to the range of the "Suppliers" table. This will allow you to conveniently refer to their values.

To fix income and expenditure transactions, we fill out two separate sheets.

We make a hat for the "Arrival":

Next stage - table automation! It is necessary to make it so that the user selects the name of the product, supplier, accounting point from the ready list. The vendor code and unit of measure should be displayed automatically. Date, invoice number, quantity and price are entered manually. Excel calculates the cost.

Let's start solving the problem. First, let's format all directories as tables. This is necessary so that later you can add something, change it.

Create a dropdown list for the Name column. Select a column (without a header). Go to the "Data" tab - the "Data Validation" tool.

In the "Data type" field, select "List". An additional field "Source" immediately appears. To make the values ​​for the drop-down list taken from another sheet, use the function: =INDIRECT("item!$A$4:$A$8").

Now, when filling in the first column of the table, you can select the name of the product from the list.

Automatically in the column "Unit. rev.» the corresponding value should appear. We will do it using the VLOOKUP and UND functions (it will suppress the error as a result of the VLOOKUP function when referring to an empty cell in the first column). Formula: .

By the same principle, we make a drop-down list and autocomplete for the "Supplier" and "Code" columns.

We also form a drop-down list for the "Accounting point" - where the incoming goods were sent. To fill in the "Cost" column, we use the multiplication formula (= price * quantity).

We form the table "Consumption of goods".


Drop-down lists are used in the columns "Name", "Point of accounting for shipment, delivery", "Buyer". Units of measurement and cost are filled in automatically using formulas.

We make a "turnover sheet" ("Results").

We set zeros at the beginning of the period, because warehouse accounting is just beginning to be conducted. If previously conducted, then in this column there will be balances. Names and units of measurement are taken from the nomenclature of goods.

The columns "Receipt" and "Shipments" are filled using the SUMIFS function. The remainders are calculated using mathematical operators.

Download the warehouse accounting program ( finished example drawn up as described above).


Here is the completed program.

However, when filling out incoming or outgoing documents, some values ​​\u200b\u200bwill still have to be scored by hand.

These include:

  • document date;
  • quantity of goods;
  • Document Number;
  • other frequently changing parameters.

Using the tools built into Excel, you can achieve automatic filling of several columns based on the reference data. However, it is unlikely that you will be able to configure this functionality yourself without deep knowledge of the program.

You can add elementary formulas yourself, for example, specify a column with the total cost of goods by multiplying the quantity by the price.

In the above way, the sheets “Income” and “Expense” are also formed. Maintaining them separately is convenient for later quick search the required document.

Formation of the turnover sheet

You can make a turnover sheet yourself, but you will have to understand some of the functions of the spreadsheet editor. Programming skills are not required here.

On a separate sheet, you can draw up information about product balances in order to understand the need to purchase a particular item in the assortment.

Tracking Critical Remains

Keeping records of goods in the store in Excel can be configured in such a way that information is displayed on the sheet with product balances about the need to purchase a particular item in the assortment. Next will be considered the simplest example how to format it in a spreadsheet editor.

In the proposed example, there are three storage locations for goods, indicating the balances in each of them. Using the IF(OR…) function, you can set up an automatic check of stock quota compliance for each warehouse. The final formula will look like this:

IF(OR(C3<3;D3<3;E3<3);«Необходимо пополнение склада»;«Товара достаточно»).

Thus, if the balance in any storage location falls below three units, the user will see a message about the need to purchase or internally move the goods. In the same way, you can monitor the reverse process - packing with a certain nomenclature item. The formula used is very simple and can always be added to a finished spreadsheet.

The specified method of monitoring values ​​is applicable not only to warehouse balances. Similarly, the following indicators can be analyzed:

  • volumes of work performed or sales by employees;
  • increase in gross turnover or profit;
  • identification of trade declines in certain groups of goods and other indicators.

Excel can provide entrepreneurs with many analytical tools when accounting for retail sales. One of its difficulties lies in the small amount of professional training material and the need to independently compose all the formulas.

Advantages of accounting for goods in a store in Excel

Try all the features of the EKAM platform for free

Warehouse management program

  • Online cash desk under 54-FZ

Email*

Email*

try

Warehouse management program

  • Setting up automation of accounting for goods on a turnkey basis
  • Write-off of balances in real time
  • Accounting for purchases and orders to suppliers
  • Built-in loyalty program
  • Online cash desk under 54-FZ

We provide prompt telephone support
we help to load the commodity base and register the cash register.

Try all the features for free!

Email*

Email*

Get access

Privacy agreement

and processing of personal data

1. General Provisions

1.1. This agreement on confidentiality and processing of personal data (hereinafter referred to as the Agreement) is accepted freely and by its own will, applies to all information that Insales Rus LLC and / or its affiliates, including all persons belonging to the same group with LLC "Insales Rus" (including "EKAM Service" LLC) can obtain about the User during the use of any of the sites, services, services, computer programs, products or services of "Insales Rus" LLC (hereinafter referred to as the "Services") and in during the execution of Insales Rus LLC of any agreements and contracts with the User. The User's consent to the Agreement, expressed by him in the framework of relations with one of the listed persons, applies to all other listed persons.

1.2. The use of the Services means the User's consent to this Agreement and the conditions specified therein; in case of disagreement with these conditions, the User must refrain from using the Services.

"Insales"- Limited Liability Company "Insales Rus", PSRN 1117746506514, TIN 7714843760, KPP 771401001, registered at the address: 125319, Moscow, Akademika Ilyushin St., 4, building 1, office 11 (hereinafter referred to as "Insales" ), on the one hand, and

"User" -

or an individual who has legal capacity and is recognized as a participant in civil legal relations in accordance with the legislation of the Russian Federation;

or a legal entity registered in accordance with the laws of the state of which such entity is a resident;

or an individual entrepreneur registered in accordance with the legislation of the state of which such person is a resident;

which has accepted the terms of this Agreement.

1.4. For the purposes of this Agreement, the Parties have determined that confidential information is information of any nature (production, technical, economic, organizational and others), including the results of intellectual activity, as well as information about the methods of carrying out professional activities (including, but not limited to: information about products, works and services; information about technologies and research works; data on technical systems and equipment, including software elements; business forecasts and information about proposed purchases; requirements and specifications of specific partners and potential partners; information, relating to intellectual property, as well as plans and technologies related to all of the above) communicated by one party to the other party in written and / or electronic form, expressly designated by the Party as its confidential information.

1.5. The purpose of this Agreement is to protect confidential information that the Parties will exchange during negotiations, conclusion of contracts and fulfillment of obligations, as well as any other interaction (including, but not limited to, consulting, requesting and providing information, and performing other assignments).

2.Obligations of the Parties

2.1. The Parties agree to keep secret all confidential information received by one Party from the other Party during the interaction of the Parties, not to disclose, disclose, make public or otherwise provide such information to any third party without the prior written permission of the other Party, with the exception of cases specified in the current legislation, when the provision of such information is the responsibility of the Parties.

2.2. Each Party will take all necessary measures to protect confidential information at least with the same measures that the Party applies to protect its own confidential information. Access to confidential information is provided only to those employees of each of the Parties who reasonably need it to perform their official duties for the implementation of this Agreement.

2.3. The obligation to keep secret confidential information is valid within the term of this Agreement, the license agreement for computer programs dated 12/01/2016, the agreement of accession to the license agreement for computer programs, agency and other agreements and within five years after termination their actions, unless otherwise agreed by the Parties.

(a) if the information provided has become publicly available without violating the obligations of one of the Parties;

(b) if the information provided became known to the Party as a result of its own research, systematic observations or other activities carried out without the use of confidential information received from the other Party;

(c) if the information provided is lawfully obtained from a third party without an obligation to keep it secret until it is provided by one of the Parties;

(d) if the information is provided at the written request of a public authority, other state authority, or local government in order to perform their functions, and its disclosure to these authorities is mandatory for the Party. In this case, the Party must immediately notify the other Party of the request received;

(e) if the information is provided to a third party with the consent of the Party about which the information is being transferred.

2.5. Insales does not verify the accuracy of the information provided by the User, and is not able to assess its legal capacity.

2.6. The information that the User provides to Insales when registering in the Services is not personal data, as they are defined in the Federal Law of the Russian Federation No. 152-FZ of July 27, 2006. "About personal data".

2.7. Insales has the right to make changes to this Agreement. When making changes in the current version, the date of the last update is indicated. The new version of the Agreement comes into force from the moment of its placement, unless otherwise provided by the new version of the Agreement.

2.8. By accepting this Agreement, the User acknowledges and agrees that Insales may send personalized messages and information to the User (including, but not limited to) to improve the quality of the Services, to develop new products, to create and send personal offers to the User, to inform the User about changes in the Tariff plans and updates, to send marketing materials to the User on the subject of the Services, to protect the Services and Users and for other purposes.

The user has the right to refuse to receive the above information by notifying in writing to the e-mail address Insales - .

2.9. By accepting this Agreement, the User acknowledges and agrees that the Insales Services may use cookies, counters, other technologies to ensure the operation of the Services in general or their individual functions in particular, and the User has no claims against Insales in connection with this.

2.10. The user is aware that the equipment and software used by him to visit sites on the Internet may have the function of prohibiting operations with cookies (for any sites or for certain sites), as well as deleting previously received cookies.

Insales has the right to determine that the provision of a certain Service is possible only if the acceptance and receipt of cookies is allowed by the User.

2.11. The user is solely responsible for the security of the means chosen by him to access the account, and also independently ensures their confidentiality. The User is solely responsible for all actions (as well as their consequences) within or using the Services under the User's account, including cases of voluntary transfer by the User of data to access the User's account to third parties under any conditions (including under contracts or agreements) . At the same time, all actions within or using the Services under the User's account are considered to be performed by the User himself, except for cases when the User notified Insales of unauthorized access to the Services using the User's account and / or any violation (suspicions of violation) of the confidentiality of their account access.

2.12. The User is obliged to immediately notify Insales of any case of unauthorized (not authorized by the User) access to the Services using the User's account and / or any violation (suspicion of violation) of the confidentiality of their means of access to the account. For security purposes, the User is obliged to independently carry out a safe shutdown of work under his account at the end of each session of work with the Services. Insales is not responsible for the possible loss or corruption of data, as well as other consequences of any nature that may occur due to the violation by the User of the provisions of this part of the Agreement.

3. Responsibility of the Parties

3.1. The Party that violated the obligations stipulated by the Agreement regarding the protection of confidential information transmitted under the Agreement is obliged to compensate, at the request of the affected Party, the real damage caused by such a violation of the terms of the Agreement in accordance with the current legislation of the Russian Federation.

3.2. Compensation for damages does not terminate the obligations of the violating Party for the proper performance of obligations under the Agreement.

4.Other provisions

4.1. All notices, requests, demands and other correspondence under this Agreement, including those including confidential information, must be made in writing and delivered personally or through a courier, or sent by e-mail to the addresses specified in the license agreement for computer programs dated 12/01/2016, the agreement of accession to the license agreement for computer programs and in this Agreement or other addresses that may be further specified in writing by the Party.

4.2. If one or more provisions (conditions) of this Agreement are or become invalid, then this cannot serve as a reason for the termination of other provisions (conditions).

4.3. The law of the Russian Federation shall apply to this Agreement and the relationship between the User and Insales arising in connection with the application of the Agreement.

4.3. The User has the right to send all suggestions or questions regarding this Agreement to the Insales User Support Service or to the postal address: 107078, Moscow, st. Novoryazanskaya, 18, pp. 11-12 BC "Stendhal" LLC "Insales Rus".

Publication date: 01.12.2016

Full name in Russian:

Limited Liability Company "Insales Rus"

Abbreviated name in Russian:

Insales Rus LLC

Name in English:

InSales Rus Limited Liability Company (InSales Rus LLC)

Legal address:

125319, Moscow, st. Academician Ilyushin, 4, building 1, office 11

Mailing address:

107078, Moscow, st. Novoryazanskaya, 18, building 11-12, BC "Stendhal"

TIN: 7714843760 KPP: 771401001

Bank details:

Warehouse management is one of the most important components in running a business. The quality and success of the organization depends on the situation at the storage facility. That is why entrepreneurs are required to pay sufficient attention to how to properly organize the inventory management system and control it in the future.

Due to the importance of this issue, we have repeatedly touched on the topic in articles and.

Now let's take a closer look at how it is convenient to monitor the movement of stocks using the simplest MS Excel program.

Most organizations that sell a particular product have a warehouse for storage. Hence the justified need for record keeping. The use of special programs for this helps organizations to make their work more efficient, while reducing costs and increasing profits. MS Excel is great for small shops and other small organizations.

CapabilitiesMSexcel:

  • Shows stock levels in stock
  • Structures and analyzes data on existing assets
  • Sales reports for any period of time

Accounting for goods inMSExcel Benefits

MS Excel is exactly the program that can simplify warehouse accounting and has a number of functions for controlling the movement of goods.

Accounting for goods in MS Excel: disadvantages

It is important to note that Excel also has many disadvantages. For example, a complex series of operations that require skills and qualifications, as well as large temporary resources, which we so often lack. Due to errors that sometimes occur in the program, it is possible to lose all available data.

Excel Functions for Inventory Management

The product is tables that are populated with data. You can select individual products or groups of products. With this software tool, it is possible to perform analytical functions and operations using formulas. The program requires the necessary knowledge to work with it. Try to make the most of all the features of the computer program. The greater the set of functions you know and know how to handle with them, the greater your opportunities when maintaining inventory records.

With the help of Excel, it is possible to track the incoming and outgoing products. You can also calculate data regarding the actions performed in the warehouse, for example, to find out the amount of leftovers for a particular type of shoe or a number of products. To minimize the amount of leftovers, find out which brand of shoes is most likely to be purchased. Get acquainted with the recommendations in the field of sale of children's shoes.

Suppose that the table header needs to be stretched over two or more lines. To do this, turn on the word wrap function directly in the cell in advance, and then you will not have any difficulties. Your table will look neat.

The program allows you to insert various fonts, which are very convenient to use when highlighting a number of products or individual categories, based on their parameters.

It is preferable to highlight more important product groups by formatting cells, columns or rows. These techniques will provide easy perception of information in the table.

Recommendations for inventory management withexcel

Ensuring the accounting of products that are in stock is explained by the need to monitor, control the assets of the store, and compile reports during the sales period. It is recommended that a competent specialist who knows the basics of MS Excel perfectly and knows how to apply them in practice is responsible for this.

Here is a good example of how to get started with inventory management in MS Excel (all values ​​are approximate):

At the beginning of the table, you should keep the expense:

Goods accounting table MS Excel

Go down a few hundred cells below and create an income table, for example, in three cells: 1-product, 2-quantity, 3-purchase price.

Goods arrival table in MS Excel

Each product that has just arrived at the warehouse needs to be assigned a price, which is very easy to do in the program. Fill in the table, for example, shoes, sandals, sneakers, sneakers, boots, and others. These are generalized names, in practice, you will fill in the product column with other names. For example, order products that are popular among the audience of buyers for your store. The list is individual for each store.

Important! So that the name of the product in the consumption table matches the name that is indicated in the invoices. Therefore, in the table it is necessary to write the name in full, without abbreviating it. You can enter information up to the size and color of the shoes. The more specific the information, the more convenient it is.

Quantity and purchase price. These columns are filled in.

Adding a new arrival to the list is easy with just two clicks. Also, immediately set the price, quantity and other parameters.

Most small and medium-sized commercial organizations solve the problem of accounting for material assets (goods or products) using the Microsoft Excel application. In this article, we will consider an example on this topic from real practice. The performed automation of the Microsoft Excel workbook allows to reduce the number of routine actions in office work, as well as to ensure control of possible user errors.

Consider a fairly standard situation when our organization is engaged in trade - the purchase of goods and their subsequent sale. In this case, it is necessary to keep accurate records of the nomenclature (quantity of goods, receipt and sale prices). Let's start our development with a sheet called Nomenclature(Fig. 2.16). This sheet plays the role of a register or directory of goods. Each line contains information about a specific item of the item (item name, receipt price, sale price, and quantity in stock). The purpose of software development is to create control sheets that will allow you to adjust the quantity of goods when they arrive or ship, as well as make changes to the price of the goods. In general, an automated book should provide a convenient interface for managers. This interface will provide two sheets - Admission and Shipment.

Direct work with the sheet shown in Fig. 2.16 is undesirable. Of course, the manager, upon receipt or sale of goods, can directly enter data into it. For example, when a new batch of “Comfort” walls arrives in the amount of 5 pieces at a new price of 25,770 rubles, you can simply adjust both the number of units in stock and the receipt price. However, this is not very convenient, and is a "springboard" for possible errors on the part of the user. It is more convenient to create a control sheet (Fig. 2.17) with elements that provide a convenient interface. And the leaf itself Nomenclature next we will hide (this feature is easily implemented using the Microsoft Excel application service) so that it is not directly available to the user. Of course, after that, you can enter protection on the structure of the book (with a password). As a result, users can work with the sheet Nomenclature only with the help of the interface implemented on other sheets.

Upon receipt of goods already in stock, but at a new price, you should decide on the recalculation of the receipt price. For definiteness, we assume that in such a case, the price of the last delivery is set as the price of receipt of goods.

Let us proceed to a detailed examination of the sheet shown in Fig. 2.17, which allows the manager to conveniently enter a new arrival of an existing product, as well as add new items of the nomenclature.

The left half of the Receipt sheet is intended for new receipts of goods that are already in the infobase (on the Nomenclature). To do this, the Combo Box control (located in the area of ​​cell A3) is filled with the names of the products that are present on the sheet Nomenclature. This action is performed when the workbook is opened (the text of the procedure required for this is given in Listing 2.21). The code assumes that the value of the property is Name combo boxes selected Spk.

" Listing 2.21. Procedure executed when opening a Private Sub workbook_Open() " Clearing the list Worksheets("Incoming").Spk.Clear " Counting the number of items in the infobase N = 0 While Worksheets("Nomenclature").Cells(N + 2, 1).Value<>"" N = N + 1 Wend " Populating the list For i = 1 To N Worksheets("Incoming").Spk.AddItem Worksheets(3).Cells(i + 1, 1).Value Next Worksheets("Incoming"). Spk.ListIndex = -1 End Sub

When the user selects the product name in the combo box, then in cell C5 on the worksheet Admission the price is entered from the corresponding line of the sheet Nomenclature books. To do this, the procedure that is performed when the Combo Box item is clicked should be formatted as shown in Listing 2.22. There is also nothing fundamentally new in it compared to the previously considered examples.

" Listing 2.22. Procedure for handling a click on a combo box Private Sub Spk_Click() Range("C5").Value = Worksheets(3).Cells(Spk.ListIndex + 2, 2).Value Range("C6").Value = "" End Sub

Now the user must enter the number of units of the new receipt of the specified product in cell C6. To increase security against accidental actions, we placed another field for a password on the sheet. This is a normal text box. Button click Contribute will update the price and quantity of products on the sheet Nomenclature only if the password is correct. Set the following properties for the text box:

  • Name - Pass;
  • PasswordChar - *.

This will ensure the display of "asterisks" when typing the password (the word typed by the user will be hidden from prying eyes). Thus, we approached writing a procedure for entering information about a new arrival (an increase in the number of specified goods and a possible change in price) on the third sheet. Listing 2.23 shows the procedure that is executed when the button is clicked. Contribute. The combination 357 is considered here as a password.

" Listing 2.23. Handling a click on the Submit button Private Sub CommandButton1_Click() If Pass.Text = "327" Then " Updating the receipt price Worksheets("Item").Cells(Spk.ListIndex + 2, 2).Value = Range(" C5").Value "(!LANG: Quantity correction Col = Range("C6").Value Worksheets("Номенклатура").Cells(Spk.ListIndex + 2, 4).Value = _ Worksheets("Номенклатура").Cells(Spk.ListIndex + 2, 4).Value + Col MsgBox "Данные внесены" Pass.Text = "" Else MsgBox "Ошибка пароля! Данные не внесены" End If End Sub!}

Here, when entering the password 357, the procedure will add the quantity of goods entered by the user to the third sheet and adjust the price. After that, an information message is displayed on the screen stating that the data has been entered, and then the password field is cleared. If the password is wrong, no changes on the sheet Nomenclature not produced.

Let us now turn to the consideration of the right side (in contrast to the left, it is distinguished by a darker shade) of Fig. 2.17. This sheet fragment must be used to enter a new product (indicating the quantity). Listing 2-24 shows the procedure that performs this functional action in this case.

" Listing 2.24. Handling a click on the Add New Item button Private Sub CommandButton_Click() " Counting the number of items entered in the Nomenclature worksheet N = 0 While Worksheets("Nomen").Cells(N + 2, 1).Value<>"" N = N + 1 Wend " Checking the password entered in the right text field If Pass2.Text = "35791" Then Worksheets("Nomenclature").Cells(N + 2, 1).Value = Range("G3"). Value Worksheets("Item").Cells(N + 2, 2).Value = Range("G4").Value Worksheets("Item").Cells(N + 2, 4).Value = Range("G5" ).Value MsgBox "Data entered" Pass2.Text = "" Else MsgBox "Password error! Data not entered" End If End Sub

On the right side of the sheet there are two controls - a button and a text box for the password. It would be possible to leave one password field for the left and for the right side of the sheet. However, the option proposed here increases the functionality of the development. As property value Name we chose Pass2. Let's formulate the procedure for user actions when working with the right side of the sheet:

  • the name of the new product is entered into cell G3;
  • cell G4 indicates the price;
  • cell G5 is reserved for the number of units of goods;
  • the user enters a password, and clicking on the Add new item button fixes (if the password is correct) the entered information on the sheet Nomenclature.

At the beginning of the procedure CommandButton2_Click the number of cells with information about products on the sheet is counted (for this, N is used) Nomenclature. After that, information about the new product is recorded in the next free line of the sheet.

Now let's move on to another sheet - Shipment(Fig. 2.18), which is used when selling goods. On the sheet Shipment there are three controls: a combo box (Name - Spk), a password field (Name - Pass3) and a button (Name - CommandButton1).

To fill in the list, it is necessary to correct the previously considered procedure (Listing 2.21) Workbook_Open. It should now look like Listing 2-25.

" Listing 2.25. Workbook Open Procedure (Option 2) Private Sub Workbook_Open() Worksheets("Incoming").Spk.Clear Worksheets("Shipping").Spk.Clear " Count Items Already Available N = 0 While Worksheets( "nomen").Cells(N + 2, 1).Value<>"" N = N + 1 Wend " Populating Lists For i = 1 To N Worksheets("Incoming").Spk.AddItem Worksheets(3).Cells(i + 1, 1).Value Worksheets("Shipping").Spk .AddItem Worksheets(3).Cells(i + 1, 1).Value Next Worksheets("Incoming").Spk.ListIndex = -1 Worksheets("Shipping").Spk.ListIndex = -1 End Sub

Next in line is the following procedure (Listing 2-26), which is executed when the combo box is clicked. As a result, in the corresponding cells of the sheet Shipment the number of units of the specified product in the warehouse will be entered, as well as its sale price (Fig. 2.19).

" Listing 2.26. Procedure to execute when a combo box is clicked Private Sub Spk_Click() Range("E6").Value = Worksheets("Item").Cells(Spk.ListIndex + 2, 4).Value Range("E7 ").Value = Worksheets("Nomenclature").Cells(Spk.ListIndex + 2, 3).Value End Sub

Now the user must indicate (in cell E6) how many units are being shipped (for orientation and control, using the previous procedure, we entered the available quantity of goods in stock). The procedure shown in Listing 2-27 (performed when the Ship button is clicked) adjusts the number of items in stock in the database.

" Listing 2.27. Submit Procedure Private Sub CommandButton1_Click() If Pass3.Text = "775" Then ColPrais = Worksheets("Item").Cells(Spk.ListIndex + 2, 4).Value Col = Range ("E6").Value If Col > ColPrais Then MsgBox "This quantity is not in stock" Exit Sub End If Worksheets("Nomenclature").Cells(Spk.ListIndex + 2, 3).Value = Range("E7") .Value ColPrais = ColPrais - Col Worksheets("Nomenclature").Cells(Spk.ListIndex + 2, 4).Value = ColPrais MsgBox "Information entered into database" Pass.Text = "" Spk_Click Else MsgBox "Password error!" End If End Sub

After making changes, the information on the screen will be updated - the user will see the already adjusted value (by calling the procedure Spk_Click, which we have previously developed). Now, in order to provide the previously discussed functionality, it is necessary to hide the sheet Nomenclature. Users in this case will use the developed sheets Admission and Shipment.

 

It might be useful to read: