Data Validation

[cchristner]

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]

I am not sure what you are saying/asking. Provide a couple of different examples. With all details.

[cchristner]

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]

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]

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