Wordwrap in dropdowns (list from data validation)

[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

 

[Gord Dibben]

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

Gord Dibben MS Excel MVP

 

[Guest]

Thanks... this was very helpful!!

regards
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






Gord Dibben MS Excel MVP

 

[cmarion]

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

 

[Ross in Oz]

i have the same problem, did you ever get an answer? If not does anynone have
the code for multiple columns?

 

[Shane Devenshire]

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]

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]

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

 

[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

 

[Ross in Oz]

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