Data Validation Function

nicolai.adamsky

New Member
Joined
Jun 29, 2007
Messages
4
I'm looking to make a function to validate a year. I want to make a sub(x,y,z) and say that if z is not an integer, a message box pops up. I also want if z is not between x and y inclusive, then a message box pops up. How do i code the sub? Thanks alot.
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,456
Hello nicolai.adamsky, welcome to the board.
Your request is a little vague as to what you have that will be represented by the x, y & z.
Assuming you mean them to represent cell values (that I also assume will contain years),
you can try something like this.
Code:
Sub ValidateTheYear()
Dim x%, y%, z%
On Error Resume Next
'Change 'Sheet1' to the real name of the sheet of interest
With Sheets("Sheet1")
  x = .Range("A1").Value 'Beginning year
  y = .Range("A2").Value 'Ending Year
  z = .Range("A3").Value 'Year to validate
End With

If Not Err.Number = 0 Then _
   MsgBox "One or more of the years are not valid.", , "Invalid Year(s)": Err.Clear: Exit Sub

If z < x Or z > y Then _
   MsgBox "' " & z & " ' is outside of the given parameters.", , "Invalid Year": Exit Sub

'Replace this MsgBox with your code if the year is validated
MsgBox "' " & z & " ' is an acceptable year.", , "All's cool."

End Sub
If this is nothing like what you meant/had in mind then can you elaborate a bit?

Hope it helps.
 

Forum statistics

Threads
1,181,658
Messages
5,931,271
Members
436,785
Latest member
KingGideon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top