Data validation query

[blackie42]

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]

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]

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]

Sorry, I am poor in English.
Although I read your #1 & #3 repeatedly,
I can't understand what you want to do.

[Aussiebear]

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]

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]

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