Thanks Shane, works a treat now, you are the Wizard
Ross

"Shane Devenshire" wrote:

Hi,

Here is the general code:

Public W As Integer, Y As Integer

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Y = Target.Column
If Target.Count 1 Then Exit Sub
If Target.Column = 3 And Target.Column <= 50 Then
Target.Columns.ColumnWidth = 20
Else
Exit Sub
End If
If Target.Column = W Then
Exit Sub
Else
Columns(W).ColumnWidth = 8.34
End If
W = Y
End Sub

============
The specific code for your case is

Public W As Integer, Y As Integer

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Y = Target.Column
If Target.Count 1 Then Exit Sub
If Target.Column = 4 Then
Target.Columns.ColumnWidth = 60
ElseIf Target.Column = 9 Then
Target.Columns.ColumnWidth = 100
ElseIf Target.Column = 10 Then
Target.Columns.ColumnWidth = 80
End If
If Target.Column = W Then
Exit Sub
ElseIf W = 4 Then
Columns(W).ColumnWidth = 10
ElseIf W = 9 Then
Columns(W).ColumnWidth = 15
ElseIf W = 10 Then
Columns(W).ColumnWidth = 12
End If
W = Y
End Sub

If I had written yours from scratch I might have used a Select Case
structure, but this one will work.

If this helps, please click the Yes button.

Cheers,
Shane Devenshire
"Ross in Oz" wrote:

Shane
Thanks for the quick response, I tried the code but got an error
Could you give a example of the total code where I input the second and
third columns to be widened.

ie make column 4 to widen to 60 and return to 10
and make column 9 to widen to 100 and return to 15
and make column 10 to widen to 80 and return to 12

or is it one setting for all columns


also is the a limit to the enteries

Thanks in advance

Ross in Oz


"Shane Devenshire" wrote:

Hi,

Here's some code that might do what you want

Public W As Integer

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count 1 Then Exit Sub
If Target.Column = 3 And Target.Column <= 50 Then
Target.Columns.ColumnWidth = 20
End If
If Target.Column = W Then Exit Sub
Columns(W).ColumnWidth = 5
W = Target.Column
End Sub

If this helps, please click the Yes button.

Cheers,
Shane Devenshire

"Ross in Oz" <Ross in wrote in message
...
i have the same problem, did you ever get an answer? If not does anynone
have
the code for multiple columns?

"cmarion" wrote:

The temporary widening works great, but I need it for about 50 columns.
Do
you know how to alter the code to accomodate this? Columns are Cc to AX

"Gord Dibben" wrote:

Arun

You cannot word-wrap a list for DV.

What you can do is use event code to make the list wider when you click
on the
drop-down.

See Debra Dalgeish's site for code.

http://www.contextures.on.ca/xlDataVal08.html#Wider




On Tue, 3 Oct 2006 02:15:02 -0700, Arun
wrote:

Can I have the text that appears in the dropdown lists (using
datavalidationlist) appear word wrapped? Currently long text in the
dropdown list is truncated to fit column width.

The text boxes themselves are wordwrap enabled, but this does not make
a
difference- pls drop a line if you know how to resolve this.

thanks in advance!
Arun

Gord Dibben MS Excel MVP