Excel VBA代码优化需求：仅点击指定单元格范围触发Access操作

The core issue in your code is that the OpenAccess subroutine is being called outside the conditional check for your target range. That's why clicking any cell triggers Access to open—even when the cell isn't in A23:A2000.

Here's how to adjust your code to fix this:

1. Move the Call OpenAccess(Init) line inside the If Not Application.Intersect(...) Is Nothing block. This ensures Access only opens when the selected cell is in your specified range.
2. Ensure Init is only assigned and used when the range condition is met (to avoid undefined variable issues).
3. Clean up redundant logic and fix inconsistent file paths for reliability.

Corrected Full Code #

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim Init As Integer
    Dim LPath As String
    'Path to Access database
    LPath = "J:\Admin\Access Database for Punch List.accdb"
    
    'Exit if selection is more than one cell
    If Target.Cells.Count > 1 Then Exit Sub
    
    ' Validate if selected cell is in the target range
    If Not Application.Intersect(Range("A23:A2000"), Target) Is Nothing Then
        ' Assign value from selected cell to Init
        Init = Target.Value
        
        ' Close Access if it's already open with the database
        Call CheckIfFileOpen
        
        ' Open Access and apply the filter
        Call OpenAccess(Init)
    End If
    ' No code runs here outside the If block—clicking other cells does nothing
End Sub

Sub CheckIfFileOpen()
    Dim fileName As String
    fileName = "J:\Admin\Access Database for Punch List.accdb"
    
    ' Quit Access if the database is already open
    If IsFileOpen(fileName) Then
        GetObject(, "access.application").Quit
    End If
End Sub

Sub OpenAccess(Init As Integer) ' Explicit type declaration for reliability
    Dim oApp As Object
    Dim LPath As String
    ' Ensure path matches the one used in other subroutines
    LPath = "J:\Admin\Access Database for Punch List.accdb"
    
    ' Launch Access and make it visible
    Set oApp = CreateObject("Access.Application")
    oApp.Visible = True
    
    ' Open the database and filter the form
    oApp.OpenCurrentDatabase LPath
    oApp.DoCmd.OpenForm "frm_AllRecords"
    oApp.DoCmd.ApplyFilter , "Initiative_Nbr=" & Init
    oApp.Forms("frm_AllRecords").SetFocus
End Sub

Key Improvements: #

• Range Restriction: All Access-related logic now runs only when clicking cells in A23:A2000.
• Consistent Paths: Fixed the mismatched database path in OpenAccess (previously pointed to J:\Kyle instead of J:\Admin—verify this matches your actual file location).
• Cleaner Logic: Simplified the CheckIfFileOpen subroutine to remove redundant code.
• Explicit Typing: Added As Integer to the Init parameter in OpenAccess to avoid implicit variant type issues.

Now, clicking any cell outside A23:A2000 won't trigger the Access database to open. Only single-cell clicks within your specified range will run the filter and launch Access.

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