Manipulate all DropDowns on a Worksheet
 

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

Manipulate all DropDowns on a Worksheet
 

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" wrote in message
...
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

Manipulate all DropDowns on a Worksheet
 

Fantastic! Thanks. :)

Darren


"Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message
...
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" wrote in message
...
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