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

Today there is a large selection of specialized tools for inventory control: from simple free programs to expensive full-featured WMS systems. Despite this, many entrepreneurs initially choose Excel spreadsheets for inventory control. At first, this seems like an affordable and convenient solution, but as the number of orders in an organization grows, using Excel for warehouse accounting is fraught with many problems. The more warehouse premises, the more commodity items, the higher the likelihood of their appearance. What difficulties do users face when working with Excel?

  • Data entry errors. Due to a single input error, it becomes necessary to recheck all table data.
  • The inconvenience of teamwork. A situation often arises when, when editing data by one user, they are overwritten by another. Thus, the accounting in Excel may be incorrect.
  • Performing many operations manually. Excel is not designed as a warehouse management solution, the program combines many basic functions, and therefore you often have to spend time on monotonous manual labor.
  • Difficulty checking the correctness of the data. When using Excel, a lot of effort is spent on checking the correctness of the entered data and on bringing the information into a suitable 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 very often, however, they are time-consuming. Warehouse program in Excel Corrections of errors and typos, lengthy checks of the correctness of the balance data and forced manual labor when working with Excel are the main reasons why entrepreneurs eventually come to use specialized solutions for warehouse accounting. Even experienced specialists who know how to keep stock records in Excel still make a choice in favor of other Excel-based programs or fundamentally new solutions.

How to keep inventory in Excel?

Regardless of whether you choose Excel spreadsheets, the MoySklad service or another specialized solution for trading, you must follow a few basic principles. If you download the program and do not use special rules, over time, the work taking into account will become much more complicated:

  1. Correct design of reference books. A handbook with unmarked goods creates guaranteed accounting problems, inconveniences for 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, SKUs, prices, VAT). If the accounting of the goods sold in the warehouse is carried out using the MyWarehouse service, all the necessary data about the goods are automatically inserted into the documents, which saves a lot of time when processing orders.
  2. Establishment of initial balances in quantitative and total terms. Correct data at the beginning of the work will give adequate reports in the process of work, and those who make up sales from a "blank slate" should not count on this.
  3. Observance of chronology in paperwork. It is necessary to register the arrival of goods to the warehouse from the supplier strictly before its shipment to the buyer. Otherwise, the cost will "go" and you will not be able to calculate exactly how much you earned. In MyStorage for each purchase and sale of goods, you can specify an arbitrary date.
  4. Structured storage of additional information. When keeping records of a warehouse, it is important to collect additional information about sales (for example, method of delivery and payment, date of shipment, customer name, manager's name) in separate fields. All these data in the future will be useful for drawing up route sheets for couriers, calculating salary for managers, analyzing statistics. In MySklad, you can easily and without programming create the required number of free fields for use in reference books and documents.

Free support by phone and e-mail is available to users of the Internet service MySklad. The support staff not only help with the work with the program, but also share advice on optimal inventory control and setting up basic business processes. Download the free version, try our new solutions.

Warehouse accounting in Excel is suitable for any trade or industrial organization where it is important to take into account the amount of raw materials and materials, finished products. For this purpose, the company keeps stock records. Large firms, as a rule, purchase ready-made solutions for electronic accounting. There are a lot of options today for various activities.

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

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

How to keep inventory in Excel?

Any custom-built warehouse management solution, either self-created or purchased, will work well only if basic rules are followed. If you neglect these principles at the beginning, then later the work will become more difficult.

  1. Fill out directories as accurately and in detail as possible. If this is a nomenclature of goods, then it is necessary to enter not only names and quantities. For correct accounting, you will need codes, articles, expiration dates (for individual industries and trade enterprises), etc.
  2. Opening balances are entered in quantitative and monetary terms. It makes sense to make an inventory before filling in the appropriate tables.
  3. Observe the chronology in registering 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 - payment method. Each organization has its own characteristics. A number of data entered into the warehouse accounting program in Excel will be useful for statistical reports, payroll 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 general recommendations can be made:

  1. For correct inventory control in Excel, reference books need to be compiled. They can take 1-3 sheets. This is the directory "Suppliers", "Buyers", "Points of account of goods". In a small organization where there are not many contractors, reference books are not needed. It is not necessary to draw up a list of points of accounting of goods if the company has only one warehouse and / or one store.
  2. With a relatively constant list of products, it makes sense to make the product nomenclature in the form of a database. Subsequently, the receipt, expense and reports should be filled out with references to the item. 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. Disposal - "Expense". The current state is "Balances" ("Reserve").
  4. Results, the report is generated using the PivotTable tool.

To prevent the headings of each inventory control table from running 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 records, the user will see the column headings.



Excel table "Warehouse accounting"

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

Making "References".

For supplier data:


* The form may be different.

For customer data:


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

To audit the points of issue of goods:


We repeat once again: it makes sense to create such directories if the enterprise is large or medium-sized.

You can make a product list on a separate sheet:


In this example, we will use drop-down lists in the table for inventory control. Therefore, we need Directories and Nomenclature: we will make links to them.

Name the range of the "Nomenclature" table: "Table1"... To do this, select the table range and enter the appropriate value in the name field (opposite the formula line). 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 the incoming and outgoing transactions, we fill in two separate sheets.

We make a hat for the "Parish":

Next stage - table filling automation! It is necessary to make sure that the user chooses from the ready-made list the name of the product, supplier, accounting point. 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, we will format all directories as tables. This is necessary so that later you can add, change something.

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

In the field "Data type" select "List". An additional field "Source" appears immediately. To get the values \u200b\u200bfor the drop-down list from another sheet, use the function: \u003d 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 “Unit. rev. " the corresponding value should appear. Let's do it using the VLOOKUP and UND function (it will suppress the error as a result of the VLOOKUP function when referencing 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 "Points of account" - where the received goods were sent. To fill in the "Cost" column, we use the multiplication formula (\u003d 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 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. If previously conducted, then this column will contain leftovers. Names and units of measure are taken from the product range.

Columns "Receipt" and "Shipments" are filled using the SUMIF function. The remainders are calculated using mathematical operators.

Download the warehouse accounting program (a ready-made example compiled according to the above described scheme).


So the independently compiled program is ready.

However, when filling out incoming or outgoing documents, some values \u200b\u200bstill have to be hammered in by hand.

These include:

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

With the built-in Excel tools, you can achieve automatic filling of several columns based on the data in the directory. However, you will hardly 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 a product by multiplying the quantity by the price.

In the above way, the sheets are also "Income" and "Expense". Their separate maintenance is convenient for the subsequent quick search of the required document.

Formation of the turnover sheet

You can make a turnover sheet yourself, but you have to figure out some of the functions of the spreadsheet editor. No programming skills are required here.

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

Tracking critical balances

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

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

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

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

This method of monitoring values \u200b\u200bis applicable not only to warehouse balances. Similarly, you can analyze the following indicators:

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

Excel can provide entrepreneurs with a variety of 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.

Pros of accounting for goods in the store in Excel

Try all the features of the EKAM platform for free

Warehouse accounting program

  • Online checkout under 54-FZ

Email*

Email*

Try

Warehouse accounting program

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

We provide prompt support by phone,
we help to load the product base and register the checkout.

Try all the possibilities 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 freely accepted and of its own free will, it applies to all information that LLC Inseils Rus and / or its affiliates, including all persons belonging to the same group with LLC "Inseils Rus" (including LLC "EKAM service"), may receive information about the User while using any of the sites, services, services, computer programs, products or services of LLC "Inseils Rus" (hereinafter - Services) and in the course of execution of any agreements and contracts with the User by Insales Rus LLC. The User's consent to the Agreement, expressed by him within the framework of relations with one of the listed persons, applies to all other listed persons.

1.2 Use of the Services means the User agrees with this Agreement and the conditions specified therein; in case of disagreement with these conditions, the User must refrain from using the Services.

"Insails" - Limited Liability Company "Insales Rus", PSRN 1117746506514, INN 7714843760, KPP 771401001, registered at the address: 125319, Moscow, Akademika Ilyushin st., 4, building 1, office 11 (hereinafter - "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 legislation of the state of which such a person is a resident;

or an individual entrepreneur registered in accordance with the legislation of the state of which such a 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 projects; information about technical systems and equipment, including software elements; business forecasts and information about prospective purchases; requirements and specifications of specific partners and potential partners; information, related to intellectual property, as well as plans and technologies related to all of the above), communicated by one party to the other in writing and / or electronic form, explicitly 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 instructions).

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, disclose 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 of the Parties will take all necessary measures to protect confidential information, at least using 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 execution of this Agreement.

2.3. The obligation to keep confidential information secret is valid within the validity period of this Agreement, the license agreement for computer programs dated 01.12.2016, the agreement of accession to the license agreement for computer programs, agency and other agreements and for five years after termination their actions, unless the Parties separately agree otherwise.

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

(b) if the information provided has become 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 has been 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 state authority, other state authority, or local self-government body 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 about the request received;

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

2.5. Insales does not verify the accuracy of the information provided by the User and does not have the ability to assess his legal capacity.

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

2.7 Insales reserves the right to amend this Agreement. When making changes in the current edition, the date of the last update is indicated. The new version of the Agreement comes into force from the moment it is posted, unless otherwise provided by the new version of the Agreement.

2.8 Accepting this Agreement, the User realizes and agrees that Inseils can send the User personalized messages and information (including but not limited to) to improve the quality of the Services, to develop new products, to create and send the User personal offers, to inform the User about changes in Tariff plans and updates, to send the User marketing materials related to 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 this in writing to the email address of Inseils -.

2.9. By accepting this Agreement, the User understands and agrees that the Inseils Services may use cookies, counters, other technologies to ensure the performance of the Services in general or their individual functions in particular, and the User has no claims against Inseils in this regard.

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.

Insails has the right to establish that the provision of a certain Service is possible only if the acceptance and receipt of cookies is permitted 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 the User's voluntary transfer of data to access the User's account to third parties on any terms (including under contracts or agreements) ... At the same time, all actions within or using the Services under the User's account are considered to have been performed by the User himself, except for cases when the User has notified Inseils about unauthorized access to the Services using the User's account and / or about any violation (suspicions of violation) of the confidentiality of his means of access to the account.

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

3.Responsibility of the Parties

3.1. A Party that has violated the obligations stipulated by the Agreement regarding the protection of confidential information transferred under the Agreement is obliged to compensate, at the request of the affected Party, for 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 damage does not terminate the obligations of the offending Party to properly fulfill its obligations under the Agreement.

4.Other provisions

4.1. All notifications, inquiries, requirements and other correspondence under this Agreement, including those that include confidential information, must be made in writing and delivered in person or through a courier, or sent by e-mail to the addresses specified in the license agreement for computer programs dated 01.12.2016, the agreement of accession to the license agreement for computer programs and in this Agreement or other addresses that may be further indicated by the Party in writing.

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. All suggestions or questions regarding this Agreement, the User has the right to send to the Inseils User Support Service or to the postal address: 107078, Moscow, st. Novoryazanskaya, 18, p. 11-12 BC "Stendhal" LLC "Inseils Rus".

Date of publication: 01.12.2016

Full name in Russian:

Limited Liability Company "Insales Rus"

Abbreviated name in Russian:

LLC "Insales Rus"

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, p. 11-12, BC "Stendhal"

INN: 7714843760 Checkpoint: 771401001

Bank details:

Warehouse stock management - one of the most important components in doing 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 a warehouse management system and monitor it in the future.

In connection with the importance of this issue, we have repeatedly touched on the topic in articles and.

Now let's dwell in more detail on how convenient it is to monitor the movement of stocks using the simplest MS Excel program.

Most organizations that sell a product have a warehouse for storage. Hence the justified need for record keeping. The use of dedicated software helps organizations to work more efficiently, while reducing costs and increasing profits. MS Excel is great for small shops and other small organizations.

OpportunitiesMSExcel:

  • Shows the amount of stock in the warehouse
  • Structures and analyzes data on available 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 to control the movement of goods.

Accounting of goods in MS Excel: disadvantages

It is important to note that Excel has many drawbacks. For example, a complex series of operations that require work skills and qualifications, as well as large time resources, which we so often lack. Due to errors that sometimes occur in the program, the loss of all existing data is possible.

Excel functions for inventory management

The product consists of tables filled with data. You can select individual products or product groups. 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 capabilities of the computer program. The greater the set of functions you know and are able to handle, the more opportunities you have when conducting inventory.

With the help of Excel, it is possible to track the arrival and departure of products. You can also calculate the data regarding the actions performed in the warehouse, for example, find out the amount of leftovers for a specific type of shoe or a number of goods. To minimize the amount of leftovers, find out which brand of shoes is most popular. Read the recommendations for the sale of children's shoes.

Suppose you want to stretch the head of a table 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 an easy perception of the information in the table.

Tips for managing inventory usingExcel

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

Here's a good example of how to get started with managing warehouse stocks in MS Excel (all values \u200b\u200bare approximate):

At the beginning of the table, the flow rate should be kept:

MS Excel inventory table

Going down a few hundred cells below and create a receipt table, for example, in three cells: 1-item, 2-quantity, 3-purchase price.

Goods receipt 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 out the table, for example, shoes, sandals, sneakers, sneakers, boots, and others. These are generalized names, but in practice you will fill in the product column with other names. For example, order products that are popular with your audience of buyers for your store. The list is individual for each store.

Important! So that the name of the goods in the consumption table coincides with the name that is indicated in the invoices. Therefore, it is necessary to write the name in full in the table without shortening it. You can include information down to the size and color of the shoe. The more specific the information is, the more convenient it is.

Quantity and purchase price. These columns are filled in fact.

It is easy to add a new item to the list 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 values \u200b\u200b(goods or products) using Microsoft Excel. In this article we will look at a real-life example on this topic. The completed automation of the Microsoft Excel workbook allows you to reduce the number of routine actions in office work, as well as to ensure control of possible user errors.

Let's 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 item (quantity of goods, prices of receipt and sale). Let's start our development with a sheet called Nomenclature (fig. 2.16). This sheet plays the role of a register or reference book of goods. Each line contains information about a specific item of the item (product name, receipt price, sales price, and quantity on hand). The goal of software development is to create control sheets that will allow you to adjust the quantity of goods when they are received or shipped, as well as make changes to the price of goods. In general, an automated workbook should provide a user-friendly interface for managers. This interface will provide two sheets - Admission and Shipment.

Directly working with the sheet shown in fig. 2.16 is deprecated. Of course, the manager can directly enter data into it when goods are received or sold. 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 the warehouse and the price of receipt. 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 we will hide it further (this feature is easily implemented using the Microsoft Excel application service), so that it will not be directly accessible 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 using the interface implemented in other sheets.

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

Let's move on to a detailed examination of the sheet shown in Fig. 2.17, which allows the manager to enter a new receipt of an existing product in a convenient form, 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 available in the infobase (on the sheet Nomenclature). To do this, the "Combo box" control (located in the area of \u200b\u200bcell A3) is filled with the names of the goods present on the sheet Nomenclature... This action is performed when the workbook is opened (the text of the procedure required for this is shown in Listing 2.21). The code assumes that the property value Name combo box selected Spk.

"Listing 2.21. Procedure for Opening a Private Sub Workbook_Open ()" Clearing the List of Worksheets. Spk.Clear "Counting the number of items in the infobase N \u003d 0 While Worksheets. Cells (N + 2, 1) .Value<> "" N \u003d N + 1 Wend "Populating the list For i \u003d 1 To N Worksheets. Spk.AddItem Worksheets (3) .Cells (i + 1, 1) .Value Next Worksheets (" Incoming "). Spk.ListIndex \u003d -1 End Sub

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

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

Now the user has to enter in cell C6 the number of units of the new receipt of the specified product. To increase protection against accidental actions, we have placed another field for the password on the sheet. This is a simple text box. Click on the button Submit will allow you to update the price and quantity of goods on the list Nomenclature only if the password is correct. Set the following properties for the text box:

  • Name - Pass;
  • PasswordChar - *.

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

"Listing 2.23. Handle a click on the Add Private Sub button CommandButton1_Click () If Pass.Text \u003d" 327 "Then" Updating the receipt price Worksheets ("Nomenclature"). Cells (Spk.ListIndex + 2, 2) .Value \u003d Range (" C5 "). Value" (! LANG: Correction of quantity Col \u003d 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 informational 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 to the sheet Nomenclature not produced.

We now turn to the consideration of the right side (unlike the left, it is highlighted in a darker shade) Fig. 2.17. This fragment of the sheet must be used to enter a new product (indicating the quantity). Listing 2.24 shows a procedure that performs this functional action in this case.

"Listing 2.24. Handling a click on the button Add new item Private Sub CommandButton_Click ()" Counting the number of added items on the sheet Nomenclature N \u003d 0 While Worksheets ("Nomen"). Cells (N + 2, 1) .Value<> "" N \u003d N + 1 Wend "Check the password entered in the right text field If Pass2.Text \u003d" 35791 "Then Worksheets (" Nomenclature "). Cells (N + 2, 1) .Value \u003d Range (" G3 "). Value Worksheets. Cells (N + 2, 2) .Value \u003d Range ("G4"). Value Worksheets. Cells (N + 2, 4) .Value \u003d Range ("G5" ) .Value MsgBox "Data entered" Pass2.Text \u003d "" Else MsgBox "Password error! No data entered "End If End Sub

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

  • the name of the new product is entered in 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 product button records (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 goods on the sheet is counted (for this, N is used) Nomenclature... After that, information about the new product is written into the next free line of the sheet.

Now let's move on to another sheet - Shipment (fig. 2.18), which is used in the sale of 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 out the list, you need to adjust the previously described procedure (Listing 2.21) Workbook_Open... It should now look like Listing 2.25.

"Listing 2.25. Procedure for opening a workbook (option 2) Private Sub Workbook_Open () Worksheets. Spk.Clear Worksheets. Spk.Clear" Counting already available items N \u003d 0 While Worksheets ( "nomen"). Cells (N + 2, 1) .Value<> "" N \u003d N + 1 Wend "Populating the lists For i \u003d 1 To N Worksheets. Spk.AddItem Worksheets (3) .Cells (i + 1, 1) .Value Worksheets. Spk .AddItem Worksheets (3) .Cells (i + 1, 1) .Value Next Worksheets. Spk.ListIndex \u003d -1 Worksheets. Spk.ListIndex \u003d -1 End Sub

The next procedure (Listing 2.26) is next, which is performed when you click the combo box. As a result, to 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 selling price (Fig. 2.19).

"Listing 2.26. Procedure for clicking the Private Sub combo box Spk_Click () Range (" E6 "). Value \u003d Worksheets (" Nomenclature "). Cells (Spk.ListIndex + 2, 4). Value Range (" E7 ") .Value \u003d 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 the warehouse). The procedure shown in Listing 2.27 (performed when you click the Ship button) allows you to adjust the number of items in stock in the database.

"Listing 2.27. Procedure to execute when the Upload button is clicked Private Sub CommandButton1_Click () If Pass3.Text \u003d" 775 "Then ColPrais \u003d Worksheets (" Nomenclature "). Cells (Spk.ListIndex + 2, 4) .Value Col \u003d Range ("E6"). Value If Col\u003e ColPrais Then MsgBox "There is no such quantity in stock" Exit Sub End If Worksheets ("Nomenclature"). Cells (Spk.ListIndex + 2, 3) .Value \u003d Range ("E7") .Value ColPrais \u003d ColPrais - Col Worksheets ("Nomenclature"). Cells (Spk.ListIndex + 2, 4) .Value \u003d ColPrais MsgBox "Added information" Pass.Text \u003d "" 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_Clickwhich we have previously developed). Now, to provide the previously agreed functionality, you need to hide the sheet Nomenclature... Users in this case will use the developed sheets Admission and Shipment.

 

It might be helpful to read: