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]

The column width only stays at 30 if the active cell is in columns 10 to 30
Your Post says:
Make Drop Down Temporarily Wider
Temporary only means when your in that cell

 

[My Aswer Is This]

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

No it does not go in a module

 

[My Aswer Is This]

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

It's from column J to AJ

Thats what my script does columns 10 to 30

 

[My Aswer Is This]

OK so I have made several postings.
Tell me again what columns are we working with?

And is nothing happening when you select any column from 10 to 30

 

[AlexBB]

Just read the posts, and yes, I want the width to be 30 when the cell is active and revert to 5 when it isn't. I think I got confused between temporary and active.

The columns are from number 10 (J) to 36 (AJ). Now that I tried clicking around, I can see that the code works when I click in the column range, all except for the rows 3-5.
Is there a way to make it work for only when the merged cell from row 3-5 are activated? I had not considered this aspect at all

 

[My Aswer Is This]

My script is using case.
And when using case we do no use If

We use
case

Case Else

 

[AlexBB]

My script is using case.
And when using case we do no use If

We use
case

Case Else

Got it! Now it works like a charm. Thank you so much for the help

 

[My Aswer Is This]

Just read the posts, and yes, I want the width to be 30 when the cell is active and revert to 5 when it isn't. I think I got confused between temporary and active.

The columns are from number 10 (J) to 36 (AJ). Now that I tried clicking around, I can see that the code works when I click in the column range, all except for the rows 3-5.
Is there a way to make it work for only when the merged cell from row 3-5 are activated? I had not considered this aspect at all

I thought the whole ideal was column 10 to 36

Now you said:
Is there a way to make it work for only when the merged cell from row 3-5 are activated?
So do nothing about columns 10 to 36

Merged cells are always a problem. If you notice on this forum it nearly always suggested to not use merged cells.
I know of no way to make it work with merged cells.
And you said: all except for the rows 3-5.
Do you mean rows 3 to 5 of columns 3 to5?

 

[My Aswer Is This]

Got it! Now it works like a charm. Thank you so much for the help

Are you sure? Or are you tired of talking with me. I may sometimes be a little blunt. I like helping but get confused when user says it's not working.
Case is a great way to not need a lot of if statements.

 

[AlexBB]

Are you sure? Or are you tired of talking with me. I may sometimes be a little blunt. I like helping but get confused when user says it's not working.
Case is a great way to not need a lot of if statements.

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.