tricky data validation formula

route217
route217 used Ask the Experts™
on
Hi Experts using excel 2010

Is it possible to have a if condition with data validation...

So I have a list in data validation against a named range in the source field....=Projects...

I want to amend so that if cell G7 is blank then make H7 blank and not show current project name that was pre selected form the data validation list....
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solutions Only
Rgonzo1971
Top Expert 2016

Commented:
Could you send a dummy?
route217Junior

Author

Commented:
Sorry....cannot from my current location....apologies..
Rgonzo1971
Top Expert 2016

Commented:
In which cell is the validation?
PMI ACP® Project Management

Prepare for the PMI Agile Certified Practitioner (PMI-ACP)® exam, which formally recognizes your knowledge of agile principles and your skill with agile techniques.

Start Today!
route217Junior

Author

Commented:
H7...
Top Expert 2016
Commented:
Then try in the corresponding sheet module ( change range at line 10) if c7 is empty it will delete H7
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("C7")) Is Nothing Then
    If Me.Range("C7") = "" Then
        Me.Range("H7") = ""
        Me.Range("H7").Validation.Delete
    Else
        With Me.Range("H7").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=$A$1:$A$3"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
    End If
End If
End Sub

Open in new window

route217Junior

Author

Commented:
Ok rgonzo that works fine is c7 is blank...deletes the named range....now if c7 has a value I need the data validation back in....=Projects.
Professor JMicrosoft Excel Expert
Top Expert 2014

Commented:
.
route217Junior

Author

Commented:
Thanks for the excellent feedback experts and prof Jim jam too
Professor JMicrosoft Excel Expert
Top Expert 2014
Commented:
route217

please put this code in that worksheet object module.

it will work as you need. it will not clear your data validation.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("G7")) Is Nothing Then
        If Target = "" Then
            Target.Offset(0, 1) = vbNullString
        End If
    End If

End Sub

Open in new window

route217Junior

Author

Commented:
Rgonzo.....I have changed line 10 to Formula1:="=Projects"....

Works fine.....however if any cell in the range c7:c1000 is blank then h7:h1000 is blank also and if we add a value in the range c7:c1000 to any cell then reapply the data validation list...
Professor JMicrosoft Excel Expert
Top Expert 2014

Commented:
route217

did you try my code.  it will not delete the data validation in the first place.
Rgonzo1971
Top Expert 2016

Commented:
then try it will hide in white the font if c7 is empty

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("C7")) Is Nothing Then
    If Me.Range("C7") = "" Then
        Me.Range("H7").Font.Color = vbWhite
        Me.Range("H7").Validation.Delete
    Else
        Me.Range("H7").Font.Color = vbBlack
        With Me.Range("H7").Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:="=$A$1:$A$3"
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = ""
            .InputMessage = ""
            .ErrorMessage = ""
            .ShowInput = True
            .ShowError = True
        End With
    End If
End If
End Sub

Open in new window

Rgonzo1971
Top Expert 2016

Commented:
the line 10 is for the validation list range or what is your validation
route217Junior

Author

Commented:
This question need a part 2....thanks for the excellent feedback and service.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial