Manipulate all DropDowns on a Worksheet

  • Thread starter Thread starter Darren Hill
  • Start date Start date
D

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
 
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.
 
Fantastic! Thanks. :)

Darren


Vasant Nanavati said:
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.
Click to expand...
 
Back
Top