如何在Excel VBA中实现数据保存成功或失败的弹窗提示？

Great job getting your user form set up for data entry! To add success/failure prompts when saving data, we just need to add error handling logic to your existing code—this will catch any unexpected issues (like protected sheets, locked cells, or permission problems) that might prevent data from saving, and show the right message accordingly.

Here's your updated code with the necessary additions, plus some small improvements for compatibility:

Private Sub cmdAddData_Click()
    ' Enable error trapping to catch save failures
    On Error GoTo ErrorHandler
    
    ' Existing validation checks
    If ComboBox1.Value = "" Then
        MsgBox "You must select your full name", vbCritical
        Exit Sub
    End If
    If ComboBox2.Value = "" Then
        MsgBox "You must select the full name of your 1st nominee", vbCritical
        Exit Sub
    End If
    If ComboBox3.Value = "" Then
        MsgBox "You must select the readiness level of your 1st nominee", vbCritical
        Exit Sub
    End If
    
    Dim wks As Worksheet
    Dim AddNew As Range
    Set wks = Sheet6
    ' Use Rows.Count for compatibility with all Excel versions
    Set AddNew = wks.Range("A" & wks.Rows.Count).End(xlUp).Offset(1, 0)
    
    ' Write data to the worksheet
    AddNew.Offset(0, 0).Value = ComboBox1.Value
    AddNew.Offset(0, 8).Value = ComboBox2.Value
    AddNew.Offset(0, 18).Value = ComboBox3.Value
    
    ' Success prompt - only runs if no errors occurred
    MsgBox "Data Saved Successfully", vbInformation
    Exit Sub ' Exit before reaching error handler
    
ErrorHandler:
    ' Triggered if any error occurs during data saving
    MsgBox "error! data not saved", vbCritical
    ' Optional: Show detailed error message for debugging
    ' MsgBox "error! data not saved: " & Err.Description, vbCritical
End Sub

Key Changes Explained: #

• On Error GoTo ErrorHandler: This line turns on error trapping. If any error happens while writing data (e.g., the sheet is protected), the code jumps directly to the ErrorHandler section.
• MsgBox "Data Saved Successfully", vbInformation: This runs only if all data writes complete without errors, using an info icon for clarity.
• Exit Sub: Ensures we don't accidentally hit the error handler code when everything works correctly.
• ErrorHandler block: Displays the failure message whenever an error is caught. I added an optional line to show the exact error description—uncomment it if you need to debug specific issues.

Quick Compatibility Tip: #

I replaced Range("A65356") with Range("A" & wks.Rows.Count) because older Excel versions have 65536 rows, while newer ones have 1,048,576. This makes your code work across all versions without modification.

内容的提问来源于stack exchange，提问作者Tena