Make Dropdown List Wider Without Making Cell Wider

I've downloaded the test(2) file from this thread:

making dropdown list wider without making cell wider ???

Sorry I'm unable to link as this is my first post.

It was the perfect solution to what I need. All I had to do was modify the code to my desired columns and the widths of the columns. I also changed the name of module to ExpandedCell But for some reason it doesn't work. I've struggled for hours and have given up.

Attached is the exact routine which I modified and my file.

Please Login or Register  to view this content.

Customer Documents & Databases.xlsm

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however you need to include code tags around your code.

Please take a moment to add the tags. Posting code between [code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/code] tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(Note: since you're new here, THIS time, I've done it for you.)

You're welcome. I can't help you as I'm not even a VBA newbie... not even a VBA numpty.

I also changed the name of module to ExpandedCell But for some reason it doesn't work.

I assume the original name was Worksheet_Change or Worksheet_SelectionChange and that it was in the worksheet class module?

And that is the reason it doesn't work. Those are "event handlers" and they are special code modules. You can't just rename them, nice idea that might seem.

Make it what it was and put it in the sheet module (where the drop down is) and it should work.

Thanks, TMS, but no dice. I didn't change the original name, Worksheet_SelectionChange, until after I loaded it the first time this morning. And it didn't work then. But thanks for the tip that there's more in a name here than just a name.

Now I can't see anything amiss with the code that I edited. So if the code is not the problem then perhaps I'm not loading it properly. So I should relate the exact steps I took:

1. The first thing I did was save the xlsx file to xlsm.
2. Secondly, I opened up the VBA window.
3. Next I right clicked on sheet2 (the sheet with my drop downs) and selceted Insert->Class Module (as opposed to Module).
4. That brought up a blank right pane in which I pasted the code.
5. Finally, I went back to Sheet2 of my workbook and tested. The result was tearing in my eyes and my having to reach for some Kleenex. (Excuse my sense of humor. Here and throughout. I need to laugh in order to alleviate my frustrations.)

The first time I attempted loading the edited code I right clicked on the proper sheet but selected Module, not Class Module (as I don't know the difference). I tried Class module this time as you questioned whether it was or not. Perhaps it was, I thought.

My thinking is that I'm not loading it properly. If I open up the test (2) worksheet which I downloaded the VBA works just as advertised. Superb! Wunderbar! My desire fulfilled! When I open up the VBA editor and look in the Project - VBAProject pane I do not see any module at all. So I tried to duplicate the test (2) file and reopened my file, opened the VBA editor and went to View->Code. That brought up a blank right pane in which I insert my code. I saved the file, closed and reopened it, opened the VBA editor and there was the code without a module listed. But failure was again the result.

I should mention, too, that I opened up the Macro Security dialog and changed from Disable VBA macros with notification to Enable VBA macros (not recommended etc.). Since that had no effect I've changed it back to Disable macros with notification.

The ball is out of my court again and I leave it to someone to find my <user error>.

Please Login or Register  to view this content.

Right click on tab (Sheet2) >>>"View Code" >>> copy/paste to blank form

When I open up the VBA editor and look in the Project - VBAProject pane I do not see any module at all.

That's because the code doesn't go in a standard module, it goes in the Sheet module for the sheet where the drop down cell exists. Unless changed from the default, they are typically named Sheet1, Sheet2, etc.

And thanks for the rep.

Thanks, John.

Your file works but not in the same way as the test (2) file. I noticed that the code is identical to my test (2) edited code other than you changed the original name Worksheet_SelectionChange to Worksheet_Change. It's beyond my paygrade to know what difference changing the name would make.

Here's where your worksheet works differently than the test (2) worksheet.


Original & desired column width settings (not sure about your file - set at 40 but final size needs to be 7 in my case):
test (2) file = 1.43
my file (with your code loaded as you've instructed) = 7
Increase_width file = 8.43

Results:
test (2) file: Clicking the drop down cell once automatically expands the drop down cell to 20 and opens up the list. The column width remains at 20 as long as other drop down lists are selected in the same column. Clicking on any cell outside that column resizes the width back to the original . . . 1.43.
my file (with your code loaded as you've instructed): No change by clicking the drop down cell to open the list. Column width increases to 40 only after making a selection from within the list. So for the first drop down it remains too narrow to read the full text in the list. Column remains at 40 and does not resize back to the required width of 7.
Increase_width file: Clicking once on any cell in the column has no effect to the column width. To get the column to expand to 40 requires double-clicking a cell in the column and then clicking on any other cell . . . inside our outside the column. The column width does not resize to original size of 8.43 or 7 once it's been expanded. Despite the code have a value of 7.

Color me confused. It's the same code run in three different workbooks now, granted with a name change, and each sheet produces a different result. My numpty head (hat tip to Glenn Kennedy for that apropos term) tells me it's a loading issue.

Ball is now in someone else's court.

Edit: I'm located in Bangkok, Thailand and I am ahead of U.S. Eastern Standard time by 12 hours. So I'm off to bed and will check back my morning. But not without first expressing appreciation for everyone's help thus far.

Ok. Event handlers. Some fire when events happen to the workbook, for example, Open and Before Close. Others fire when events happen relating to a worksheet. They have specific names reflecting the event they monitor. In this case, we are interested in two specific event handlers. Worksheet_Change fires when a cell is changed … so, it will fire AFTER the value in a cell is changed, probably not what you want. Worksheet_SelectionChange fires when a cell is selected … that is, before the cell's value is changed. This is the one you need.

I can't look at the workbook or code at the moment as I am looking at this on an iPad. I can only assume you are understanding what you are being told and implementing it.

As per TMS reply:

Please Login or Register  to view this content.

@TT: Thanks for the rep.



If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

John,

I've inserted the above code with the edited name, Worksheet_SelectionChange. and loaded it as you've instructed: right click on the sheet with the drop downs and select View Code. And here's what happened . . . nothing. And then it worked beautifully!!

So what did I change to go from no change in column width to all of a sudden having the column width change as intended (and reverting back to it's original width after clicking outside the column)?

Something must be different from my worksheet and the test (2) worksheet. And here's what it is. The columns 8 and 25 in my worksheet are merged across with other cells; 8 thru and including 12 and 25 thru and including 29. Merged cells disallow the VBA from working. However, clicking on an unmerged cell within column 8 or 25 instantly expands the width to the desired 40. Makes perfect sense to me.

Returning the column back to it's final size only requires clicking on any cell outside of the column. Edit: But it must be an unmerged cell. Clicking on a merged cell outside of the column does nothing.

And so the VBA works, albeit with a slight and not so inconvenient workaround. My <user error> was thus twofold: loading the code as a module and failing to recognize that the code did not work on a cell within the column when it is merged with other cells.

My only question now is what needs to be added to the code to account for merged cells?

Also, I vaguely recall seeing at one time an expanded drop down list without the column needing to be expanded. Thinking logically, the fact that the width of the drop down is equal to the width of the column (also applied to merged cells across any number of other columns) obviously means that it must be so coded in Excel as default behavior. Code is whatever the code is and any code can be changed. Since the drop down is defined as an object would it be possible to change the properties of that object directly in VBA? That, to me, would seem to be the ideal solution. As the old truism goes, there's always more than one way to skin a cat. Any thoughts or reasons why that would not be possible? And as another truism states, where there's a will there's a way.

I'll leave the questions open but would like to thus far thank everyone for helping me to at least have an important workable solution for my application. You are all a bunch of unsung heroes in a world that needs more heroes. And I'll pay tribute to y'all with a song. "Heroes" by another hero, the late great David Bowie, performed live in Berlin. Sorry I'm not yet allowed to provide links but it's easily found on YouTube.

Edit: I've attached the updated file with the VBA in case it's helpful. The only change to the VBA that I've made from the above is to change the value of 40 to 22. Since the drop down is in a merged cell the 40 is added to the width of the other cells. I only need 22 for the expanded width.

I thought I'd keep the thread open to solve for the VBA accounting for merged cells. Or should I open another thread for that?

Yes, someone let me know earlier about thanking those who have helped via the star icon. Thanks.