PDA

View Full Version : Data validation query



blackie42
10-19-2016, 06:56 AM
Hi,

I have a dropdown list in column A with the following list: Open, Closed, Scrub

Is it possible to prevent 'closed' from being selected (putting an error message out) if the adjacent cell is not a date in format
DD/MM/YYYY.

many thanks
Jon

mana
10-22-2016, 07:29 AM
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range
Dim myList As String

With Range("a1")
On Error Resume Next
Set r = Intersect(Target, .Offset(, 1))
On Error GoTo 0
If r Is Nothing Then Exit Sub


Application.EnableEvents = False
.ClearContents
Application.EnableEvents = True

If IsDate(r) Then
r.NumberFormat = "dd/mm/yyyy"
myList = "Open, Closed, Scrub"
Else
myList = "Open,Scrub"
End If

With .Validation
.Delete
.Add Type:=xlValidateList, Operator:=xlEqual, Formula1:=myList
End With
End With


End Sub

blackie42
10-22-2016, 11:12 AM
Hi Mana,

Apologies the dropdown is in column G and I already have some code referencing that column (which you kindly provided) as follows


Dim r As Range
Dim c As Range
On Error Resume Next
Set r = Intersect(Target, Columns("G"))
On Error GoTo 0
If r Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each c In r
If c.Value = "Scrub" Then c.Offset(, 3).ClearContents
Next

Any chance could merge the code

Many thaks
Jon

mana
10-22-2016, 04:42 PM
Sorry, I am poor in English.
Although I read your #1 & #3 repeatedly,
I can't understand what you want to do.

Aussiebear
10-22-2016, 09:19 PM
Does this help?


Option Explicit

Private Sub WorkSheet_Change(ByVal Target As Range)
Dim r As Range
Dim c As Range
Dim myList As Range
Set r = Intersect(Target, Columns("G"))
If r Is Nothing Then Exit Sub
On Error GoTo 0
Application.EnableEvents = False
For Each c In r
If IsDate(r) Then
r.NumberFormat = "dd/mm/yyyy"
myList = "Open, Closed, Scrub"
Else
myList = "Open,Scrub"
End If
If c.Value = "Scrub" Then c.Offset(, 3).ClearContents
Next
With .Validation
.Delete
.Add Type:=xlValidateList, Operator:=xlEqual, Formula1:=myList
End With
End With
End Sub

blackie42
10-23-2016, 01:47 AM
AussieBear - thanks for input bug bugs out @ With. Validation

Mana,

To explain a little better (hopefully)

The datalist item 'Scrub' once selected in column G needs to blank the 3rd cell along - the original code works OK.

The datalist item 'Closed' once selected in column G needs to check the adjacent (next) cell along to ensure there is a date entered - if no date then 'Closed' can not be selected.

I've checked the code for 'Closed' in another spread-sheet and unfortunately when selecting closed in A1 it does not check B1 for a date.

What I would like is for both these datalist options to work together if possible

So in col G select 'Scrub' and col J (and K would be good too) contents cleared, select 'Closed' , check if date present in H and if so allow item to be selected

many thanks for your time

regards
Jon

blackie42
10-25-2016, 02:03 AM
Any chance the closed' data option can be referenced to the adjacent cell if it not empty - that's f its not possible to check for the date

thanks
Jon