BYU Student Author: @Michael_Barney
Reviewers: @Kylie_Larsen17, @Saul_Esplin
Estimated Time to Solve: 40 Minutes
We provide the solution to this challenge using:
- Excel
Need a program? Click here.
Overview
Techware Logistics is a warehouse that manages thousands of products across multiple locations. The company is struggling with inventory tracking, stock alerts, and automated restocking reports. Currently, warehouse employees manually update an Excel sheet, leading to errors and inefficiencies.
The company has tasked you, a VBA specialist, with developing parts of an automated inventory management system in Excel using VBA. Your solution should generate alerts for low stock levels and create restocking reports.
The current inventory system requires several improvements to enhance efficiency and accuracy. To prevent shortages, the system should trigger alerts when an item’s stock falls below a predefined threshold. Additionally, a restocking report should be generated, listing all items that need replenishment. The system should include user-friendly buttons and macros to simplify the functions for everyone.
Instructions
- Create a low stock alert system. Make sure that inventory managers are immediately notified when stock is running low on the excel sheet. Your first macro should be able to do the following:
- Check Stock Levels Automatically:
- The macro should scan the Current Stock column.
- If Current Stock < Reorder Level, trigger an alert.
- Apply Conditional Formatting:
a. If stock is low, highlight the cell red.
b. If stock is sufficient, remove the formatting. - Trigger a Pop-Up Notification:
If low-stock items are detected, a message box should display:
“Warning: Some items have fallen below their reorder level!”
- Create a restocking report generator. Your second macro should be able to do the following:
- Create a new worksheet named “Restocking Report”.
- Copy headers from the inventory sheet:
- Product ID, Product Name, Current Stock, Reorder Level, Recommended Reorder Quantity.
- Scan inventory data:
a. Identify products where Current Stock < Reorder Level.
b. Copy those rows into the Restocking Report sheet. - Display Confirmation Message:
After the report is created, show a pop-up:
“Restocking Report has been generated!” - Make the Macro Button-Activated:
- Add a button labeled “Generate Report” in the worksheet.
-
Link your two macros to buttons on the first sheet of the data so that managers and all users of the data can run the macros easily. Label the buttons, “Check Low Stock” and “Restock Report Generator” respectively.
-
When you have completed the challenge, upload a screenshot of your VBA code to the thread.
Data Files
Suggestions and Hints
Once the “Check Low Stock” button is pushed, there should be 25 cells highlighted in red.
Solution