setting "width" list box in data validation list

iwans

New Member
Joined
May 11, 2004
Messages
7
dear....can i set width in list box data validation (width in cell i set short).thanks

width.gif
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Re: setting "width" list box in data validation li

Did you try playing with the width of the cell D1 ?
 
Upvote 0
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
 
Upvote 0
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.

Code:
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top