Determine if cell has a dropdown list

[UHsoccer]

Is there a way using a macro to determine which cells have a dropdown list and then retrieve the name?

 

[MickG]

If we are talking validation lists (cells addresses) then :-
There's probably a better way than this, but it seems to work .

[COLOR=navy]Sub[/COLOR] MG05Apr59
[COLOR=navy]Dim[/COLOR] v [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] Txt [COLOR=navy]As[/COLOR] [COLOR=navy]String[/COLOR]
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] v [COLOR=navy]In[/COLOR] ActiveSheet.UsedRange
    [COLOR=navy]On[/COLOR] [COLOR=navy]Error[/COLOR] [COLOR=navy]Resume[/COLOR] [COLOR=navy]Next[/COLOR]
    Txt = Txt & Chr(10) & Chr(-CLng(v.Validation.InCellDropdown) + 31) & v.Address
[COLOR=navy]Next[/COLOR] v
MsgBox Txt
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[UHsoccer]

Thanks, I got that working for me. Always curious how one arrives at that code.....

 

[MickG]

Its really a bit of trial an error.
This line "Txt = Txt & Chr(10) & Chr(-CLng(v.Validation.InCellDropdown) + 31) & v.Address" is formed by:_
If you just loop throught range, with "v.Validation.InCellDropdown" in the loop it will return "True" if validation cell found, but if not found an error occurs
Start loop with "on error resume next" to override any Errors.
if you then change that line to "-CLng(v.Validation.InCellDropdown" by adding "-Clng" this converts as "string" to a "long" variable, which changes "True " to "-1",by adding a "-" at the front, it changes "-1" to "1"
At the end of the code line the address is returned by "v.address" but you don't want the "1" in front of it. so by adding 31 and "Chr" in front of it , it becomes the "Asci " code chr(32) which equal a blank space, and thats it !!!

 

[UHsoccer]

I am saving your explanation for future reference. Cool piece of logical thinking

 

[aerosumi]

If you want to avoid playing with blanks you could use the following structure

Sub MG05Apr59()
Dim v As Range
Dim Txt As String
For Each v In ActiveSheet.UsedRange
    On Error Resume Next
    If v.Validation.InCellDropdown Then
        Txt = Txt & Chr(10) & v.Address
    End If
Next v
Debug.Print Txt
End Sub