• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 281
  • Last Modified:

Setting Data Validation Rules with A Function

Hi,

I have the attached Excel spreadsheet designed for data entry. For the columns titled MUAC COLOR and MUAC NUMBER, I would like to prevent the data entry clerks from entering data in these cells if the age of the person is greater than 5 years old.

I already have data validation rules set for MUAC COLOR to allow a drop down list to appear, and I have restricted the value they can enter for the MUAC NUMBER as it should be between 50 and 300 mm.  

However, I want to further restrict the user from entering values in these cells if the value in the AGE column is not between 1-5 years old because this MUAC questions are only relevant for children aged 1-5 years old.  (Last year when I got the data back, there were many cells entered for people who were aged 35 or 68 so I know that's a data entry error. I want to alert the user that they are about to make an error before it happens. )

I don't know how I should do this with a function or with more data validation rules or what?

Any help is greatly appreciated.

thanks,
carlynne
ERA-Reg-Template-2011.xlsx
0
Carlynne
Asked:
Carlynne
  • 4
  • 2
1 Solution
 
SiddharthRoutCommented:
Try this Sample File. Since you already have a data validation in Age column so I am not checking for values less than 0 in the code.

Sid

Code Used

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    On Error GoTo Whoa
    
    If Not Intersect(Target, Columns(15)) Is Nothing Then
        If Target.Offset(, -3).Value > 5 Then
            MsgBox "Age > 5"
            Target.ClearContents
        End If
    End If
letscontinue:
    Application.EnableEvents = True
    
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume letscontinue
End Sub

Open in new window

ERA-Reg-Template-2011.xlsm
0
 
CarlynneAuthor Commented:
Sid,

It seems to work great! Thanks so much!

My only question is can you give me the steps to insert the code from the Excel menu so I know in the future?

carlynne
0
 
SiddharthRoutCommented:
Sure.

Here is the quickest way to access the Visual Basic Editor.

From the Excel Sheet Press Alt+F11 :)

Sid
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
SiddharthRoutCommented:
Were you able to figure it out? If not which Excel version are you using so that I can explain with screenshots. :)

Sid
0
 
CarlynneAuthor Commented:
Yes, I got it! Alt f11 works.

carlynne
0
 
SiddharthRoutCommented:
Gr8 :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now