如何通过Excel VBA保存用户AutoComplete设置并临时关闭后恢复

Got it, let's walk through exactly how to implement this in VBA—we'll make sure we respect the user's original settings, avoid forcing changes on anyone, and clean up after ourselves when the workbook closes.

Step 1: Understand the Core Property #

First, the setting you need to target is Application.EnableAutoComplete—this is a Boolean value where:

• True = AutoComplete is turned on
• False = AutoComplete is turned off

We'll read this value when the workbook opens, store it, then restore it when the workbook closes.

Step 2: Full VBA Implementation #

You'll want to paste this code directly into the ThisWorkbook module of your Excel file:

' Module-level variable to store the user's original AutoComplete setting
Private originalAutoCompleteSetting As Boolean

Private Sub Workbook_Open()
    ' Save the user's existing AutoComplete state as soon as the workbook opens
    originalAutoCompleteSetting = Application.EnableAutoComplete
    
    ' Temporarily disable AutoComplete for your worksheet's use case
    Application.EnableAutoComplete = False
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ' Add error handling to ensure we don't block the workbook from closing if something goes wrong
    On Error Resume Next
    
    ' Restore the user's original AutoComplete setting
    Application.EnableAutoComplete = originalAutoCompleteSetting
    
    ' Reset error handling to default
    On Error GoTo 0
End Sub

Key Details to Note #

• Respect User Settings: By storing originalAutoCompleteSetting when the workbook opens, we guarantee we'll revert to exactly what the user had—whether AutoComplete was on or off before they opened your file. No forced changes here.
• Error Safety: The On Error Resume Next in the close event ensures that even if there's an unexpected issue (like Excel being unresponsive), we won't prevent the user from closing the workbook.
• Scope: The Private variable is tied to the ThisWorkbook module, so it stays accessible only within that module and won't interfere with other code in your workbook.

What If the User Modifies Settings Mid-Session? #

If the user manually turns AutoComplete back on while using your workbook, our code will still revert to their original state when they close it. This is intentional—we're only responsible for restoring the setting to how it was when they first opened your file, not tracking every change they make.

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