Validation List: Wide as Widest List Item

  • Hi all,


    I am posting this is in a new thread because it is not really relevant to my old thread.


    I am trying to make some of my data validation dropdown list boxes narrower. I was told one way to do that is to use combo boxes. (If there are any other ways, please let me know!)


    I don't think my combo boxes are working. I had tried to make a combo box appear in each data validation pulldown cell by using this tutorial:


    http://www.contextures.com/xlDataVal10.html


    I followed this tutorial word for word, but when I try to test the code at the end by double-clicking on a data validation pulldown cell, no combo box appears. Not sure how to remedy this! Any help greatly appreciated.


    Thanks!

  • Re: Combo Boxes Not Appearing


    I just made a quick sample of what I am working with.


    In this sample there is a text-fill-in column next to a column that uses data validation dropdown menus. The data validation lists are on a protected Sheet2.


    Now what I did was I opened the Control Toolbox, clicked the Combo Box button, went into Design Mode, and the only thing I changed from the default properties on the combo box was changing AutoSize to "True".


    Then I exited Design Mode and cut-and-paste the code from


    http://www.contextures.com/xlDataVal10.html


    into the sheet (via right clicking on the sheet and going to View Code)


    Saved thatm went back to the workbook. When you double-click on one of the data validation dropdown menus now, a combo box appears like it's supposed to, but it is not populated with the data validation fields. You can still see the data validation dropdown under the combo box.


    You will see that the second column, "Choose An Item", is rather narrow but the dropdown menu box is wide. All I want to do is make it so that the width of the drodown menu box is only as wide as its longest text item.


    I'm attaching the file now - please let me know what you think!


    Thanks!

  • Re: Combo Boxes Not Appearing


    I've looked at the link and I'm a little confused by it.


    Nowhere does it tell you how to populate the combobox and in the code itself it sets ListFillRange to nothing, effectively clearing the combobox.


    That just doesn't make sense to me.

  • Re: Combo Boxes Not Appearing


    Is there a tutorial somewhere that shows you how to do that?? I am (obviously) confused as well.


    Alternately, is there just a way to make the data validation dropdown menus narrower without using combo boxes?

  • Re: Combo Boxes Not Appearing


    Debra's example assumes a range. The example file data validation is =$L$2:$L$13


    Yours uses a named range =Items


    Modify your code
    [vba] .ListFillRange = ActiveWorkbook.Names(str).RefersToRange.Parent.Name & "!" & _
    ActiveWorkbook.Names(str).RefersToRange.Address[/vba]

  • Re: Combo Boxes Not Appearing


    Good call!


    However, I can't name a range, because then I get the "You may not use references to other worksheets or workbooks for Data Validation criteria" message. My main sheet is Sheet1 and my lists are on Sheet2.

  • Re: Combo Boxes Not Appearing


    Yes, and the code I posted gets you around the problem.
    It unravels the named range address for use in the combobox. The data validation contiunes to use the =Items reference.

  • Re: Combo Boxes Not Appearing


    Tried it... the combo boxes still aren't populating. For reference this is what my code looks like now:


  • Re: Combo Boxes Not Appearing


    Quote from zura04

    Is there a tutorial somewhere that shows you how to do that?? I am (obviously) confused as well.


    Alternately, is there just a way to make the data validation dropdown menus narrower without using combo boxes?


    Try
    Matching ComboBox Controls
    Dependent Validation Lists

  • Re: Combo Boxes Not Appearing


    All I want to do is make the pulldown menu boxes (created via data validation list) narrower - make them only as wide as the longest text entry in the list. Right now they are quite wide even if the column they are in is narrow. It is just an aesthetic formatting thing. Someone had suggested earlier that maybe this could be done with combo boxes but if there is a way to do it without combo boxes, even better.

  • Re: Combo Boxes Not Appearing


    Here something weird!


    Using the example workbook 59296. Change the width of column C to 8.
    The droplist is still too wide. Select a non validated cell and save the workbook. Now try the dropdown.
    For me it is as wide as described before, column width + dropdown button.

  • Re: Combo Boxes Not Appearing


    Strange indeed...but it works :) I have always thought the list is a wide as the column housing the referenced list. Wrong!


    zura04, why were you using the page you linked to, As far as I can tell it's not a work-around your problem. See AutoComplete With Validation Lists for a better IMO.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!