Need to adjust the width of a Data Validation dropdown list

[harrysolomon]

I have a workbook which contains 6 worksheets and in each of those worksheets there are multiple columns which contain Data Validation Dropdown lists.

I want to be able to adjust the width of the dropdown lists when the users click on them so they can see the values to be selected and then when they select a value, I'd like the column to auto adjust it's width to the selected value.

I found some code online which does this great but I can only use the code for a single column, in
the following example it only works on the dropdown list in Column 7.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column = 7 Then
Target.Columns.ColumnWidth = 20
Else
Columns(7).AutoFit
End If
End Sub

How can I change this so it can be used on other columns? For example, I want it to work on the lists where Target.Column = 3,
or Target.Column = 7 or Target.Column = 8 or Target.Column = 9.

I'm also going to want to use this across all the worksheets in the workbook but I'm guessing that would just be a matter of assigning this code to each worksheet?

Thanks.

 

[onlyadrafter]

Hello,

try

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
        Select Case Target.Column
            Case 3, 7, 8, 9
                Target.Columns.ColumnWidth = 20
            Case Else
                Columns(Target.Column).AutoFit
        End Select
End Sub

 

[harrysolomon]

Hello,

try

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
        Select Case Target.Column
            Case 3, 7, 8, 9
                Target.Columns.ColumnWidth = 20
            Case Else
                Columns(Target.Column).AutoFit
        End Select
End Sub

Thanks but this isn't working correctly on my worksheet.
When I click on one of the specified columns, the width is not auto-adjusting to 20 for some reason.

 

[onlyadrafter]

Hello,

Sorry, forgot to mention that the code needs to go into the sheet code window, not a standard module.

You will need to copy it into every sheet code window.

 

[Jonmo1]

The original code was

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

So drafter's solution should be adjusted accordngly.

 

[onlyadrafter]

Oops!

Good spot Jonmo, wonder how many times I read the original post?

 

[harrysolomon]

That fixes it, thanks. I totally missed that change to the code.