Data validation in excel 2007

Question

I am trying to restrict the inputs of certain cells in my excel worksheet as follows: 1-7,10,12, which means that only numbers 0 to 9 and symbols - and , can appear in the cell. I would like to ideally handle it in a non-vba based Data validation manner, but even a vba based solution will be ok.

EDIT - There is one keyword which would be an exception, 'Fixed', if I see this word it would be allowed.

Answer 1

The VBA version using Regex object: I just wrote the function. You can simply call this function within the Sheet change event. (like how Siddharth has used). And one other thing, each time user enters wrong characters, the function deletes them all :D... Then again, you need to be aware that to make sure this operation happens within a particular range of your choice.. coz otherwise it can erase any cell that's being changed!!! Given Siddtharth's post for infinite loops within this `worksheet change event, I have edited the code to include that bit as well.

    Option Explicit

    '-- within sheet change event
    Private Sub Worksheet_Change(ByVal Target As Range)
       On Error GoTo Zoo
       Application.EnableEvents = False
       Call NumbersAndCommaDashOnly(Target)

       GetBack:
       Application.EnableEvents = True
       Exit Sub
   Zoo:
       MsgBox Err.Description
       Resume GetBack
    End Sub

Function NumbersAndCommaDashOnly(ByRef rngInput As Range) As String    
Dim objRegex As Object
Dim strInput As String

Set objRegex = CreateObject("VBScript.RegExp")
objRegex.IgnoreCase = True
objRegex.Global = True
objRegex.Pattern = "^[-,0-9]+$|^[Fixed]$"

If Not IsNull(rngInput.Value) Then
    strInput = rngInput.Value
Else
    NumbersAndCommaDash = "Empty Range"
    rngInput.Value = ""
    Exit Function
End If

If objRegex.Test(rngInput.Value) Then
    NumbersAndCommaDash = objRegex.Replace(rngInput, "")
Else
    NumbersAndCommaDash = "No numbers found"
    rngInput.Value = ""
End If

End Function

• For an Excel Formula based solution, you may look into this MSDN article.

Answer 2

Here is a VBA Approach for just cell A1.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim i As Long

    On Error GoTo Whoa

    Application.EnableEvents = False

    If Not Intersect(Target, Range("A1")) Is Nothing Then
        If Len(Range("A1").Value) <> 0 Then
            For i = 1 To Len(Range("A1").Value)
                Select Case Asc(Mid(Range("A1").Value, i, 1))
                '~~> Check for 0-9, "," and "-"
                Case vbKey0 To vbKey9, 44, 45
                Case Else
                    Range("A1").ClearContents
                    MsgBox "Invalid Value"
                    Exit For
                End Select
            Next
        End If
    End If

Letscontinue:
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume Letscontinue
End Sub

The code goes in the Sheet1 code area.

Screenshot (Code in Action)

FOLLOWUP to the recent edit in the question

Change the line

If Len(Range("A1").Value) <> 0 Then

to

If Len(Range("A1").Value) <> 0 And _
UCase(Range("A1").Value) <> "FIXED" Then