CODEWITHSUNDEEP
vbaexcel
Miksel
Miksel

Reputation: 13

Data validation based on cell value

I am adding a data validation list based on a cell value. If value in cell B29 = "text1" then add data validation in cell D29. If the cell B29 has a different value then a formula has to be added to cell D29 and the data validation has to be removed.

Here is an example of the macro:

If Range("B29").Value = "Text1" Then
Range("D29").Value = ""
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=INDIRECT(B29)"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
ElseIf Range("B29").Value = "Value1" Then
 Range("D29").Formula = "=IF(Sheet2!B9,VLOOKUP(Sheet2!B8,'Team Target Tabel'!C2:E17,2,FALSE),"""")"
 Range("D29").Validation.Delete
ElseIf Range("B29").Value = "Value2" Then
 Range("D29").Formula = "=IF(Sheet2!B9,VLOOKUP(Sheet2!B8,'Team Target Tabel'!C2:E17,2,FALSE),"""")"
 Range("D29").Validation.Delete
ElseIf Range("B29").Value = "Value3" Then
 Range("D29").Formula = "=IF(Sheet2!B9,VLOOKUP(Sheet2!B8,'Team Target Tabel'!C2:E17,2,FALSE),"""")"
 Range("D29").Validation.Delete
 End If
End Sub

Cell B29 is also a data validation cell which has 4 values. If the value is text1 then cell D29 has to change into a data validation list but I have to run the macro manually to do this. When D29 is a data validation list and I change the value in cell B29 I have to run the macro again (manually) to change it back to the formula.

Upvotes: 1

Views: 2354

Answers (2)

A.S.H
A.S.H

Reputation: 29352

You could capture the Worksheet_change event to re-run the macro when D29 changes.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Me.Range("B29")) Is Nothing Then
        Call myMacroToChangeValidationOfD29
    End If
End Sub

BTW, why With Selection.Validation not With Range("D29").Validation ??

Upvotes: 1

Lajos Arpad
Lajos Arpad

Reputation: 77045

I would use StrComp to compare strings, example:

If StrComp(Range("B29").Value, "text1") = 0 Then
    'Something
End If

You want to check B29 against "text1", however, you are checking it against "Text1". You might have a problem of case sensitivity. Your else cases are similar, you could simplify them to a single else, like this:

If StrComp(Range("B29").Value, "text1") = 0 Then
Range("D29").Value = ""
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=INDIRECT(B29)"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
Else
 Range("D29").Formula = "=IF(Sheet2!B9,VLOOKUP(Sheet2!B8,'Team Target Tabel'!C2:E17,2,FALSE),"""")"
 Range("D29").Validation.Delete
End Sub

Upvotes: 0

Related Questions