EXCEL LIST

[Neil Holden]

Hi, i have a column with is very narrow, about the length of a charactor, if
the user selects column B for example a list choice appears, but the list
isn't wide enough to see each option, is they anyway you can automatically
open the list to the correct size and once selected go back to its original
size?

Thanks.

 

[Mike H]

Hi,

Right click your sheet tab, view code and paste the code below in. Note the
commented out line. You can use autofit or fixed widths with this line.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 2 Then
'Target.ColumnWidth = 8.5
Columns(2).EntireColumn.AutoFit
Else
Columns(2).ColumnWidth = 2#
End If
End Sub


Mike

 

[Patrick Molloy]

maybe you could use the sheet's selection change event -- rigth click th
etab, select view code and paste this:

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 2 Then
Columns(2).ColumnWidth = 20
Else
Columns(2).ColumnWidth = 2
End If
End Sub

whenever you select any cell, the code call this procedure. If the selection
is in B, the column with gets widened, otherwise it resets to the smaller
width

just an idea

 

[Neil Holden]

Hi, thanks for your reply, but i need it to go back to its original column
width size after its selected?

Thanks

 

[Patrick Molloy]

unfortunately the selection chaneg fikres after the change, so we need a
flag...in the code below, the boolean bSkip is the flag - its set to true if
a value changes in B and this prevents the column width expandin

Option Explicit
Private bSkip As Boolean
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 Then
Columns(2).ColumnWidth = 2
bSkip = True
End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If bSkip Then
bSkip = False ' reset the flag and exit
Exit Sub
End If
If Target.Column = 2 Then
Columns(2).ColumnWidth = 20
Else
Columns(2).ColumnWidth = 2
End If
End Sub