Wordwrap in dropdowns (list from data validation)

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Can I have the text that appears in the dropdown lists (using
data>validation>list) 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
 
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




Can I have the text that appears in the dropdown lists (using
data>validation>list) 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
Click to expand...

Gord Dibben MS Excel MVP
 
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
 
i have the same problem, did you ever get an answer? If not does anynone have
the code for multiple columns?
 
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
 
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
 
Hi,

I'm working today but I will try to look at this at noon. Originally it
worked on my machine, but maybe I didn't copy all of it.

Cheers,
Shane Devenshire
 
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
 
Back
Top