Making merged cells auto resize to fit text

[OleMiss2010]

I am working on a macro that will include an input box that has the user type a paragraph long response. That part is easy. The challenging part has been getting the area it goes into which is a 2 rows by 9 columns merged cell to auto resize to fit the text if it is bigger than the space. I don't necessarily need the code for this; I just need to figure out how to make it happen. This seemingly easy task is driving me nuts. I've tried text wrapping and other obvious techniques and nothing works. Any ideas?

 

[vijaySharma]

OleMiss,


Try the below code...

Sub autoFitMergeCells()

Dim strInput As String


    strInput = InputBox("Enter data")

[dataCell] = strInput


    With Range("dataCell")

.WrapText = True

.RowHeight = .RowHeight * 2

End With

End Sub

~VijaySharma

 

[OleMiss2010]

I'm still experiencing the same problem. If the text in the input box takes up more space than the cells it is going into then it will not auto size the cells to make all of the text visible.

 

[vijaySharma]

OleMiss,


1. Select the merged cell (2 rows x 9 cols)

2. Define a range name "dataCell" as I have used in the example above.


Now if you run the above code it shoud resize the cell.


~VijaySharma

 

[OleMiss2010]

Now I'm noticing something else, is there a limit on the number of characters that can be used in an input box?