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 Zvi, it seems to work OK but can the macro work as a selection change so it work instantly when the selection is made in the drop down box? Otherwise I have to run the macro each time.

Would it be possible not to have the column size below the current width? Or I can we specify a minimum width for each column range? If I could specify each range on a separate line with it's own minimum it could easy for me. I sorry I didn't explain clearly before.

Thank you

 

[Akuini]

Did you try my code in post #8 ?

 

[ZVI]

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

Yes, sure. Try this in the sheet's module:

Private Sub Worksheet_Change(ByVal Target As Range)
' ZVI:2019-02-07 https://www.mrexcel.com/forum/excel-questions/1086525-expand-column-width-automatically-based-drop-down-selection.html
 
  '--> Settings, change to suit
  Const FitColumns = "A:Z"  ' Columns to fit
  Const FirstDataRow = 24   ' Fit data are in that row and the below rows
  '<--End of the settings
 
  Dim a() As Variant, Col As Range, OldWidth
 
  Application.EnableEvents = False
  Application.ScreenUpdating = False
 
  With Intersect(Target.EntireColumn, Me.Range(FitColumns))
    For Each Col In .Columns
      With Col.Resize(FirstDataRow - Col.Cells(1).Row)
        a() = .Value
        .Value = Empty
        OldWidth = .ColumnWidth
        .EntireColumn.AutoFit
        If .ColumnWidth < OldWidth Then
          .ColumnWidth = OldWidth
        End If
        .Value = a()
      End With
    Next
  End With
 
  Application.EnableEvents = True
  Application.ScreenUpdating = True
 
End Sub

 

[ZVI]

Did you try my code in post #8 ?

That is good solution!

 

[Shadkng]

Hi Akuini, I'm sorry I missed it. I just tried it and it seemed to work once but I can't get it to work again. Below is the code which I just modified the range. When it did work I noticed it only worked on the first wide entry, so if I selected another entry it didn't expand. Also, after it expands to a wide entry and then I select a narrow one, can it resize smaller?

Also, I have another private script in that sheet named "worksheet_change". Can I rename it? I assume I can change the first part of the name and need to leave "change"? I deleted the other script for now so there would be no conflict.

Thanks

Private Sub Worksheet_Change(ByVal Target As Range)
        'change the range address to suit
    If Not Intersect(Target, Range("C24:D1000")) Is Nothing Then
        Dim x As Double
        If Target.Cells.Count = 1 Then
            x = Target.Columns.ColumnWidth
            Target.Columns.AutoFit
            If Target.Columns.ColumnWidth < x Then Target.Columns.ColumnWidth = x
            
            'minimum width, change to suit
            If Target.Columns.ColumnWidth < 15 Then Target.Columns.ColumnWidth = 15
       
        End If
    End If

End Sub

 

[Shadkng]

Hi, I just realized that both codes #8 and #13 work with my regular data validation drop down lists, however I am using combo drop downs as well and it doesn't work when I use the combo. Any solution for that?

 

[ZVI]

Depending on type of dropdown (Form's dropdown or ActiveX combobox) use one of the below code in the sheet's module

Sub DropDown_Change()
  ' Assign macro of any not ActiveX dropdowns to this Sub
  Worksheet_Change Me.DropDowns(Application.Caller).TopLeftCell.EntireColumn
End Sub
 
Sub ComboBox1_Change()
  ' Code for ComboBox1 ActiveX
  Worksheet_Change Me.ComboBox1.TopLeftCell.EntireColumn
End Sub

 

[Shadkng]

I am using the activeX combo so I used the second code. But it's wroking erratically and I'm getting a "run time error 91, Object variable or with block variable not set" when I sue the combo droop down. The error does not happen if I use just the data validation drop down arrow. Also, if I type something is a column that is not a drop down I get the same error.

Also, you're saying to put the activeX code into the sheet module as a separate sub? Does it have to be private sub? Thanks

 

[ZVI]

For the Combobox1 ActiveX-control, embedded into the sheet, use Private Sub ComboBox1_Change in that sheet code module.
And yes - it's a separate sub.
And it can be used without Private statement as well.
In design mode you may just double click on the embedded ComboBox1 to create template of its code in the sheet's module.
Could you post full code of the sheet's module to analize it?
What is the top left cell for embedded ComboBox1 control?

 

[My Aswer Is This]

I do not understand this:

And it can be used without ​

Private​

statement as well.
You may just double click on the embedded ComboBox1 to create template of its code in the sheet's module.

Are you saying by just double clicking on a ActiveX combobox
This will result in creating a Module Script? Which is not a Private script.

I tried this and it does not do that.

The only way I know how to run a Module script in a Activex control is to call the Module script from the Activex Control.

Or maybe I can learn something new here.
​