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]

Try this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
'Modified  6/21/2022  10:28:01 AM  EDT

Select Case Target.Column
    Case 10 To 36
        Target.Columns.ColumnWidth = 30
    Case Else
        Columns.ColumnWidth = 5
End Select
End Sub

 

[AlexBB]

Try this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
'Modified  6/21/2022  10:28:01 AM  EDT

Select Case Target.Column
    Case 10 To 36
        Target.Columns.ColumnWidth = 30
    Case Else
        Columns.ColumnWidth = 5
End Select
End Sub

Thank you for your answer!

However, it does not seem to work, as the columns doesn't become wider when I click on it :\

 

[My Aswer Is This]

Thank you for your answer!

However, it does not seem to work, as the columns doesn't become wider when I click on it :\

Well it works for me. When I click on column 10 to 36 the columns width becomes 30 any other column I click on becomes the width 5. So are you saying nothing happens when you click on any cell in column 10 to 30? When your not in these column the column width is 5

Did you install the script the way I explained?

But if you exit the column the with goes back to 5
Is that not what you wanted?

 

[My Aswer Is This]

You have several pieces of code here so I'm a little confused
So tell me in words what you wanting

My script says if you select column 10 to 36 column width= 30
Else column with = 5

When you move out of those columns the column with goes back to 5

 

[My Aswer Is This]

So tell me when your in what columns do you want the width to be 30
Now the sheet name is not important these type scripts work in the active sheet the sheet you put the code in

 

[AlexBB]

Well it works for me. When I click on column 10 to 36 the columns width becomes 30 any other column I click on becomes the width 5. So are you saying nothing happens when you click on any cell in column 10 to 30? When your not in these column the column width is 5

Did you install the script the way I explained?

But if you exit the column the with goes back to 5
Is that not what you wanted?

Yes, it is exactly what I'm aiming for

What I did was that I went into the 'View code' tab for the sheet that I'm working in and inserted the code there. Does it need to be a module instead?

Here's a screenshot from after the code, where I've selected column nr. 10:

And with the initial code, which only works for one column:

I don't know if merged cells affect the code, or if something else is getting in the way.

 

[My Aswer Is This]

Not sure why your mentioning the sheet name.
You showed this:
Worksheets("KARTLEGGING").Columns("J:AJ").ColumnWidth = 5

 

[AlexBB]

Not sure why your mentioning the sheet name.
You showed this:
Worksheets("KARTLEGGING").Columns("J:AJ").ColumnWidth = 5

It was after I tried making a module instead of inserting the code in the sheet itself, so thought it might help.

 

[AlexBB]

So tell me when your in what columns do you want the width to be 30
Now the sheet name is not important these type scripts work in the active sheet the sheet you put the code in

Oh, I didn't know, I'll remember that from now on, thanks!

It's from column J to AJ