Forum Discussion
DJ_Excel
Aug 06, 2021Copper Contributor
Run macro if columns in a specific line are selected
Hi,
I barelly remeber how to write VBA so i tend to find scripts on the internet.
I found one that i wanted to modify but i cant seem to make the correct changes.
Essentially i want to add a condition to this code, so it would only work when the columns on line 65 are selected. Withoout changes the code works fine.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim cols As Variant, col As Variant
If Target.Count > 1 Then Exit Sub
cols = Array(8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28)
For Each col In cols
If Target.Column = col Then
Target.Columns.ColumnWidth = 50
Else
Columns(col).ColumnWidth = 15
End If
Next
End Sub
My latest try was this.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'define variables for lines and columns'
Dim cols As Variant, col As Variant
Dim lins As Variant, lin As Variant
'Do not run macro if more than one cell is selected'
If Target.Count > 1 Then Exit Sub
'define array as line 65 from column 8 to column 28'
lins = Array(65)
cols = Array(8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28)
For Each lin In lins
If Target.Line = lin Then
For Each col In cols
If Target.Column = col Then
'Increase column width temporarily'
Target.Columns.ColumnWidth = 50
'reduce column width back to original value'
Else
Columns(col).ColumnWidth = 15
End If
End If
End If
Next
End Sub
Any one want to point out my probably obvious oversight or mistake?
Thank you so much!
I see. Here is a new version:
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Range("H1:AB1").ColumnWidth = 15 If Target.Count > 1 Then Exit Sub If Target.Row <> 65 Then Exit Sub If Target.Column >= 8 And Target.Column <= 28 Then Target.ColumnWidth = 50 End If End Sub
4 Replies
It's a bit simpler:
Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Count > 1 Then Exit Sub If Target.Row <> 65 Then Exit Sub If Target.Column >= 8 and Target.Column <= 28 Then Target.ColumnWidth = 50 Else Target.ColumnWidth = 15 End If End Sub
- DJ_ExcelCopper ContributorThanks HansVogelaar , this is much more elegant.
It doesn't seem to go back to size 15 after i click out though...I see. Here is a new version:
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Range("H1:AB1").ColumnWidth = 15 If Target.Count > 1 Then Exit Sub If Target.Row <> 65 Then Exit Sub If Target.Column >= 8 And Target.Column <= 28 Then Target.ColumnWidth = 50 End If End Sub