Excel Word Wrap Problem after Macro

Question

I have a macro that I use to receive data from an InputBox and then insert that data into a cell. I am having some formatting issues with the data after the following macro runs.

Sub InsertNotes()
'
' insertnotes Macro
'

'
    Dim UserNotes As String

    UserNotes = InputBox(Prompt:="Please enter your note below:", Title:="Note input", Default:="Notes")
    If UserNotes = "" Then Exit Sub
    ActiveSheet.ListObjects("Notes").ListRows.Add (1)
    ActiveSheet.Range("Notes").Cells(1, 1) = Date
    ActiveSheet.Range("Notes").Cells(1, 2) = UserNotes

End Sub

The table cells are formatted to have word-wrap on, but when the notes are inserted into the table the cells are not wrapped. However, if I run the macro again and insert a new note, the previous note that was inserted will show up as wrapped even though nothing has happened to it other then being shifted down a row. Is there something I can do within the code or formatting to get it to wrap properly?

Answer 1

The line

ActiveSheet.ListObjects("Notes").ListRows.Add (1)

adds new cells to your sheet which inherit their format from the list heading. So what you have to do is make sure the heading cells have word wrap enabled, too. As an alternative, you could add the word wrap property afterwards, like this:

 ActiveSheet.Range("Notes").Cells(1, 2).WrapText = true

Answer 2

The only fix I've found so far for this is to Insert the row that I need and then insert and delete one more row after that. For some reason the word wrap property will kick in and start working after the insertion (and then it's deleted as it's not needed).

Sub InsertNotes()
'
' insertnotes Macro
'

'
    Dim UserNotes As String
    ' Turn off screen updating
    Application.ScreenUpdating = False
    UserNotes = InputBox(Prompt:="Please enter your note below:", Title:="Note input", Default:="Notes")
    If UserNotes = "" Then Exit Sub
    ActiveSheet.ListObjects("Notes").ListRows.Add (1)
    ActiveSheet.Range("Notes").Cells(1, 1) = Date
    ActiveSheet.Range("Notes").Cells(1, 2) = UserNotes
    ActiveSheet.Range("Notes").Cells(1, 2).WrapText = True
    ' Crap fix to get the wrap to work. I noticed that after I inserted another row the previous rows
    ' word wrap property would kick in. So I just add in and delete a row to force that behaviour.
    ActiveSheet.ListObjects("Notes").ListRows.Add (1)
    ActiveSheet.Range("Notes").Item(1).Delete
    Application.ScreenUpdating = True

End Sub

Doesn't seem very ideal, but it gets the job done until I can know what the proper answer is.