[RESOLVED] WordWrap In Excel

I have the following lines of code in VB6

VB Code:

shWorkSheet.Range("A29") = "Comments"
        shWorkSheet.Columns("A:BZ").AutoFit
        bkWorkBook.Worksheets(1).Columns(30).WrapText = True
        shWorkSheet.Range("A30") = txtComments.Text
        bkWorkBook.Worksheets(1).Columns(30).ColumnWidth = 12

txtComments is a multiline text box and I need to display the contents in an Excel spreadsheet as a block of text.

Right now, the comments are all in row 30 and spreading across in a single line to about column I or J.

What modifications do I need to make?

Hack
Can you post a screenshot of the s/sheet? I'm having a hard time visualizing your data layout.

The only way I could save it small enough to fit as an attachment was to save it as monochrome, but you should get the idea. As you can see, the comments just scroll in a straight line completely off the screen.

Change your code

VB Code:

shWorkSheet.Range("A29") = "Comments"
        shWorkSheet.Columns("A:BZ").AutoFit
        bkWorkBook.Worksheets(1).Columns(30).WrapText = True
        shWorkSheet.Range("A30") = txtComments.Text
        bkWorkBook.Worksheets(1).Columns(30).ColumnWidth = 12

to

VB Code:

shWorkSheet.Range("A29") = "Comments"
shWorkSheet.Columns("A:BZ").AutoFit
With shWorkSheet.Range("A30")
    .Value = txtComments.Text
    .WrapText = True
    .ColumnWidth = 20 'You will need to tweak the width
    .RowHeight = 20 'You will need to tweak the height
End With

This will allow you to adjust the height and width of the comments cell.

Hack
One small change. Rather than manually setting both the width and the height, I would suggest you set the width to your desired value and then AutoFit the height to ensure that the entire comments block is visible.

VB Code:

.ColumnWidth = 20 'You will need to tweak the width
    .Row.AutoFit 'Ensures that all data is visible

Quote:

---

Originally Posted by DKenny

Hack
One small change. Rather than manually setting both the width and the height, I would suggest you set the width to your desired value and then AutoFit the height to ensure that the entire comments block is visible.

VB Code:

.ColumnWidth = 20 'You will need to tweak the width
    .Row.AutoFit 'Ensures that all data is visible

---

VB Code:

With shWorkSheet.Range("A30")
            .Value = txtComments.Text
            .WrapText = True
            .ColumnWidth = 40
            .Row.AutoFit '<==== error:  Invalid Qualifier
        End With

I've been playing around and it seems a RowHeight = 40 and a ColumnWidth = 40 should suit my needs.

However, I like the AutoFit thing, but I get the above stated error message when I tried to use it.

My bad, change .Row to .EntireRow and the AutoFit method will work.

Quote:

---

Originally Posted by DKenny

My bad, change .Row to .EntireRow and the AutoFit method will work.

---

Dude, you are on my Christmas card list! :D

That worked.