Cell only expands vertically

[bbeier]

Hi,

I'm in desperate need to be able to type in cells G3 through G34 and the cell expands vertically (not horizontally) with my writing. I'm currently using "Word Wrap." I would also like to be able to use bullet points (rich text) and press the "enter" button without the focus moving to a different cell.
Right now word wrap works until all the vertical space has been used, and then just shows "########" across the width of the cell.
All help is sincerely appreciated!!!

 

[santeria]

I have the feeling that this was dealt with in some detail previously, so it's in the archive, but I can't think what the terms of the reference were...

Someone gave a very concise and useful code for it previously.

 

[Greg Truby]

Are you using <Alt>+<Enter> at the end of each line of text to embed a CarriageReturn? (Or is it a LineFeed? I forget.)

 

[bbeier]

Such speedy reply the week of Christmas, I'm speechless!!
The alt + enter is a big help - thank you.
There is an article entitled "Auto Expand" and I've been trying to play around with the code, but can't get it to work.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Cells.Columns.AutoFit
End Sub

Do I have to specify the Range or Target??
ray:

 

[HalfAce]

Hi Brad and welcome to the board,
When I tried your Autofit code it did nothing. You might want to change it to this:

Private Sub Worksheet_Change(ByVal Target As Range)
Cells.Columns.AutoFit
End Sub

You would only need to specify a range if you want only that range to autofit your entries. (Right now you have it set for the entire sheet.) An example for limiting the columns you want this done in would be:
Columns("A:D").AutoFit - for only columns A through D.

If you want only a specific range to autofit, you could write your code something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column > 4 Or Target.Row > 10 Then Exit Sub
Target.Columns.AutoFit
End Sub

This will autofit for only columns A through D, and only when there is an entry made in the range A1:D10.

As for being able to press Enter and have the active cell stay where it is, go into Tools > Options > Edit tab and uncheck the "Move Selection After Enter" checkbox. (It’s the fourth one down on the left side.) This will set that to be the case in all your excel applications though.

Hope some of this helps,
Dan

 

[Yogi Anand]

Hi Dan:

The Workbook_SheetChange event code needs to be installed in ThisWorkbook and the code applies to every sheet of the workbook

in contrast to

The Worksheet_Change event code is installed in a worksheet and applies to that particular worksheet only.

So the code for the Workbook_SheetChange event when installed in the right place does work.

 

[HalfAce]

Ah yes Yogi,
I see the error of my ways, and you're right about where I place it.
Thanks.
Dan

 

[Yogi Anand]

Hi Dan:

Cheers -- That AutoFit feature is reallty cool!

 

[bbeier]

All the responses have been tremendously usefull - thank you.
The autofit code will come in handy for a couple other spreadsheets I'm using, but still wondering if there is a way where autofit will only expand the cells vertically, instead of horizontally.

 

[HalfAce]

Hi Brad.
I was thinking about your question again and now I'm wondering...what do you mean by using "Word Wrap"? (Is this actually "Wrap text"?) Because that seems to be doing just what you've asked for.
(Highlight cells G3 through G34 and choose Format > Cells > Alignment tab and check "Wrap text" in the lower left hand corner.) This will increase the height of the entire row though, but what I was thinking of doing would have done that anyway.

Is Word Wrap something different, or are you actually using Wrap text and it isn't working for you?

Dan