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
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
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.