Alternative for data validation

[ghostkkz]

Hi everyone,

Currently facing a roadblock with a drop down list data validation approach and is seeking for alternatives.

The issue lies with the data list where options contains as much as 415 characters in total.

Traditional data validation list only displays 255 characters and I've tried 2 ways to resolve this issue:

1. Using macro to Zoom in at the text when data validation cells are selected/highlighted. Didn't work well at all.

2. Using a ActiveX control Combo Box, despite setting the width size to 300, Combox and Listbox don't support text wrap in drop down list. - screen shot attached.

Forum, i wonder do any of you have any brilliant alternatives to solve my issue?

 

[bjurney]

First break the lists down, maybe to 200 characters each in Column A to look something like this:

(Cell A1 This is where you want the first row of data and so that it carries here and
(Cell A2 then reads down here so it looks like one big thing. (10 spaces)

In cell B1 enter the formula =CONCATENATE(A1,A2)
in cell C1 enter the formula =SUBSTITUTE(B1," "," ",1)

Place the combobox where you would like it and choose Listfillrange and put in Sheet1!A1:A2 change list width to 400 pt


Private Sub ComboBox1_Change()
If ComboBox1.Value = range("A1") Then

ComboBox1.Value = Range("C1")

Else
If ComboBox1.Value = range("A2") Then
ComboBox1.Value = Range("C1")

End If
End If

End Sub

Repeat as needed for each data validation


That way if they choose either line it will combine the two lines but break it up as two to look a little cleaner.

Hope that helps

 

[T. Valko]

It'll work in a DV drop down. Doesn't look very good, though!

If you make the column wide enough that holds the drop down and you put line breaks in the source then format the cell with the drop down to wrap text then adjust the row height....

Looks like crap, but it works!