Alternative to Wrap text in Excel?

Question

I am making a spreadsheet that offers a column for user input. Each worksheet changes based on the month the user has given.

If Target.Value = "January" Then
    ActiveSheet.Range("K7:K44").Value = ActiveSheet.Range("BA7:BA44").Value
ElseIf Target.Value = "February" Then
    ActiveSheet.Range("K7:K44").Value = ActiveSheer.Range("BB7:BB44").Value
End If

Now the actual macro is cleaner but anyways.. Now if say cell BA7 contains a string that doesn't fit in K7 the client wants a "drop down box" to show up on K7 like a Data Validation List. I have searched and searched and I don't think it's possible but the client says they have seen it somewhere before.

So they don't want wrap text, they don't want shrink to fit.. They want their drop down arrow. Is there any possible way of doing this?

Answer 1

It's kind of ghetto, but maybe they're talking about this: if you go to Data -> Validation... and then Allow and List, you can choose to make a cell a reference itself by selecting it as its own source. Then you'll have a drop down box whose sole purpose is to display the value in its own cell and is only visible when the cell is selected.

That seems to work for some values, but for really long entries it still truncates the information. That's all I can guess; if that's not it then I'm interested to see what the answer is.

Answer 2

AFAIK the dropdown validation width is controlled by the cell width, so you can't do that with a dropdown.

I think one (ugly) way to do something similar to what your client saw is to create a VBA control to display the values, and set it hidden.

Then hook on Worksheet_SelectionChange(ByVal Target As Range), check if the target is in the expected range, place the control appropriately below the cell , change its Value property to the cell content and show it. Otherwise just hide it again.

EDIT:

You need only one combobox or listbox per Worksheet (I'm not sure if one is enough for all worksheets). You can change the TopLeftCell property to display it where you want.