PDA

View Full Version : Data Validation



cchristner
01-11-2017, 02:42 AM
I want to set up a cell that has data validation based on the value of another cell. Is there a way to do that? An example of the issue I am having is one part number we use have values that have to be entered as a decimal. All the other part numbers that cell wont have any values. So basically if Cell B1 is = to 1 a decimal must be entered. If B1 = any other part number the cell can be anything. I am not sure if this is even possible so I figured I would ask.


Thank you

SamT
01-11-2017, 09:59 AM
I am not sure what you are saying/asking. Provide a couple of different examples. With all details.

cchristner
01-12-2017, 01:46 AM
I guess I did leave out a big part. So I have a code that wont allow an operator to save without a value being entered in G5. I need it set up the same way for one specific part number "100" in B1 that will require G13 to have a value. However if any other part number is in B1 G13 can be left blank and still allow them to save.

If Range ("G5").value =""Then
MsgBox " You must have a value in cell G5"

Bob Phillips
01-12-2017, 03:44 AM
Data Validation is not a good option, it is too easily overridden.

I would add a worksheet_change event to that cell and test the value in B1 in VBA.

SamT
01-12-2017, 11:01 AM
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Not DataValidated Then Cancel = True
End Sub
'Code for worksheet(Blah Blah), might as well check when the User leaves the sheet.

Private Sub Worksheet_Deactivate()
Dim X
X = DataValidated
End Sub



'In Standard Module

Private Function DataValidated() As Boolean
With Sheets("Blah Blah")
'Most likely scenario
If .Range("B1") <> 100 Then
DataValidated = True
Exit Function
End If

'B1 is 100
If .Range("G5") <> ??? Then 'Adjust to suit
MsgBox " You must have a value in cell G5"
.Range("G5").ClearContents
.Range("G5").Activate
Else
DataValidated = True
End If
End With

End Function