Let dropdown list width expand beyond width of column so people can read answer choices?

I have a spreadsheet with a lot of data validation lists. Some of the response options are quite long and the text gets cut off because the column widths are narrow, and we want folks to be able to read the entire answer choice.

We don't want to permanently expand the column widths because there are many columns and that would make it hard to navigate and read. I wrote some code to temporarily expand the whole column when a cell in that column is selected. That solved the width problem but the downside is that now it's impossible to "Undo", which is a pretty important function.

The weird thing is in some instances, the drop down does expand beyond the column width when selected and sometimes it doesn't. I'm not sure why. Is there a way to allow just the dropdown to automatically expand according to response option text lengths, consistently?

Thank you in advance!

Another link at that site has some other methods. http://www.contextures.com/xlDataVal08.html

Thanks for the code, and the link! I tried using the code above, just swapping out the sheet names, but I got the error message "compile error: method or data member not found." I'm super new at this, and am not savvy enough to understand all of the statements to know if I need to swap out any other parameters in the code provided above. Any thoughts on this?

I looked at the link, which is where I got the original code base for making the whole column temporarily wider, but this has the issue of resetting the page and making it impossible to ever hit "undo".

Thanks!

Why would you need undo? IF the user made the wrong selection, they would just need to redo their selection.

Hi Kenneth, because the sheet contains lots of different types of data fields. The column width adjusting code makes it so that you can't undo ANYTHING in the sheet. If you accidentally delete a bunch of text or clear contents of a bunch of cells, you don't have the option to undo. =/

Thanks, xl! I was trying to play around with what you mentioned above but haven't figured it out yet - I'm still working on it. We were using list data validation drop downs rather than developer controls so I hadn't used combo boxes before.

There are a LOT of drop downs in the spreadsheet. Folks using the Excel file could potentially be doing so on Macs/PCs and various versions of Excel and crappy old computers, so we were hoping to have a file that would be as compatible and unlikely to crash/freeze as possible. (Not sure if that would factor into whether it would be OK to have lots of controls or not?)

In this example, one must reselect or change the selection if the cell is selected if you want to resize the column back to a prescribed default value. I have also attached the workbook as an example to play with.

In your file, change the Range() in Intersect() to suit.

Please Login or Register  to view this content.

WHOA. Your solution is perfect! Thanks so much, x! I love that it works for any cell with data validation drop downs in it.

I tried copy/pasting your code from the Working sheet to the Not Working sheet to see if it'd work but I'm getting the same "compile error: method or data member not found" message again when I click in any cell. The main difference I see in the code is that the last chunk of code in your sheet said "TempCombo" and "KeyUp" up top whereas in the copied code the last chunk says "(General)" and "TempCombo_KeyUp" but I'm not sure how to fix it since the drop downs don't allow those options from your code. I know earlier you talked about manually inserting a combo box and I tried learning about those (assuming that you're referring to: Developer>Insert>Form Controls>Combobox?), but if that's what you were talking about I don't see where you inserted it? Could you please walk me through what other steps you took other than putting in the code?

Spreadsheet with my copypasta of your code into "Not working" and general messing around/testing is attached.

Sorry for being such a n00b! o_O

Ohhh I was totally looking at the wrong thing. That makes a lot more sense now. Thanks X! I got it to work with everything except our dependent drop downs (where each subsequent column =INDIRECT of the previous column - those are kinda tricky so I'm not terribly surprised). This is super useful!

Originally Posted by xladept

Hi X,

I made the width 10 times the Cell width, see the red.

This is in your "Working" sheet module:

Please Login or Register  to view this content.

Dear Xdrenched,

Appreciating how old this post is, I hope it will reach you or other experts on this platform.

I have benefited greatly from the solution you shared in this post. I tried it and it worked. However, I do have a follow up question and hope you might be able to shed some light on it as I am noob that do not fully understand what each set of these codes are supposed to do.

What is working for me: being able to see the full texts in each option when I clicked on the drop-down list. This is working perfectly.

What I am trying to fix: after I selected a response, the full text of my selection is shown in a separate text box besides the selected cell and is blocking all other cells. Is it possible to change the code so that once I selected a response, that is the end of it, I did not need to see the full text of what I selected in a separate text box.

I hope that makes sense, if not, I am glad to attach picture of files as needed.

Thank you so much for your help in advance!