Drop down list column width change
 

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!

Drop down list column width change
 

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


"dspitler" wrote:

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!

Drop down list column width change
 

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

On Tue, 25 Mar 2008 12:06:04 -0700, dspitler
wrote:

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!