?
Solved

Setting Data Validation Rules with A Function

Posted on 2011-04-19
6
Medium Priority
?
279 Views
Last Modified: 2012-05-11
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
Comment
Question by:Carlynne
  • 4
  • 2
6 Comments
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35430283
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
 

Author Comment

by:Carlynne
ID: 35430353
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
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 1000 total points
ID: 35430386
Sure.

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

From the Excel Sheet Press Alt+F11 :)

Sid
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 30

Expert Comment

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

Sid
0
 

Author Comment

by:Carlynne
ID: 35430475
Yes, I got it! Alt f11 works.

carlynne
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35430476
Gr8 :)
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

755 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question