如何在VBA中实现指定单元格双击/F2时弹出禁止手动编辑提示

Hey there! Let's get this edit restriction set up for your January, February, and March worksheets. Below is a step-by-step approach that integrates smoothly with your existing formatting code, blocking manual edits (double-click or F2) on your specified ranges while showing the required message.

Step 1: Optimize Your Existing Formatting Code #

First, let's clean up your Structure4 sub to remove unnecessary Activate and Select calls—these can slow down your code and aren't required for the formatting to work:

Sub Structure4()
    Dim arr As Variant
    arr = Array("A6:C105", "I6:AM105", "AN6:AN105", "AO6:AO105", "AP6:AP105", _
                "AQ6:AQ105", "AR6:AR105", "AS6:AS105", "AT6:AT105", "AU6:AU105", _
                "AV6:AV105", "AW6:AW105", "AX6:AX105", "AY6:AY105", "AZ6:AZ105", _
                "BA6:BA105", "BB6:BB105", "BC6:BG105", "BH6:BH105", "BI6:BL105")
    
    Dim wb As Workbook
    Set wb = Application.Workbooks("Book1")
    
    Dim ws As Worksheet
    Dim i As Integer
    
    For Each ws In wb.Sheets
        Select Case ws.Name
            Case "January", "February", "March"
                With ws
                    For i = LBound(arr) To UBound(arr)
                        With .Range(arr(i))
                            .Font.Name = "Arial Unicode MS"
                            .Font.Size = 8
                            .HorizontalAlignment = xlCenter
                        End With
                        
                        ' Apply number format to specific ranges
                        Select Case i
                            Case 0, 1, 3, 5, 7, 9, 11, 13, 15, 16, 17
                                .Range(arr(i)).NumberFormat = "0.0;[Red]0.0"
                        End Select
                    Next i
                End With
        End Select
    Next ws
End Sub

Step 2: Add Edit Restriction Logic #

To block manual edits on your target ranges, we'll use worksheet event procedures—these trigger when a user tries to edit a cell via double-click or F2. You have two options here:

Option 1: Direct Code in Each Worksheet (Simple for 3 Sheets) #

This is straightforward if you only have three target sheets:

1. Open the VBA Editor with Alt + F11
2. In the Project Explorer (left pane), double-click the January sheet module
3. Paste the code below, then repeat the process for February and March:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    CheckRestrictedRange Target, Cancel
End Sub

Private Sub Worksheet_BeforeKeyDown(ByVal Target As Range, ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    ' Catch the F2 key press
    If KeyCode = vbKeyF2 Then
        CheckRestrictedRange Target, True
        KeyCode = 0 ' Suppress the F2 key to prevent edit mode
    End If
End Sub

Private Sub CheckRestrictedRange(ByVal Target As Range, ByRef CancelEdit As Boolean)
    ' Define your restricted ranges (matches the array in your Structure4 sub)
    Dim restrictedRanges As Variant
    restrictedRanges = Array("A6:C105", "I6:AM105", "AN6:AN105", "AO6:AO105", "AP6:AP105", _
                            "AQ6:AQ105", "AR6:AR105", "AS6:AS105", "AT6:AT105", "AU6:AU105", _
                            "AV6:AV105", "AW6:AW105", "AX6:AX105", "AY6:AY105", "AZ6:AZ105", _
                            "BA6:BA105", "BB6:BB105", "BC6:BG105", "BH6:BH105", "BI6:BL105")
    
    Dim rng As Variant
    For Each rng In restrictedRanges
        ' Check if the clicked cell is in any restricted range
        If Not Intersect(Target, Me.Range(rng)) Is Nothing Then
            CancelEdit = True ' Block the edit
            MsgBox "You can't add information manually on this specific cell", vbExclamation, "Edit Restricted"
            Exit Sub
        End If
    Next rng
End Sub

Option 2: Class Module (Bulk Handling for Future Sheets) #

If you plan to add more month sheets later, this method avoids copying code to each sheet:

1. Insert a new Class Module (Insert > Class Module), then rename it RestrictedSheet (use the Properties window to change the name from Class1)
2. Paste this code into the class module:

Public WithEvents ws As Worksheet

Private Sub ws_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    CheckRestrictedRange Target, Cancel
End Sub

Private Sub ws_BeforeKeyDown(ByVal Target As Range, ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If KeyCode = vbKeyF2 Then
        CheckRestrictedRange Target, True
        KeyCode = 0
    End If
End Sub

Private Sub CheckRestrictedRange(ByVal Target As Range, ByRef CancelEdit As Boolean)
    Dim restrictedRanges As Variant
    restrictedRanges = Array("A6:C105", "I6:AM105", "AN6:AN105", "AO6:AO105", "AP6:AP105", _
                            "AQ6:AQ105", "AR6:AR105", "AS6:AS105", "AT6:AT105", "AU6:AU105", _
                            "AV6:AV105", "AW6:AW105", "AX6:AX105", "AY6:AY105", "AZ6:AZ105", _
                            "BA6:BA105", "BB6:BB105", "BC6:BG105", "BH6:BH105", "BI6:BL105")
    
    Dim rng As Variant
    For Each rng In restrictedRanges
        If Not Intersect(Target, ws.Range(rng)) Is Nothing Then
            CancelEdit = True
            MsgBox "You can't add information manually on this specific cell", vbExclamation, "Edit Restricted"
            Exit Sub
        End If
    Next rng
End Sub

3. Insert a standard module (Insert > Module), then paste this initialization code:

Dim restrictedSheets As Collection

Sub InitializeEditRestrictions()
    Set restrictedSheets = New Collection
    
    Dim ws As Worksheet
    Dim sheetObj As RestrictedSheet
    
    ' Link the class to your target sheets
    For Each ws In ThisWorkbook.Sheets
        Select Case ws.Name
            Case "January", "February", "March"
                Set sheetObj = New RestrictedSheet
                Set sheetObj.ws = ws
                restrictedSheets.Add sheetObj
        End Select
    Next ws
End Sub

4. Finally, add this to your ThisWorkbook module to initialize the restrictions when the file opens:

Private Sub Workbook_Open()
    InitializeEditRestrictions
End Sub

How It All Works #

• BeforeDoubleClick: Triggers when a user double-clicks a cell. Setting Cancel = True stops Excel from entering edit mode.
• BeforeKeyDown: Catches the F2 key press, suppresses it, and runs the same restriction check.
• CheckRestrictedRange: Reusable function that checks if the target cell is in any of your restricted ranges. If yes, it blocks the edit and shows your message.

内容的提问来源于stack exchange，提问作者Fábio Espadaneira