Make Drop Down Temporarily Wider For Multiple Columns

[AlexBB]

Hi everyone,

I'm kind of new and still learning how to properly use VBA and code in Excel. Currently, I'm struggling with adjusting the following 'standard code' from contextures to apply to multiple columns (J to AJ, or number 10 to 36). I've tried multiple variants, but struggle with making the columns go back to their initial width.

The standard code:

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

A code I've tried:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Select Case Target.Column
Case 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36

If Target.Count < 9 Then Exit Sub
If Target.Column > 9 Then
Target.Columns.ColumnWidth = 25
Else:
Worksheets("KARTLEGGING").Columns("J:AJ").ColumnWidth = 5
End If

End Select
End Sub

Would anyone be able to help me?

 

[My Aswer Is This]

Not at all - you wrote before, that merged cells are bothersome. So I made adjustments to the sheet, deleted the merged cells, and moved everything up, so that the drop-down list would be in the first row. And now it works when I click on first cell of the column, as before, I had to click on an empty, unmerged cell. Also, I wasn't familiar with case statements, so it was a great insight.

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.