Using Excel For Note Taking

[miketurn]

I find myself using Excel more and more to take notes with, typically most will use Word for this purpose but I feel that Excel offers many benefits that Word does not. However, Excel unfortunately has some key limitations as well which is why I am here.

The main limitation I am dealing with is the issue of text wrapping.
I would like the ability to keep all rows at the same height, I find that applying the "wrap text" option creates a ton of different row heights and it becomes too confusing visually for me. When using Excel for note taking it is also nice to have each row of text be its own entity where you can add new rows in between, delete entire rows, etc. When text is wrapped that convenience is lost in my opinion.

What I currently am doing is setting a relatively average column width that stays the same, and when I get to the end of that column width with text I have to hit enter to go to the next row.
For obvious reasons this becomes increasingly annoying to deal with, and also if you want to change the column width in the future this method will cause problems.

What I ideally would like is a built in setting that uses the column width vertical line in a "wrap to ruler" or "wrap to window" kind of a setting found in Word. Where when the text goes over the column width it automatically puts you into the next row, but this does not exist.

On a few other threads I have tried to come up with some ideas for some macro tools to request, and many users have been extremely helpful, but I was wondering if anyone has any ideas of how something like this may be achieved in Excel, I don't think such a setting exists within the program itself.

I have tried the approach of typing in Word and then pasting into Excel but it is rather inconvenient and with 256 character limitation of each cell, it becomes tricky to not lose notes without knowing it, as well as I lose a lot of the conveniences of using Excel.

I am also in the process of looking into software designed specifically for note taking, but I am afraid I have gone too far with Excel to turn back now.

(I have more I can explain, but I don't want this post to get too lengthy.)

If anyone has any ideas, please let me know.
Thank You

 

[tonyyy]

miketurn,

Sorry, was away for a long weekend.

Type an apostrophe (') in front the line...

On Error GoTo errHandler

And then type in some text.

Does the text split into multiple rows?
Does the text truncate or repeat?
What is the error number? Error description? And what line of code is highlighted when the error occurs?

 

[miketurn]

@tonyyy
No problem, thank you for coming back to this thread.

Okay so if I run your macro in its original state I get what I mention in post #6 of this thread.
If I put an apostrophe in front of the following line as you mention

On Error GoTo errHandler

I get a dialog box that pops up and reads....

Runtime Error '438':
Object doesn't support this property or method

When I select "debug" from the above dialog box, it highlights the following line of your macro.

Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row).RemoveDuplicates Columns:=1, Header:=xlNo  'Change to match your column

The last example I tried, when I select the "debug" I can see in the spreadsheet that it wrapped the first two lines of text okay, but then it appears that the third line of text repeats about 5 times.

When I am testing out the macro I am testing it out in Column A.
I tried it in Row 1 and also a few other rows and they all seem to produce the same results.

I kept the "fudgefactor" at 4
The only thing I changed is the ColumnWidth to 50


If you need any more information, please let me know.
Thank You again for your help.

 

[tonyyy]

miketurn,

What version of Excel are you using? And is it on a PC or MAC?

 

[mole999]

did you research using word with a blank table predefined, when you are finished you can lift and place that in excel

 

[tonyyy]

miketurn,

I haven't been able to reproduce the 438 error, so I used another approach to replace the offending line...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ColumnWidth As Long
Dim txtLength As Long
Dim i As Long, j As Long, k As Long, StartPosition As Long, n As Long
Dim LastRow As Long, fudgeFactor As Long
Dim rng As Range

Application.ScreenUpdating = False
On Error GoTo errHandler
ColumnWidth = 50 'Change to match your column width
LastRow = Cells(Rows.Count, "A").End(xlUp).Row 'Change to match your column
k = 0
fudgeFactor = 4 'Adjust up if text is being truncated.

If Not Intersect(Target, Columns(1)) Is Nothing Then 'Columns(1) is Column A; change to match your column
    Application.EnableEvents = False
    i = Target.Row
    StartPosition = 1
    txtLength = Len(Cells(i, 1).Value)
    If txtLength > ColumnWidth Then
        Rows(i).Copy
        Rows(i).Resize(Round((txtLength / ColumnWidth) + fudgeFactor, 0), 1).EntireRow.Insert
        Application.CutCopyMode = False
        For j = 0 To Round((txtLength / ColumnWidth) + fudgeFactor, 0)
            If j = 0 Then
                For k = ColumnWidth To 1 Step -1
                    If Mid(Cells(i, 1).Value, k, 1) = " " Then
                        Cells(i, 1).Value = Mid(Cells(i, 1).Value, 1, ColumnWidth - (ColumnWidth - k))
                        StartPosition = k
                        Exit For
                    End If
                Next k
            Else
                For k = ColumnWidth To 1 Step -1
                    If StartPosition + k > txtLength Then
                        Cells(i + j, 1).Value = Mid(Cells(i + j, 1).Value, StartPosition, ColumnWidth)
                        Exit For
                    ElseIf Mid(Cells(i + j, 1).Value, (StartPosition + k), 1) = " " Then
                        Cells(i + j, 1).Value = Mid(Cells(i + j, 1).Value, StartPosition, ColumnWidth - (ColumnWidth - k))
                        StartPosition = StartPosition + ColumnWidth - (ColumnWidth - k)
                        Exit For
                    End If
                Next k
            End If
        Next j
    End If
End If
LastRow = Cells(Rows.Count, "A").End(xlUp).Row 'Change to match your column
For n = LastRow To 2 Step -1
    If Cells(n, 1) <> "" And Cells(n, 1) = Cells(n - 1, 1) Then Rows(n).Delete
Next n
errHandler:
    If Err.Number <> 0 And Err.Number <> 1004 Then MsgBox Err.Number & ": " & Err.Description
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

It should be noted that the code won't allow two successive rows with the same text. For example, if you type "nota bene" in line 3 and again in line 4, line 4 will be deleted.

 

[miketurn]

@tonyyy
Thank You so much for providing an updated version of your macro.
I wish you would have been able to replicate the same error, just so we could be on the same page.
Without anyone giving me too much flak for it, I am currently using Excel 2003, do you think that is what is causing the error?
I obviously don't know much about the VB macro language, I wouldn't think it would really change that much, but would you know if that line of code that I mention I am getting the error on, is some kind of newer addition to the language?
Hopefully the fact that I am losing 2003 won't make you lose interest.

Has anyone else tried out the original macro provided by @tonyyy and received the same error code mentioned?

 

[miketurn]

@tonyyy
That being said, I have tried out your new update, and I no longer am getting the error.
I have however noticed a couple of things so far, if you are interested in continuing to help me....
1.) So far haven't really noticed anything getting "truncated" I am noticing that I am getting many rows that contain a space at the beginning of them. Would there be a way for you or someone else to be able to have the macro remove this extra space at the beginning of each row when they contain a space? This kind of coding can be found here...

https://www.mrexcel.com/forum/excel-questions/991831-remove-extra-spaces-within-cells.html

2.) Probably not the smartest idea, but I am thinking of applying your macro to my "personal.xls" file which would apply your macro to all my spreadsheets. I know you probably had to add this to your update to solve the issues I was having, but the fact that it removes rows that come after each other that have the same content scares me just a little. I don't think it will be a problem overall so it isn't a big deal to fix, but if there is an easy way just to be safe that would be great. Like I said I imagine you had no choice but to add this because of my situation.

3A.) When the updated macro is applied it does not wrap the text directly at the column width (I have set it in the macro to 50)
It will let a couple words in each row to go beyond the column width.

3B.) If I apply "text wrap" to my rows (as you mentioned to do in the original macro you provided just to give an idea of what the text will look like) the macro will not work correctly. It will take those couple of words that go beyond the column width (mentioned above in 3A) and it will wrap them making the rows double in height to fit those couple little words, but as a whole it will continue wrap the text the way the macro is designed too. So any rows that the text goes beyond the column width become "text wrap" rows with larger row heights and rows that the text does not go beyond the column width remain the proper row height. Hopefully this makes sense.
Not sure if you could replicate 3A and 3B on your system?

Anyways though, just the fact that you have been able to take it this far is absolutely amazing me, I would not have thought this to even be possible, so thank you again for this. Obviously it is up to you how far you would like to go with the macro, but thank you so much for your help regardless, even in its current state your macro is very helpful.

 

[tonyyy]

...

 

[tonyyy]

1. Add the code in red to the bottom of the macro...

LastRow = Cells(Rows.Count, "A").End(xlUp).Row 'Change to match your column
For n = LastRow To 2 Step -1
    If Cells(n, 1) <> "" And Cells(n, 1) = Cells(n - 1, 1) Then Rows(n).Delete
Next n
[COLOR=#ff0000]LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For n = 1 To LastRow
    Cells(n, 1).Value = Trim(Cells(n, 1).Value)
Next n[/COLOR]
errHandler:
    If Err.Number <> 0 And Err.Number <> 1004 Then MsgBox Err.Number & ": " & Err.Description
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

2. Not sure if/how the macro would work from the "personal.xls" file. Currently you have the code installed in a Sheet module. To work on all the sheets in a workbook the code would need to be installed in the "ThisWorkbook" module, with the the SheetChange or SheetSelectionChange option. For example...

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

3A. Please recall from my original response: "Be aware, the code is based on a poor assumption - that the column width is equal to the number of characters in that column. The choice of font, size, style, etc will likely change the outcome of the wrapping." My testing was done using Calibri 11; I'm guessing you're using a different and larger font. Try changing the column width setting in the macro to a smaller number until it's closer to your desired result.

3B. "Text Wrap" is related to the answer in 3A, so changing the column width in the macro will also affect the text wrap/row height on screen. You could also keep "Text Wrap" turned off.

...so thank you again...

You're most welcome.

 

[mole999]

@miketurn 2003 is significantly different to later versions, excel went onto use xml format data, anything that was added to later versions will not be backward compatible, that said the 2003 version should be usable forward