Results 1 to 10 of 10
  1. #1
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    prevent blank entry in cell (Excel 2000)

    In an Excel spreadsheet, how do you make sure that a particular cell has an entry? I can set up a Validation for the data in thhe cell, but that doesn't prevent just skipping by it, leaving it empty, and clicking in another cell. Is there a way?

  2. #2
    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: prevent blank entry in cell (Excel 2000)

    One way is to "seed it" with some default value before you give it to them. To change it will require using the datavalidation which can prevent blanks.

    You can set up a worksheet event that triggers and checks for empty cells in a range or in a particular row or whatever, but you need to provide more info on what you need. You can trigger it, for example, if an entry is made in col a, that they must fill in col b, then c, then d, etc before you give them back control.

    Another way is to use a userform, that requires all the input before it unloads and then fills in the appropriate cells.

    Steve

  3. #3
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: prevent blank entry in cell (Excel 2000)

    Steve
    This is a worksheet that computes insurance quotes. Three critical fields that need data are A4 (age, must be between 18 and 64), B4 (sex, must be m or f), C4 (area, 3 digits between 800 and 816). Data validation will take care of the correct values in the cells providing that the user enters the cells, but the user can just click into another cell without entering into one of the three critical cells, A4 or B4 or C4. Granted, the entire form will not compute properly without these entries, but it would be better if some blanket code were set up to insist upon values in these 3 cells.

    Hope that gives a little more information.
    Tom

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: prevent blank entry in cell (Excel 2000)

    What do you want to trigger the check? going to any cell other than A4, B4, or C4?
    Legare Coleman

  5. #5
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: prevent blank entry in cell (Excel 2000)

    Yes. If the user clicks into any other cell without first entering a value in all 3 fields (A4 and B4 and C4) I would like a message that alerts regarding whichever of those 3 cells has been left blank. And sending the focus to that cell would be a bonus.

    Tom

  6. #6
    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: prevent blank entry in cell (Excel 2000)

    Does this do it? I assumed it was "Sheet1" (change as appropriate in "SetVariables"

    Add this to a module:

    <pre>Option Explicit
    Public sWks As String
    Public sAgeRange As String
    Public iMinAge As Integer
    Public iMaxAge As Integer
    Public sSexRange As String
    Public sSexChoices As String
    Public sAreaRange As String
    Public iMinArea As Integer
    Public iMaxArea As Integer

    Sub SetVariables()
    sWks = "Sheet1"
    sAgeRange = "A4"
    iMinAge = 18
    iMaxAge = 64
    sSexRange = "B4"
    sSexChoices = "MF"
    sAreaRange = "C4"
    iMinArea = 800
    iMaxArea = 816
    End Sub

    Sub ValidateData()
    Dim vAge
    Dim vSex
    Dim vArea

    vAge = Range(sAgeRange).Value
    vSex = Range(sSexRange).Value
    vArea = Range(sAreaRange).Value

    'If vAge = "" Then vAge = (iMinAge + iMaxAge) / 2
    'If vSex = "" Then vSex = "M"
    'If vArea = "" Then vArea = (iMinArea + iMaxArea) / 2

    Do While Not (IsNumeric(vAge)) Or _
    Val(vAge) <= iMinAge Or Val(vAge) > iMaxAge
    vAge = Val(InputBox("Enter the age" & vbCrLf & _
    "Note: Age must be between " & iMinAge & " and " & iMaxAge))
    Loop
    Application.EnableEvents = False
    Range(sAgeRange).Value = vAge
    Application.EnableEvents = True

    Do While InStr(sSexChoices, vSex) = 0 Or Len(vSex) <> 1
    vSex = UCase(InputBox("Enter the age" & vbCrLf & _
    "Note: Sex must be either M or F"))
    Loop
    Application.EnableEvents = False
    Range(sSexRange).Value = vSex
    Application.EnableEvents = True

    Do While Not (IsNumeric(vArea)) Or _
    Val(vArea) < iMinArea Or Val(vArea) > iMaxArea
    vArea = Val(InputBox("Enter the Area" & vbCrLf & _
    "Note: Area must be between " & iMinArea & _
    " and " & iMaxArea))
    Loop
    Application.EnableEvents = False
    Range(sAreaRange).Value = vArea
    Application.EnableEvents = True
    End Sub</pre>


    Add these to the thisWorkbook object
    <pre>Option Explicit
    Private Sub Workbook_Open()
    SetVariables
    If ActiveSheet.Name = sWks Then ValidateData
    End Sub

    Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    If Sh.Name = sWks Then ValidateData
    End Sub

    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
    If Sh.Name = sWks And Not Intersect(Target, _
    Union(Range(sAgeRange), Range(sSexRange), Range(sAreaRange))) _
    Is Nothing Then ValidateData
    End Sub</pre>


    When you open the workbook, you will initialize the variables (define cells and ranges for values). If you are on the sheet to validate, the validate procedure will run. the validate checks for valid entries in the cells.

    If the you open on another sheet, when you activate the sheet of interest the validate procedure will run.

    When/If you change the contents of the cells of interest the validate procedure will run ensuring only valid data in the ranges.

    Steve

  7. #7
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: prevent blank entry in cell (Excel 2000)

    Steve
    Thanks. I'll give it a go.
    Tom

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: prevent blank entry in cell (Excel 2000)

    The following is a little simpler than Steve's solution. The code below, placed in the worksheet selection change event routine for the worksheet in question will prevent the user from going to any other cell if A4, B4, or C4 does not contain a value. You can then use valadation to enforce the correct values.

    <pre>Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim rngNotEmpty As Range, ocell As Range
    Application.EnableEvents = False
    Set rngNotEmpty = Union(Range("A4"), Range("B4"), Range("C4"))
    If Intersect(Target, rngNotEmpty) Is Nothing Then
    For Each ocell In rngNotEmpty
    If ocell.Value = "" Then
    ocell.Select
    MsgBox "Cells A4, B4, and C4 must have values"
    Exit For
    End If
    Next ocell
    End If
    Application.EnableEvents = True
    End Sub
    </pre>

    Legare Coleman

  9. #9
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: prevent blank entry in cell (Excel 2000)

    Legare
    Thanks. Happy to have any and all solutions. Appreciate it.
    Tom

  10. #10
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: prevent blank entry in cell (Excel 2000)

    Thanks to both Steve and Legare.
    Both of this solutions work very well.

    All the best.
    Tom

Posting Permissions

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