Aug 06 2021 08:30 AM
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!
Aug 06 2021 08:51 AM
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
Aug 06 2021 08:56 AM
Aug 06 2021 12:01 PM
SolutionI 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
Aug 09 2021 05:47 AM
Thank you so much @Hans Vogelaar !
I do not understand how but it works!
Aug 06 2021 12:01 PM
SolutionI 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