List Width - Data Validation / Combobox

[Decumanus Maximus]

I've searched high and low (google isn't my friend this time) and I can't find an answer about these particular "issues".

First Issue: Data Validation - List Width

I understand that a data validation's list width is determined by the column's width. It was working before, however, after making some modification's to the spreadsheet, the drop-down list is dramatically wider than the column width now (for every drop-down list on the worksheet). Any ideas?

Second Issue: Excel Form -> Combobox - List Width

I'm using a fairly narrow (width-wise) Combobox that has a drop-down list with the numbers 1 through 10. I've set the MaxLength property to 2 and I'm aware of the ListWidth property, however, by setting the list width it adds a scrollbar to both the vertical and horizontal axes of the drop-down list. I just want a drop-down list that is the same width as the actual Combobox. It appears a Combobox has a set list width that can't be alter (although I'm sure there is a way). Any ideas?

Any help you can provide would be greatly appreciated.

 

[CaliKidd]

I realize this is an old post, but no one replied, so I will. Better late than never...

In regards to your 2nd question, I believe the short answer is no. I had the same issue with a narrow width combo box and I was unable to resolve the scrollbar issue. Seems to be a limitation/feature of the object's properties. This post explains it better:

http://www.mrexcel.com/forum/showthread.php?t=491845&highlight=listwidth

 

[Decumanus Maximus]

I appreciate the link and the response.

 

[CaliKidd]

You're welcome. Helping each other is what this forum is all about.

Just to be clear, from what I could tell, the default listwidth property is set to "0 pts", but that is deceptive because it appears to be really 72 pts. In other words, when you set the listwidth to 72 pts, the horizontal scrollbars will go away (assuming your list is narrower than that), but the combobox object automatically puts horizontal scrollbars for any listwidth between 1-71 pts. I'm not sure why they programmed the object that way. I see it as a flaw. Setting the maxlength property will not affect this in any way. It only limits the length of a user's data entry.

 

[CLockeWork]

Hi, I came across this thread in search of a solution to the same problem.

I just wanted to add to this, for future visitors, that I seem to have been able to fix the horizontal scroll issue for combo-boxes.

• I have a series of numbers
• the width of my box is 30.75
• I've set ColumnCount to 1, ColumnWidths to 10pt, ListWidths to 28pt
• I'm using 2010

I honestly have no idea why this works, but it does :D (for me at least)