如何阻止单元格输入特定值？现有VBA代码失效求技术协助

Let's break down why your current code isn't working and fix it step by step—there are a few straightforward issues causing the failure:

What's Wrong With Your Original Code? #

• Variable Typo: You have a typo in Revsioncell (missing an 'i') which throws a compile error
• Incomplete Structure: Your Else has no matching End If, and you never closed the For Each loop with Next Revisioncell
• Redundant Case Checks: Checking three separate case variations is unnecessary
• Unbound Event: Your RevisionInput subroutine isn't tied to a worksheet change event, so it won't trigger automatically when someone edits column M

Fixed & Optimized Code #

Paste this into the worksheet module (not a standard module) for the sheet where you want to block "latest" entries:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim watchRange As Range
    Dim intersectRange As Range
    
    ' Define the range we want to monitor (M3:M500)
    Set watchRange = Me.Range("M3:M500")
    ' Only run code if the edited cell is in our target range
    Set intersectRange = Intersect(Target, watchRange)
    
    If Not intersectRange Is Nothing Then
        Application.EnableEvents = False ' Prevent infinite loop from undo/edits
        
        Dim cell As Range
        For Each cell In intersectRange
            ' Normalize to uppercase to catch all case variations in one check
            If UCase(cell.Value) = "LATEST" Then
                MsgBox "Please input correct revision or if one is not available," & vbCrLf & _
                       "Please type 'To be confirmed'", vbExclamation
                Application.Undo ' Revert to the cell's previous value
                ' Or replace the line above with cell.Value = "" to clear the invalid entry
            End If
        Next cell
        
        Application.EnableEvents = True ' Re-enable worksheet events
    End If
End Sub

Key Improvements Explained #

• Auto-Triggering: Tied to the Worksheet_Change event, so it runs automatically whenever someone edits a cell in M3:M500
• Case-Insensitive Check: UCase(cell.Value) converts the input to uppercase, so we catch all variations (latest, Latest, LATEST) with a single comparison
• Clean Structure: Properly closed loops and conditionals to eliminate compile errors
• Infinite Loop Prevention: Disabled events temporarily while making changes, so the Worksheet_Change event doesn't trigger repeatedly
• Efficient Targeting: Uses Intersect to only process cells that are actually edited in our monitored range, instead of looping the entire M3:M500 every time

Quick Testing Tip #

Type any case variation of "latest" into cells M3-M500—you should see the message box pop up immediately, and the invalid entry will be reverted to its previous value.

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