Determine if cell has a dropdown list

UHsoccer

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

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
If we are talking validation lists (cells addresses) then :-
There's probably a better way than this, but it seems to work .
Code:
``````[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

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

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 !!!

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

If you want to avoid playing with blanks you could use the following structure
Code:
``````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``````

Replies
3
Views
556
Replies
10
Views
469
Replies
4
Views
559
Replies
1
Views
449
Replies
1
Views
262

1,203,094
Messages
6,053,503
Members
444,667
Latest member
KWR21

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back