dropdown list width in Excel

Question

Hello,

The question has already been asked on the forum, but I'm reposting it with some details about my problem:
- I have a narrow-width column in which users will select a one-character code via a dropdown list; so far, so good.
- At the top of this column, I want users to be able to choose a title via another dropdown list. This title has a width much greater than one character, so the display is set up vertically.
> Problem: When the user wants to choose their title from the list, it is as wide as the column, and this reduced width does not allow them to view the offered titles. At one point, a colleague found a display parameter to have a larger width, but we lost that option and it’s impossible to find where it would be.
Note: In fact, I have many adjacent columns with the same usage, so increasing the column width is the last solution I am considering.

Thank you in advance for your responses.

Configuration: Windows XP Internet Explorer 6.0

Mike-31 · Accepted Answer

Hi,

It's a pity to bring up this old discussion, a relevant question that has been raised numerous times.
There is a solution to create validation lists with a width greater than the column width.

You must create the widest validation list first.

1) When the validation list is created, it takes the width of the cell; at this point, you need to increase the column width, which will determine the width of the drop-down list.

IT IS IMPORTANT TO SAVE THE FILE AT THIS STAGE.

2) If you increase the column width, the validation list follows.

3) However, if you reduce the column width, the width of the validation list will be limited to the width at creation.

This technique, discovered by chance in 2008, works on versions 2000, 2003, and under 2007 and 2010 by saving the file under the old .xls extension (Excel Workbook 97-2003(*.xls)).

If you want an example, let me know.
A+
Mike-31

A period of failure is a perfect time to sow the seeds of knowledge.

eriiic · Answer

Hello,  a colleague found a display parameter to have a larger width I've never seen that... For me, the width is that of the cell.  But you can use another type of dropdown list.  - 'View / Toolbar' check 'Control Toolbox'  - select 'dropdown list' and draw it on the sheet  - once selected, right-click on it and choose 'properties'  - LinkedCell: reference of the cell receiving the selection, for example B2  - ListFillRange: reference of the range with the list, for example G2:G9  - disable design mode by clicking on the 'square-rule-pencil' icon. Reactivate it if you want to change the location or size of the list.  eric

Bolos · Answer

Hello,

I had the same question and this answer is a first element.

However, how do I set up the ActiveX control so that it only takes its full width when selected? The rest of the time I would like it to remain the size of the column.

Hoping to be clear.
Thank you.

Bolos · Answer

Hello,

I had the same question and this answer is a first element.

However, how can I set up the ActiveX control so that it only takes its full width when selected? The rest of the time, I would like it to stay the size of the column.

Hoping to be clear
Thank you

eriiic · Answer

Re,  You could do it like this: Private Sub ComboBox1_DropButtonClick() ComboBox1.Width = 120 End Sub Private Sub ComboBox1_LostFocus() ComboBox1.Width = 60 End Sub   eric

Bolos · Answer

Re,

Thank you for your response.

Since I have 100 lists to make, I was hoping to find something cleaner; I am currently working on the "HideSelection" property but I don't see any difference whether I choose the value "true" or "false".

Any idea?

Thanks in advance.

eriiic · Answer

Hi,

I think I've found your Vaucluse trick.
You just need to widen the column receiving the drop-down lists before applying the validation, and Excel retains that previous width as the minimum width...
Eric

Bolos · Answer

Hi,thank you for your response. The technique does work indeed.However, if in the same column, below the cells affected by the dropdown menu I copy/paste from another sheet, the problem comes back. At that point, even if I exit without saving, the problem remains upon reopening the file...Best regards

eriiic · Answer

Good evening,  A macro that expands the columns if a validation by list is selected. Restores the original width afterwards. Validation list width.xls eric

Kostrovit · Answer

Hello,
I am reviving this old post first of all to thank eriiic. I used his solution to widen a column when selecting from a drop-down list. I want to make it clear right away that I am an amateur. My basic skills leave me astounded by modern codes, but from an example I manage to interpret and synthesize different solutions.

This is how I created the project "Internet Slots.xls" for a media library, reusing Eric's solution combined with other features.

A curiosity that I am also including in the folder (see link below). While working on "Internet Slots," I reached a point where, miraculously, I achieved what I originally wanted. To have a column width of 10 and when I click on the list, it opens with a width of 30 without affecting the column. So it is possible. The problem is that my other functionality no longer worked: only entering the first occurrence of the item in the cell (the list grouping 3 columns from another sheet). In this case, the entire row ended up in the cell.

http://www.box.net/shared/62m6ksat0x

By analyzing the bugs step by step, I saw that there were problems with If End If loops. When I corrected them, I obtained what eriiic proposed, the temporary widening of the column. A miracle never lasts long. But if it's of interest for a professional to look into to understand what happened, I would be interested, as modifying only the width of the list is a more elegant solution.

In the meantime, thank you again for enlightening my path

Best regards

Kostrovit

Kostrovit · Answer

Thank you Mike-31,

And sorry for this post revival. The procedure you explained works well, but is unstable in my program. The list constantly takes the width of the column without my users being able to say exactly what they did to make that happen. I spend my time restoring the situation with this procedure, it's a bit tedious.

I promise if I can't find a solution, I'll open a new post to call for help.
Thank you
Best regards
Kostrovit

Dropdown list width in Excel

11 answers

Unrefreshed usb drive on android interface

Erreur de captcha

How to delete quarantine files in defender?

Issue with accents

Https://www.youtube.com/@topblazo

Unable to type the at-sign

Reinstallation of w11 impossible after pc crash...

Download impossible for visual petanque

Connect external dvd player to samsung tablet

Phone not receiving certain sms