Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Mar 2001
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    cell must contain numerics (97)

    Valid entry in cell A1 must include at least 3 numeric characters.
    How can I get a warning to flash up if a user enters invalid data?

    regards
    cj

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: cell must contain numerics (97)

    You can use validation for this. The exact implementation depends on whether you want the entire entry to be a number, and whether it can be negative, and whether it should be a whole number.

    In the following I have assumed that you want the entry to be a whole number of at least three digits, i.e. it is greater than or equal to 100.

    Select cell A1
    Select Data | Validation...
    In the Allow dropdown list, select 'Integer' (or perhaps it is 'Whole Number', I am using a foreign-language version of Excel)
    In the Data dropdown list, select 'Greater than or equal to'.
    In the Minimum box that appears, enter 100.

    In the other tabs of the Validation dialog, you can specify a hint that appears whenever the user selects A1, and an error message.

  3. #3
    Star Lounger
    Join Date
    Mar 2001
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: cell must contain numerics (97)

    Thanks Hans,

    but this isn't the solution I need.
    Cell A1 is allowed to contain either alpha or numeric characters, or a mixture of both.
    The only stipulation is that there must be at least 3 numerics.

    c

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: cell must contain numerics (97)

    I don't hink you can do this with datavalidation. I think you need a change macro. This added to the sheet object in VB should work.

    Steve

    <pre>Option Explicit
    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim rng As Range
    Set rng = Range("A1")
    If Not Intersect(Target, rng) Is Nothing Then
    Dim x As Integer
    Dim sStripNum As String
    Dim sWord As String
    sWord = rng.Value
    sStripNum = sWord
    For x = 0 To 9
    sStripNum = Application.WorksheetFunction. _
    Substitute(sStripNum, x, "")
    Next x
    If Len(sWord) - Len(sStripNum) < 3 Then
    MsgBox ("Cell " & rng.Address & " must contain 3 numerics")
    Application.EnableEvents = False
    rng.ClearContents
    rng.Select
    Application.EnableEvents = True
    End If
    End If
    End Sub</pre>


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •