none
Lookup if there is (inCellDropdown) in a sheet RRS feed

  • Question

  • HI

    I wonder how to lookup using (VBA) if "a sheet" has a cell with (Validation.InCellDropdown=True) and if I could do that, how to get the "selected-value" of that cell?

    Best regards



    • Edited by Sam1968 Tuesday, February 12, 2019 2:44 AM
    Tuesday, February 12, 2019 1:50 AM

All replies

  • Hi Sam 1968,

    I've made a sample and shared it via OneDrive.  Please download "Search_Validation.xlsm".

    [note]
      (1) Data Validation is in cell [C5] and [E7]
      (2) button [Search All at once] will show all cells with Data Validation at once
           button [Search Each one by one] will show a cell with Data Validation one by one, its cell address and value. 
      (3) search range is [B2:H22] (you can change the range as you like)

      



    [Code]

    ' ---[Search All at once]
    Private Sub btn_Search_All_Click()
        ActiveCell.SpecialCells(xlCellTypeAllValidation).Select
    End Sub
    
    ' ---[Search Each one by one]
    Private Sub btn_Search_Each_Click()
        On Error Resume Next
        ' ---
        Dim myRange As Range
        For Each myRange In Range("B2:H11")
            Err.Clear
            If (myRange.Validation.Type > 0) Then
                If (Err.Number = 0) Then
                    MsgBox "cell [" & fnc_GetAlphabet(myRange) & myRange.Row & "]" _
                        & Chr(13) & "value=" & myRange.Value
                End If
            End If
        Next myRange
        ' ---
        On Error Resume Next
    End Sub
    
    ' --- column Number > column Alphabet
    Private Function fnc_GetAlphabet(ByVal myRange As Range) As String
        Dim adrs As String
        adrs = myRange.Address
        ' ---
        fnc_GetAlphabet = Split(adrs, "$")(1)
    End Function
    


    Regards,

      

    Ashidacchi -- https://ssl01.rocketnet.jp/hokusosha.com/default.html



    • Edited by Ashidacchi Wednesday, February 13, 2019 12:59 AM
    Wednesday, February 13, 2019 12:55 AM