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

G

green

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.
 
M

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)?
Click to expand...

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