Drop Down Question 2

[Scott24x7]

Is it possible to have a drop down with options (like Yes, No, Sometimes) but also allow the user to put whatever they want in the field? Drop-down I've seen only under "data validation" but I just want to make it easy to pick common options, but still need users to be able to put in what they want for some of them, which can't be determined prior to their answering the question.

Is this possible? (I would prefer not to use a messy com object).

Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."

 

[SkipVought]

hi,

What application and version?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Scott24x7]

Skip,
Completely flexible on that. If it's only possible in 2007 - 2010 that's fine, if it works across all of them, that's ok too.


Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."

 

[SkipVought]

APPLICATION please.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Scott24x7]

Excel. Sorry.

Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."

 

[SkipVought]

Excel has auto complete, which essentially does that without a control, as long as those values already exist in the column.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Scott24x7]

Skip,
Yeah, but that is not what I am after... for exactly the reason you point out, that 1) it must already exist in the column (and I've noticed that section breaks where merged cells appear above them can disrupt that too), and 2) longer repeated values still have to be typed until there is a "difference", and in this case, that will happen frequently.
Any other ideas?


Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."

 

[SkipVought]

1. You are crazy for having merged cells in a table! That REALLY messes the soup and destroys many of Excel features, as you have discovered!

2. longer repeated values still have to be typed until there is a "difference", because they are not in your yes, no, sometimes list anyway!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Scott24x7]

Skip,
Point 1 - HA! Thanks, yeah, unfortunately I don't always "get my way" when it comes to layout, and my client takes the trade off...
Point 2 - Yeah, but by the time you've typed all but the last word... there's little savings in entry time. I'll just tell them "not possible".


Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."

 

[SkipVought]

Really, the ONLY thing that makes this 'Not Possible' is the MERGED CELLS.

I have a workbook that has a list of 200+ distinct values that usually takes only 2 or 3 strokes to make a unique hit. In a few cases, nearly the entire value.
[tt]

[red]MERGED CELLS[/red]

[/tt]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gruuuu]

A while ago, I made myself this addition to a custom add-in:

Private Sub CAS()
Selection.HorizontalAlignment = xlCenterAcrossSelection
End Sub

I then added a custom button right beside the Merge Cells button specifically for this.

And, Skip, before you ask me "Why did you leave the Merge button there?!?!?!", it's for workbooks sent to me, to unmerge and CAS easily

 

[SkipVought]

Gruuuu. Now THERE's some thinking 'outside the merge box!' ==> *

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Scott24x7]

Gruuuu,
The subtly is lost on me. (Sorry). What does this do?


Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."

 

[SkipVought]

It centers across a selected range WITHOUT MERGING.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Scott24x7]

Ahhh.... I see the value of that now. Very cool!


Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."

 

[Gruuuu]

Even better than that, too. It formats each cell in your selection to use that behavior.
So let's say you have data that looks so:

Header1           Header2            Header3
Sub1    Sub2      Sub3     Sub4      Sub5     Sub6

So if you select all the cells above your subheaders and CAS, it will center each cell content through the empty cells, and will result in:

   Header1           Header2            Header3
Sub1    Sub2      Sub3     Sub4      Sub5     Sub6

Bam. One selection, one button.

 

[SkipVought]

Bam

I like that!

Bam, thats good!

That's a Bam sweet solution!

Hot Bam!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}