PDA

View Full Version : Need some newbie help with Cell validation



doctortt
03-28-2011, 04:28 PM
My boss asked me to program this stuff when I have absolutely no knowledge on this. Can you guys/gals help me out on this code?

I need to write some codes to validate the data in this one cell.

This cell must be numerical and greater than 0 but less than 1 million. I know how to write the latter, but I'm not sure what the codes are for controlling the cell to be numerical. In addition, if either one of them or both are false, I would like Excel to throw out an error message.

your help is appreciated.

nepotist
03-28-2011, 05:49 PM
You can use the data validation tool that is readily available. I am assuming that you have office 2007 or 2010. Go to Data tab and you should see the data validation option in "Data Tools". Remember to select the cell that you want to validate before you do this.

Click the down arrow then select data validation.

From the allow dropdown select Whole number - Data between and then enter min and max.

You record this to develop the macro and understand how it works.

doctortt
03-28-2011, 06:02 PM
The problem is that because of some office politics, I'm not allowed to use data validation under data tools, and I have the develop the codes myself.

nepotist
03-28-2011, 06:11 PM
Ok I recored the Macro for it and is as follows

Sub Macro1()
'
With Selection.Validation ' Change the selection for cell of your preference
.Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="0", Formula2:="1000000"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub

doctortt
03-28-2011, 06:19 PM
Hate to be annoying, can you point me how to create the same thing using "if" statements?

nepotist
03-28-2011, 06:26 PM
Function D_Valid(ByVal Target As Range)
If (Target < 0) Or (Target > 1000000) Then
MsgBox "Error Message"
End If
End Function


Once done, Say you wanna validate the cell A2 type the formula in anyother cell as =D_Valid(A2)

p45cal
03-29-2011, 04:51 AM
The problem is that because of some office politics, I'm not allowed to use data validation under data tools, and I have the develop the codes myself.This would be astonishing! Data validation is standard excel stuff. What else do office politics also prevent you from doing? - using formulae? It would be less surprising to hear that office politics prevented use of macros - but that seems to be OK in your office.

Could you expand on these politics?

shrivallabha
03-29-2011, 07:20 AM
"POLITICS IS the last resort of the scoundrels."
I thought Excel is like Cricket, a gentleman's game.

Use conditional formatting to change color though it won't raise a warning via msgbox. Or you can put this code in the worksheet.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
If Target.Value >= 0 & Target.Value <= 1000000 Then
MsgBox "Incorrect Entry. Please Provide Values between 0 to 1000000!"
End If
End If
End Sub

Change the cell address to the one which you need.