Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: setting "width" list box in data validation list

  1. #1
    New Member
    Join Date
    May 2004
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default setting "width" list box in data validation list

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


  2. #2
    Rules Violation
    Join Date
    Oct 2002
    Location
    Turkey
    Posts
    1,075
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

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

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

  3. #3
    New Member
    Join Date
    May 2004
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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

  4. #4
    Rules Violation
    Join Date
    Oct 2002
    Location
    Turkey
    Posts
    1,075
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

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

    then, no hope...

  5. #5
    MrExcel MVP parry's Avatar
    Join Date
    Aug 2002
    Location
    Wellington, New Zealand
    Posts
    3,355
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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

  6. #6
    New Member
    Join Date
    May 2004
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    it doesn't work. please help me.

  7. #7
    MrExcel MVP parry's Avatar
    Join Date
    Aug 2002
    Location
    Wellington, New Zealand
    Posts
    3,355
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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

  8. #8
    New Member
    Join Date
    May 2004
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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.

  9. #9
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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?

  10. #10
    New Member
    Join Date
    Mar 2009
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •