Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Manipulate a Range | Excel Discussion (Misc queries) | |||
manipulate data | Excel Worksheet Functions | |||
Possible to manipulate Legend? | Charts and Charting in Excel | |||
Clearing Dropdowns In A Worksheet | Excel Programming | |||
Manipulate Excel | Excel Programming |