Expand column width automatically based on drop down selection

[Shadkng]

I'm having trouble finding code online that works to autofit a column width based on a selection in a drop down list. I would also like to have a minimum width that the column won't go below. I would need it for many columns in the same sheet so could it be a global type code? If not I can specify the column ranges and that would work. I am using combo drop downs as well if that matters. Thanks

 

[Shadkng]

Hi Akuini, it's working better but not working with the activeX combo list box. It works with the data validation drop down

Another issue just came to mind. If I choose a wide entry in row 24 and the column widens, and then I choose a narrow entry in row 25 the column will narrow. How can we keep the range to remain at the widest entry in any of the cells?

 

[Akuini]

The combobox worked for me, here's a sample workbook
I put data validation in A24:A35

https://www.dropbox.com/s/triwefosj...matically-based-drop-down-selection.xlsm?dl=0


I'm confused, you said before:

Also, once a column is made wider by an entry, I would like it to reduce if another small entry is chosen.

but now you say:

Another issue just came to mind. If I choose a wide entry in row 24 and the column widens, and then I choose a narrow entry in row 25 the column will narrow. How can we keep the range to remain at the widest entry in any of the cells?

But in the sample workbook above I changed the code to meet your last requirement.

 

[ZVI]

It seems that this:

In regards to question 1, I mean if I choose a wide entry from the drop down list the column will autofit as it should. Then if I then choose another selection from the same drop down and it is a narrow entry then the column stays wide from the previous selection, it does not size down. I think I mean by a change event as well.

contradicts to your previous:

...Would it be possible not to have the column size below the current width?

as current width is set in the previous step of column's width fitting.
But anyway just comment or delete this lines of the code:

      'If .ColumnWidth < OldWidth Then
      '  .ColumnWidth = OldWidth
      'End If

 

[Shadkng]

The combobox worked for me, here's a sample workbook
I put data validation in A24:A35

https://www.dropbox.com/s/triwefosj...matically-based-drop-down-selection.xlsm?dl=0


I'm confused, you said before:



but now you say:



But in the sample workbook above I changed the code to meet your last requirement.

Yes what I asked didn't make sense. The column needs to stay for the widest entry. But my combo box still doesn't work with the autofit and I don't know why...any ideas? Could it be a setting in the properties?

 

[Akuini]

Yes what I asked didn't make sense. The column needs to stay for the widest entry. But my combo box still doesn't work with the autofit and I don't know why...any ideas? Could it be a setting in the properties?

Hm, it could be. Just delete your combobox and create a new one without changing any property (except the name)

 

[Shadkng]

Hm, it could be. Just delete your combobox and create a new one without changing any property (except the name)

No luck. Just to be clear I am using an ActiveX combo box. I made a new one and named it the same as before to match the code "QuoteCombo". Your sheet works nicely - also your combo box is nicer than mine so after we solve this problem I have to ask you how to refine mine. Any other possibilities?

 

[Akuini]

No luck. Just to be clear I am using an ActiveX combo box. I made a new one and named it the same as before to match the code "QuoteCombo". Your sheet works nicely - also your combo box is nicer than mine so after we solve this problem I have to ask you how to refine mine. Any other possibilities?

I am using an ActiveX combo box too, so not sure what happened. Try copying my combobox to your workbook & adjust the name.

If that doesn't resolve the problem then maybe you can upload your workbook (without sensitive data) to dropbox or google drive.

 

[ZVI]

It's because fitting is applied only to the single changed cell, but should be applied to cells of rows 24:1000 in the changed column.
Here is the fixing:

Private Sub Worksheet_Change(ByVal Target As Range)
  Const RangeToFit = "A24:B1000,D24:E1000" '<-- change the range address to suit
  Dim OldWidth As Double, Col As Range
  If Intersect(Target, Range(RangeToFit)) Is Nothing Then Exit Sub
  For Each Col In Intersect(Target.EntireColumn, Range(RangeToFit)).Columns
    OldWidth = Col.ColumnWidth
    Col.AutoFit
    If Col.ColumnWidth < OldWidth Then Col.ColumnWidth = OldWidth
    If Col.ColumnWidth < 10 Then Col.ColumnWidth = 10
  Next
End Sub

 

[Shadkng]

I am using an ActiveX combo box too, so not sure what happened. Try copying my combobox to your workbook & adjust the name.

If that doesn't resolve the problem then maybe you can upload your workbook (without sensitive data) to dropbox or google drive.

I got it...I didn't realize in post#30 that you added some code at the top which I just saw. Sorry about that.

The combobox on your sheet works more smoothly than mine. A few questions:

1. When I click your box it opens with a blank space. Mine open with the first value at the top of the range.
2. Your box covers the data validation box completely and widens past the cell width. I have played with the widths, but only the width of the entire window changes.
3. Also, when I create the combobox where should I place it? In top left corner? So I should just drag it there?

Thanks again...I have to go to sleep so I'll be in touch tomorrow.

 

[Akuini]

1. When I click your box it opens with a blank space. Mine open with the first value at the top of the range.

Well, try something simple first, copy all sub in my sample workbook to yours (delete all duplicate sub}, see what happen.
Note: I have revised your original code in some parts.

2. Your box covers the data validation box completely and widens past the cell width. I have played with the widths, but only the width of the entire window changes.

I change the width using this line:
.Width = 150 'Target.Width + 5
you may change the size from 150 to any size you want.

3. Also, when I create the combobox where should I place it? In top left corner? So I should just drag it there?

The combobox will be hidden & moved from time to time, so in doesn't matter where you placed it in the first time.