Drop down list column width change

[dspitler]

I used Debra Dalgleish's website to Make the Dropdown List Temporarily Wider
(http://www.contextures.com/xlDataVal08.html) and I put the following code in
my sheet:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column = 3 Then
Target.Columns.ColumnWidth = 30
Else
Columns(3).ColumnWidth = 7
End If
End Sub

This works great for the whole column, but I only want the column to change
size when I select cells that have drop down lists in them. The way it
currently is set up is that when I click on any cell that is in column 3, the
column expands to 30. Is there a way to set up the code so that the column
width temporarily changes only when I select a cell with a drop down list?

I'm new to VB, so please be easy on me.

Thank you!

 

[JLGWhiz]

I did not test this, so try it out and post back if there is a problem.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column = 3 And Target.Validation.Value = True Then
Target.Columns.ColumnWidth = 30
Else
Columns(3).ColumnWidth = 7
End If
End Sub

 

[Gord Dibben]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const WS_RANGE As String = "C1,C5,C12" 'adjust to suit
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
For Each cell In Target
Target.ColumnWidth = 30
Next
Else
Columns(3).ColumnWidth = 7
End If
End Sub


Gord Dibben MS Excel MVP