check validation data, return TRUE or FALSE

ape · Nov 6th 2018

I have validation data " Yes and NO" set up on a sheet. How can I check if selection is "YES" or "NO"? e.g return TRUE if YES, False if NO
Any help will be appreciated.
Thank you

Code;

Code

Private Sub NewDataSheet(SheetName As String)
Dim Ws As Worksheet


.Range("D1").Value = "YES"
.Range("E1").Value = "NO"
Set Ws = Worksheets(SheetName)
With Ws.Range("B1").Validation
.Add xlValidateList, xlValidAlertStop, xlBetween, "=$D$1:$E$1"
.InCellDropdown = True
.InputTitle = True
.ShowInput = True
End With


End Sub

Display More

graha_karya · Nov 7th 2018

TRY THIS

Code

Public range1 As Range
Private Sub NewDataSheet(SheetName As String)
Dim Ws As Worksheet
 
Set Ws = Worksheets(SheetName)
Set range1 = Ws.Range("D1:E1"): range1.Value = Array("YES", "NO")
With Ws.Range("B1").Validation
    .Delete ' reset all validation
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
        Formula1:="='" & SheetName & "'!" & range1.Address
End With
 
End Sub


Sub tes()
NewDataSheet "Sheet1"
MsgBox "Current Value Validation is !!! " & range1.Find([b1].Value), 48
End Sub

Display More

graha_karya · Nov 7th 2018

sorri change to

Code

Public range1 As Range
Private Sub NewDataSheet(SheetName As String)
Dim Ws As Worksheet
 
Set Ws = Worksheets(SheetName)
Set range1 = Ws.Range("D1:E1"): range1.Value = Array("YES", "NO")
With Ws.Range("B1").Validation
    .Delete ' reset all validation
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
        Formula1:="='" & SheetName & "'!" & range1.Address
End With
 
End Sub


Sub tes()
NewDataSheet "Sheet1"
MsgBox "Current Value Validation is !!! " & IIf(range1.Find([b1].Value) = "NO", False, True)
End Sub

Display More

ape · Nov 7th 2018

Great thanks for the code, more efficient way of setting up. For "YES" or "NO" (hide or show sheet) decision I have setup code below which works well.

For Each Ws In ThisWorkbook.Worksheets
.
.
.
For Each CellRange In Ws.Cells.SpecialCells(xlCellTypeAllValidation).Cells
With CellRange
If .Validation.Type = xlValidateList Then
If .Value = .Range("D1:D1").Value Then
Ws.Visible = True
Else
Ws.Visible = False
End If
End If
End With
Next CellRange
.
.
.
Next Ws

check validation data, return TRUE or FALSE

Participate now!

Share

Users Viewing This Thread