|I found this at:|
You will need to modify it to use cell references that match your worksheet.
[A1:A5] refers to the cells with the Drop Downs
[E:E] refers to the column that contains the Source for the Drop Downs. The code assumes that the Source column is as wide as the data in those cells.
Right-click the sheet tab for the sheet you want this to happen in and chose View code.
Paste the following code into the pane that opens, then modify the cell references.
Then go back into the sheet, select a drop down and see what happens.
Note: The workbook will need to be saved as an xlsm or xlsb file and macros will need to be enabled on the user's machine in order for this work.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim myShp As Shape, Drp As Single
On Error Resume Next
'cells holding drop downs
If Intersect(Target, [A1:A5]) Is Nothing Then Exit Sub
If Target.Validation.Type = xlValidateList Then
Set myShp = ActiveSheet.Shapes("Drop Down 1")
Drp = myShp.Width - Target.Width
'Column holding list, sized appropriately
myShp.Width = [E:E].Width
myShp.Left = Target.Left - myShp.Width / 2 + Drp * 2
Set myShp = Nothing
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.