How do I get a reference to a control on a sheet?

[Guest]

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)

Thanks.

 

[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)