PDA

View Full Version : [SOLVED] Validation bugbears



Sir Babydum GBE
06-22-2005, 04:21 AM
OK, I guess this is gonna be a 'yes' or 'no' thing, so here goes.

On xl97 (possibly the other versions too) When you create a validation list, the things validated may include rather long expressions.

Now, you may want to resize the column on which the list appears and wrap the text so that the column doesn't have to take up the whole flippin' page. But have you noticed that the validation drop-down list get's narrower as your column does - and the choices available to the user are not wrapped - which means they can't see the darned things properly. This usually results in a barrage of "I hate your spreadsheet" insults from the poor people who have to use them. :motz2:

As much as i try to have wide shoulders(:mkay ), I'd still like to know if there any sort of option or code that can wrap the text in the validation drop-down list - or perhaps make the drop-down list wider?

This would solve one of my long-time pet peeves with Excel, and could even restore peace and harmony in the universe. The benefits could be endless. :wot

Bob Phillips
06-22-2005, 04:31 AM
On xl97 (possibly the other versions too) When you create a validation list, the things validated may include rather long expressions.

The obvious way is to temporarily widen the cell. Debra Dalgleish works it here http://www.contextures.com/xlDataVal08.html#Wider

Sir Babydum GBE
06-22-2005, 04:50 AM
Thanks again xld,

That works pretty well, but there are two slightly irritating things - s'pose you can't have everything.
1) With this code in the sheet, it does the job of widening the cell, but then the arrow dissapears and you have to re-click on the cell to get it back.
2) Peace and Harmony don't seem to have been restored in the universe - as I had predicted. perhaps i need to tweak the code?

Bob Phillips
06-22-2005, 04:59 AM
1) With this code in the sheet, it does the job of widening the cell, but then the arrow dissapears and you have to re-click on the cell to get it back.

That is true of Data Validation period, nothing to do with the code. If you need to have an arrow at all times, you need a combobox (but has other disadvantages). If you just need to know where it is, colour it.

Sir Babydum GBE
06-22-2005, 05:19 AM
Ok,

Like I said, I can't have everything... Actually, I don't mind these little things anyway - it's more the end-users who moan until my ears drop off.

Thanks again - they'll have to make do!!

Bob Phillips
06-22-2005, 05:35 AM
Thanks again - they'll have to make do!!

Bang goes your medal.:doh:

Sir Babydum GBE
06-22-2005, 05:46 AM
Nah, it's still a work of art - just one that requires a teeny bit more patience, that's all. :)