Excel VBA Stock Update Form Guide
Uploaded by
iko erickoExcel VBA Stock Update Form Guide
Uploaded by
iko erickoCommon questions
Powered by AIThe 'RESET_Click' subroutine clears the search criteria fields (BERDASARKAN, KATAKUNCI, TANGGAL) and resets corresponding cells (M5, N5) on Sheet5. It prevents screen updates during operation to optimize performance, updates the ListBox to show all transaction records (from 'A5:K'), and clears the summary labels (HARGASATUAN, DISKON, TOTALHARGA, JUMLAHBARANG) to ensure the form is ready for new input or search operations .
The 'On Error Resume Next' statement is used to allow the code to continue running even after an error occurs, effectively suppressing runtime errors. This is useful in scenarios where the presence of certain errors is acceptable (e.g., initially empty ranges). However, it can lead to unnoticed errors if not managed carefully, as it bypasses traditional error handling methods that would alert the programmer to potential issues in the code .
'WorksheetFunction.Sum' is used to calculate the sum of specific ranges, such as the total quantity, unit price, and discount. The code applies it to compute summary information (JUMLAHBARANG, HARGASATUAN, DISKON, TOTALHARGA) which are displayed on the form. This ensures efficient aggregation of data retrieved from Sheet5, allowing users to view comprehensive summary metrics directly within the form .
The 'CARI_Click' subroutine first checks if the search criteria (BERDASARKAN and KATAKUNCI) are provided. If not, it displays a message prompting the user to input search data. It then records these criteria in specific cells (M4 and M5) on Sheet5. An Advanced Filter operation is executed using these criteria, filtering data from Sheet5's column 'A4' to be copied to columns 'P4:Z4'. The results are shown in a ListBox, while summary calculations (JUMLAHBARANG, HARGASATUAN, DISKON, TOTALHARGA) are performed and formatted accordingly .
The 'MonthView1_DateClick' event handler sets the TANGGAL control's value to the selected date from MonthView1. It then updates the N5 cell on Sheet5 with this date, formatting it as 'MM/dd/yyyy', ensuring consistency in date representation within the application .
During userform initialization, the code sets the list of possible search criteria options (e.g., 'Kode Transaksi', 'Nama Customer') in the BERDASARKAN combobox. It clears previous values in specific cells (M5, N5) on Sheet5 and sets the ListBox to display the relevant transaction data range ('A5:K'). This setup ensures the userform is prepped with necessary configurations and ready for immediate use, enhancing user experience by providing default search parameters .
The code employs several strategies to handle user input errors and maintain robustness: It validates essential input fields, showing an error message if any are empty, and uses 'On Error Resume Next' to prevent the code from crashing due to unexpected conditions. These measures ensure that data operations only proceed when input is correct, thereby maintaining data integrity and application stability, while careful range management prevents common errors related to data entry or manipulation .
After updating the stock information, the code sets the RowSource property of the ListBox (TABELUPDATE) to the range containing the updated data ('stokupdate!A5:G' to the last row of column G) on Sheet9. This dynamically updates the content displayed in the ListBox with the most recent data entries .
The VBA code checks if any key fields (KODE1, NAMA1, SUPPLIER, STOKAWAL, TAMBAHSTOK, TOTALSTOK) are empty before proceeding. If any of these fields are empty, a message box (MsgBox) is triggered to inform the user that complete data is required, preventing further processing until all fields are filled .
The purpose of using 'Application.ScreenUpdating = False' is to improve the efficiency of the macro by preventing Excel from updating the screen until the code execution is complete. This reduces flickering and speeds up the process, as the screen is not continuously refreshed during code execution .