VBA: How to update multiple Excel sheets from a single UserForm without performance issues?
I'm optimizing some code but I'm trying to implement I'm working on an Excel VBA project where I need to update multiple sheets based on user inputs from a UserForm... The UserForm collects data like names, IDs, and other relevant information, and when the user clicks the 'Submit' button, I need to write this data to three different sheets: 'DataSheet1', 'DataSheet2', and 'SummarySheet'. However, I'm facing performance issues when multiple entries are being processed, and sometimes the application hangs or throws a 'Run-time error '1004': Application-defined or object-defined error' when I try to access certain ranges. Here’s the code I currently have for the button click event: ```vba Private Sub btnSubmit_Click() Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet Set ws1 = ThisWorkbook.Sheets("DataSheet1") Set ws2 = ThisWorkbook.Sheets("DataSheet2") Set ws3 = ThisWorkbook.Sheets("SummarySheet") Dim nextRow1 As Long, nextRow2 As Long, nextRow3 As Long nextRow1 = ws1.Cells(ws1.Rows.Count, "A").End(xlUp).Row + 1 nextRow2 = ws2.Cells(ws2.Rows.Count, "B").End(xlUp).Row + 1 nextRow3 = ws3.Cells(ws3.Rows.Count, "C").End(xlUp).Row + 1 ws1.Cells(nextRow1, 1).Value = txtName.Value ws2.Cells(nextRow2, 2).Value = txtID.Value ws3.Cells(nextRow3, 3).Value = txtComments.Value ' Clear inputs after submission txtName.Value = "" txtID.Value = "" txtComments.Value = "" MsgBox "Data submitted successfully!" End Sub ``` I’ve tried disabling screen updating and events before running the code to improve performance: ```vba Application.ScreenUpdating = False Application.EnableEvents = False ' [Your existing code here] Application.ScreenUpdating = True Application.EnableEvents = True ``` But it seems like the application still slows down significantly when I submit multiple entries in a row. Also, I suspect that the issue may be occurring if there’s a validation or formatting issue in the cells I’m trying to write to. Does anyone have tips on how to optimize this process, or how to handle potential errors more gracefully? I’m using Excel 2016 and would appreciate any guidance on best practices for handling this situation in VBA. I'm working on a application that needs to handle this. For reference, this is a production application. Am I approaching this the right way? I'm working with Vba in a Docker container on Windows 10. Thanks for your help in advance!