setting "width" list box in data validation list

[iwans]

dear....can i set width in list box data validation (width in cell i set short).thanks

 

[leishtheman15]

Re: setting "width" list box in data validation li

Hi - I found this code below and it works well - the only snag is that whenever I save the file it stops working (I'm using it embedded within an event) - the save method seems to cause the code to permanently lose the shape instance (although I can get it back by closing out of the file and then re-opening).

Any ideas as to how I might overcome this problem???

Thanks,
Andy

Hi, the following code courtesy of a previous post by NateO should do it. Substitute A1:A5 for the cells that have drop down data validation boxes & C:C for the cells that have your list. Right click the sheet, select view code and paste the code in the right hand window.

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 = [C:C].Width 
    myShp.Left = Target.Left - myShp.Width / 2 + Drp * 2 
End If 

Set myShp = Nothing 

End Sub