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

 

[Haluk]

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

Did you try playing with the width of the cell D1 ?

 

[iwans]

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

no...i dont want to set width in cell D1. Just i want to set width in dropdown in list box.thanks

 

[Haluk]

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

then, no hope...

 

[parry]

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

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

 

[iwans]

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

it doesn't work. please help me.

 

[parry]

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

To kick it off you may have to resize the width of your list column (column A in your example).

regards
Parry

 

[iwans]

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

dear ... i still can't setting width listbox without resize cell. anybody one who can help me, please. thank you.

 

[NateO]

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

Hello, this is the only way I know of making this happen.

Which module did you place this code in? How did you pass the targeted width and to what?

 

[sharpis]

Simply create the list in a merged cell.
Excel 2007 Ribbon_Home/Alignment/Merge Cells and merge over 2 or more cells for the list width required and then create your data validation list box.
In my case this is all that was required instead of using VB.