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, thank you! so far it seems to working as it should. I will test further and let you know if all is good.

 

[Shadkng]

Hi Zvi, so far the code is working well, but I have a different problem that may be related to the code you wrote.

I created a dependent drop down list on existing data validation lists. In my column "B" I have a data validation range named "fabric" (I put =INDIRECT) in the source box. In column "D" I have another drop down the show the specific colors based on the fabric chosen in "B" (I put =INDIRECT(SUBSTITUTE(B24," ","_")) in the source box. I followed the online "Contextures" guide on how to name the ranges, etc. The dependent drop down is working fine using standard data validation lists. When I double click to use the Combo list it works in column "B" (fabric), but it does not work in column "D" to choose color. When I click the combo box it shows up blank. However, I am using your modified Combobox code you gave me in post #50 - so yours is different than the online code. The online tutorial mentions some additional code to use for combo lists but I don't exactly understand it. I am hoping you can solve. Do you need more information? Thanks

 

[ZVI]

There is no any difference in result of calculating s in my code vs str in your code of the post 21. So, the issue is not in s at all.
The suggestion:
instead of: .ListFillRange = s
try using: .ListFillRange = Evaluate(s).Address(External:=True)

 

[Shadkng]

That didn't work. The combobox went away totally in column D. Combo is OK in column B.

For the combo boxes I was originally using a standard code form online tutorials but it got changed a few times while we were working on the column widths.

Maybe I should remove the column width code and go back to the code that works and then re-try to merge the column width code?

 

[Shadkng]

Just wanted to let you guys know the above problem with the 2nd combo box not showing the list is solved. I gave the formula from the data validation box a name in name manager and then used the name in data validation ( I ended up using an index/offset/match formula I found online for the dependent lists). So everything so far is working well with the column widths and combo code you revised. Thanks again for everyone's help it is really appreciated.

 

[Shadkng]

Hi again, wondering if you can help me a bit more. It seems I am losing the combo box again when using the INDIRECT function in data validation. I need to use it for the dependent drop down boxes. Originally I started out with the code I found online and you modified it as in post #50 . I went back to the tutorial on the original code and they have instructions to insert the following code in order to work with indirect. But since you modified the code, this will not work because the variables are different and maybe other reasons.

I don't know what this extra code does, but I was hoping you will be able to figure it out and maybe modify it to work in the code for post #50 . If you need the original code that I found online I can send it. Thanks

Here is added code:

'for simple INDIRECT function (English)
' e.g. =INDIRECT(B2)
'will create dependent list of items
    If Left(str, 4) = "INDI" Then
      lSplit = InStr(1, str, "(")
      str = Right(str, Len(str) - lSplit)
      str = Left(str, Len(str) - 1)
      str = Range(str).Value
    End If

The instruction say to insert the code below this line from code in post #50

 s = Mid(s, 2)