ExcelBanter - View Single Post - How do I get a reference to a control on a sheet?

Melanie Breden

I am using VBA in Excel XP and Win 2k.

I have several drop down controls on a sheet. The control are NOT ActiveX objects.

How do I loop through all the controls on the sheet and set them all to list index 0 (the first choice)?

try this:

Sub ResetDropDown()
Dim shpDropDown As Shape

For Each shpDropDown In ActiveSheet.Shapes
If shpDropDown.FormControlType = xlDropDown Then
shpDropDown.ControlFormat.ListIndex = 1
End If
Next shpDropDown
End Sub

--
Regards

Melanie Breden
- Microsoft MVP für Excel -

http://excel.codebooks.de (Das Excel-VBA Codebook)