Alternative for data validation

ghostkkz

New Member
Joined
Dec 23, 2009
Messages
1
:) 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?
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

bjurney

Active Member
Joined
Aug 24, 2009
Messages
320
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

Well-known Member
Joined
May 9, 2009
Messages
16,623
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,710
Messages
5,597,701
Members
414,164
Latest member
ARTW

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top