Prevent enter to move to the next cell if no character is typed

[Aussiebear]

Sorry but that was my mistake. Apparently you can't use Const with an array function.

As an alternative to Paul's code does this work?

Private requiredCellAddressesArray As Variant ' Declare at the top of the module, not inside the sub

Private Sub Worksheet_Activate()
    ' This event fires when the sheet becomes active.
    ' It's a good place to initialize our array of cell addresses once.
    ' This ensures the array is set up when the user first enters the sheet.
    If IsEmpty(requiredCellAddressesArray) Then 
        ' Only initialize if not already done
        Call InitializeRequiredCellsArray
   End If
   ' Optional: Ensure the first cell is activated when the sheet is activated
    If Not IsEmpty(requiredCellAddressesArray) Then
        Me.Range(requiredCellAddressesArray(LBound(requiredCellAddressesArray))).Activate
    End If
End Sub

Private Sub InitializeRequiredCellsArray()
    ' Define your non-contiguous cells here.
    ' This needs to be a regular variable, populated at runtime.    
    requiredCellAddresses Array = Array( "B4", "B5", "B6", "B7", "B8", "B9", "B10", "B11", "B12", "B13", "B14", "B15", "B16", "C16", "D16", _        
    "E16", "F16", "G16", "G15", "G14", "G13", "G12", "G11", "F11", "E11", "E10", "E9", "E8", "F8", "G8", "H8", "I8", "J8", "J9", "J10", "J11", _
    "J12", "J13", "K13", "L13", "M13", "N13", "N12", "N11", "N10", "O10", "P10", "Q10", "Q9", "Q8", "R8", "S8", "S7", "S6", "S5", "R5", "Q5", _
    "P5", "O5", "N5", "M5", "M4", "L4", "K4", "J4", "I4", "I5", "H5", "G5", "F5", "F4", "F3", "G3", "G2", "H2", "I2", "J2", "K2", "L2", "M2", "N2", _
    "O2", "P2", "Q2", "R2", "R3", "S3", "T3", "U3", "V3", "V4", "V5", _        "V6", "V7", "V8", "V9", "W9", "X9", "X8", "X7", "Y7", "Y6", "Y5", "Y4", _
    "X4", "X3", "X2", "Y2", "Z2", "AA2", "AA3", "AA4", "AA5", "AA6", "AA7", "AA8", "AA9", "AA10", "Z10", "Z11", "Z12", "Z13", "Z14", "Z15", _
    "Y15", "X15", "W15", "V15", "U15", "U16", "T16", "S16", "R16", "Q16", "P16", "O16", "N16", "M16" )
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    ' This event fires when the sheet becomes active.
    ' It's a good place to initialize our array of cell addresses once.
    ' This ensures the array is set up when the user first enters the sheet.
    If IsEmpty(requiredCellAddressesArray) Then 
        ' Only initialize if not already done
        Call InitializeRequiredCellsArray
    End If
    Dim currentCellAddress As String
    Dim previousCellAddress As String
    Dim currentCellIndex As Long
    Dim i As Long
    Dim blnIsValidMove As Boolean
    ' Turn off events to prevent re-triggering this macro during our own actions
    Application.EnableEvents = False
    On Error GoTo CleanUp
    ' Check if the selected cell is one of our required cells
    blnIsValidMove = False
    currentCellIndex = -1 
    ' Initialize to not found
    For i = LBound(requiredCellAddressesArray) To UBound(requiredCellAddressesArray)
        If Target.Address(False, False) = requiredCellAddressesArray(i) Then
            currentCellIndex = i
            blnIsValidMove = True
            Exit For
        End If
    Next i
    ' If the selected cell is not one of our designated cells, or it's a multi-selection
    If Not blnIsValidMove Or Target.Cells.Count > 1 Then
        Dim firstEmptyRequiredCellIndex As Long
        firstEmptyRequiredCellIndex = -1
        ' Find the first empty cell in the sequence
        For i = LBound(requiredCellAddressesArray) To UBound(requiredCellAddressesArray)
            Set currentCell = Me.Range(requiredCellAddressesArray(i))
            If IsEmpty(currentCell.Value) Or Trim(currentCell.Value) = "" Then
                firstEmptyRequiredCellIndex = i
                Exit For
            End If
        Next i
        ' If there's an empty required cell, activate it
        If firstEmptyRequiredCellIndex <> -1 Then
            MsgBox "Please fill in all preceding mandatory fields. Cell " & requiredCellAddressesArray(firstEmptyRequiredCellIndex) & " is blank.", vbExclamation, "Mandatory Field"         
            Me.Range(requiredCellAddressesArray(firstEmptyRequiredCellIndex)).Activate
        Else
            ' All required cells are filled, allow movement freely
            ' No action needed, user can move elsewhere
        End If
    Else 
        ' User selected a designated single cell in the sequence
        ' Check if the immediately preceding cell in the sequence is filled
        If currentCellIndex > LBound(requiredCellAddressesArray) Then
            Set previousCell = Me.Range(requiredCellAddressesArray(currentCellIndex - 1))
            If IsEmpty(previousCell.Value) Or Trim(previousCell.Value) = "" Then
                MsgBox "Please fill in cell " & previousCell.Address(False, False) & " before moving to " & Target.Address(False, False) & ".", vbExclamation, "Mandatory Field"
                previousCell.Activate 
                ' Go back to the previous, unfilled cell
            End If
        End If
        ' Additionally, ensure no cells earlier in the sequence were skipped
        For i = LBound(requiredCellAddressesArray) To currentCellIndex - 1
            Set currentCell = Me.Range(requiredCellAddressesArray(i)) 
            ' Use currentCell for iteration now
            If IsEmpty(currentCell.Value) Or Trim(currentCell.Value) = "" Then
                MsgBox "Please fill in all preceding mandatory fields. Cell " & currentCell.Address(False, False) & " is blank.", vbExclamation, "Mandatory Field"
                currentCell.Activate 
                ' Go back to the first skipped cell
                Exit For 
                ' Stop checking once the first skipped cell is found
            End If
        Next i
    End If
    CleanUp:    Application.EnableEvents = True
    If Err.Number <> 0 Then
        MsgBox "An error occurred: " & Err.Description, vbCritical, "Error"
    End If
End Sub