Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Must enter value (2003)

    Loungers,

    I'm need to set up a rule (like in validation) where a value (numeric) must be entered into a cell, if a value in say A1 is greater than 0, then a value must be entered in say B1 - if not entered then a message appear detailing requirement - like in validation

    I've tried data validation, but as I understand it this only related to the value entered in the cell related to the validation rule

    Any thoughts?

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

    Re: Must enter value (2003)

    Excel is not the ideal application for this kind of thing. If you really need this, use a database application such as Access.

    You could use conditional formatting to draw attention to missing values (you can refer to other cells in the conditions for conditional formatting).

    It might be possible to use the Worksheet_Change and Worksheet_SelectionChange events to try to enforce data entry, but it would be rather tricky.

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

    Re: Must enter value (2003)

    Validation probably will not work for what you want. What do you want to trigger the message appearing? When the user tries to close the workbook? When the user changes to a different worksheet?
    Legare Coleman

  4. #4
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Re: Must enter value (2003)

    Thanks Hans - will look at other options

  5. #5
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Re: Must enter value (2003)

    Legare

    Thanks for the reply - I was hoping that a message would appear after moving to the next cell if no data was entered - as with data validation

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

    Re: Must enter value (2003)

    Does the code below, placed in the module behind the worksheet do what you want?

    <code>
    Option Explicit
    Dim oPrevCell As Range

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim dblVal As Double
    If Not oPrevCell Is Nothing Then
    If Not Intersect(oPrevCell, Range("B1")) Is Nothing Then
    If Range("A1").Value <> "" Then
    dblVal = 0
    On Error Resume Next
    dblVal = Range("A1").Value
    If dblVal > 0 And Range("B1").Value = "" Then
    MsgBox "Cell B1 must not be empty."
    Application.EnableEvents = False
    Range("B1").Select
    Application.EnableEvents = True
    Exit Sub
    End If
    End If
    End If
    End If
    Set oPrevCell = Target
    End Sub
    </code>
    Legare Coleman

  7. #7
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Re: Must enter value (2003)

    Legare

    Sorry about the delay in replying - been away.

    Thanks for the code will give it a go

Posting Permissions

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