Manipulate all DropDowns on a Worksheet

[Darren Hill]

I want to create a macro to select each DropDown (Forms Toolbar) on the
current worksheet, and resize it to fit the cell it is positioned over.

Is this possible?

If it helps, the dropdowns are labelled with the cell as the last part of
the name - for example, "Drop Down A5", so I can easily get that cell by
removing the first 10 characters.

Darren

 

[Vasant Nanavati]

Hi Darren:

Try:

Sub Test()
Dim drp As DropDown, iLen As Long
For Each drp In ActiveSheet.DropDowns
With drp
iLen = Len(drp.Name) - InStr(1, drp.Name, "n ") - 1
.Left = Range(Right(drp.Name, iLen)).Left
.Top = Range(Right(drp.Name, iLen)).Top
.Height = Range(Right(drp.Name, iLen)).Height
.Width = Range(Right(drp.Name, iLen)).Width
End With
Next
End Sub

Regards,

Vasant.

 

[Darren Hill]

Fantastic! Thanks.

Darren

Hi Darren:

Try:

Sub Test()
Dim drp As DropDown, iLen As Long
For Each drp In ActiveSheet.DropDowns
With drp
iLen = Len(drp.Name) - InStr(1, drp.Name, "n ") - 1
.Left = Range(Right(drp.Name, iLen)).Left
.Top = Range(Right(drp.Name, iLen)).Top
.Height = Range(Right(drp.Name, iLen)).Height
.Width = Range(Right(drp.Name, iLen)).Width
End With
Next
End Sub

Regards,

Vasant.