Program Penjualan dg Excel Macro VBA | Part 1 | Form Barang

Summary

This video provides a step-by-step guide to creating a simple sales system using Excel macros. It covers the setup of a worksheet with key columns for product details like name, purchase price, selling price, and stock. The video demonstrates how to use Excel ActiveX buttons to add functionality for saving and canceling data entries. It explains the integration of Visual Basic for Applications (VBA) to automate tasks like saving transaction data and updating stock levels. The video concludes by showing how to manage sales transactions and calculate remaining stock.

Takeaways

• 😀 A simple sales system application is being created using Excel macros.
• 😀 Three initial worksheets are set up: 'Om' for product details, 'Transaction' for sales information, and 'Para' for the output.
• 😀 The 'Om' worksheet includes columns for product name, purchase price, selling price, and stock quantity.
• 😀 Formatting for prices uses the Indonesian Rupiah symbol without commas or decimals.
• 😀 Colors are used to visually organize different areas, including product data and transaction areas.
• 😀 An ActiveX button control is inserted in the Excel sheet for interaction, such as cancel and save functions.
• 😀 The script for the 'Cancel' button is set to delete all data if clicked.
• 😀 The 'Save' button is configured to save data into a database with a confirmation prompt.
• 😀 Excel macros are used to automate the saving process, where data is saved in the correct format automatically.
• 😀 When saving, data is formatted in a currency style (Indonesian Rupiah) without commas or decimals.
• 😀 The application is capable of handling product sales, calculating stock levels, and updating transaction data with each entry.

Q & A

What is the primary goal of the Excel sales system in the script?

The primary goal of the Excel sales system is to create a simple application using Excel macros to track goods, sales, and stock levels, while automating the processes of saving and updating data.

How do you set up the initial worksheet for the sales system?

You need three initial sections: one for the goods (with columns for name, purchase price, selling price, and stock), one for the transaction form, and one for the database where sales data will be stored.

Why is the formatting of the purchase and selling prices important in the system?

The formatting ensures that the purchase and selling prices are displayed in Indonesian Rupiah without commas and with no decimal places, making the data easier to read and more consistent with accounting standards.

What role do macros play in the sales system described?

Macros are used to automate the actions of the system, such as saving the transaction data and handling button functionalities (like 'Save' and 'Cancel'). They reduce the need for manual data entry and improve efficiency.

How are the buttons integrated into the Excel sales system?

Buttons are inserted using the 'Developer' tab in Excel, specifically ActiveX controls. These buttons are then assigned specific properties and linked to macros that trigger actions like saving or canceling data.

What happens when the 'Cancel' button is clicked in the sales system?

When the 'Cancel' button is clicked, all data entered in the current transaction form is deleted, effectively clearing the form for the next entry.

How does the save process work in the Excel sales system?

The save process involves clicking the 'Save' button, which triggers a macro to store the data (including purchase price, selling price, stock, and sold items) in the database, with proper formatting for currency.

What are the steps for setting up the database and tracking stock in the system?

The database is created by adding a 'Sold' column and a 'Stock' column to track the remaining inventory. Stock is automatically updated when items are sold, with the 'Sold' value subtracted from the initial stock.

How does the system update the stock after a sale is made?

The system automatically calculates the remaining stock by subtracting the number of sold items from the initial stock, updating the stock levels in real-time.

How does the Excel sales system handle errors or incorrect inputs?

The system doesn't explicitly mention error handling, but the process includes manually deleting incorrect rows or entries and adjusting the data accordingly, ensuring the integrity of the database.