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

 

[miketurn]

Here is one of the tools (among many) I requested on another thread that a forum user provided for me.
It allows you to type beyond the column width and when this macro is applied it wraps the text based on the column width.
What I have recently found out is that even though a cell shows beyond 256 characters of text, once this macro is applied it will only wrap 256 characters worth, chopping the rest beyond 256 characters off completely. Not sure if there is a way around this but anyways here is the macro example

Sub Justify_Selection()
Application.DisplayAlerts = False
Dim c As Range
    For Each c In Selection
     c.Justify
    Next
End Sub

 

[My Aswer Is This]

Why not use a Excel Textbox?

 

[tonyyy]

miketurn,

The following is not at all a complete solution but you're welcome to give it a try...

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
Dim LastRow As Long, fudgeFactor As Long

Application.ScreenUpdating = False
On Error GoTo errHandler
ColumnWidth = 20 '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
Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row).RemoveDuplicates Columns:=1, Header:=xlNo  'Change to match your column
errHandler:
    If Err.Number <> 0 Then MsgBox Err.Number & ": " & Err.Description
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

The code should be pasted into the worksheet module associated with the worksheet where you're entering/editing notes. 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.

There are several places in the code where you can make adjustments, and is so noted by a comment.

I might suggest that you enable "wrap text" in the column you're entering/editing notes - just for the visual effect it provides. Once you hit the enter or tab key, or select another cell, the text will wrap into the cells below.

Cheers,

tonyyy

 

[miketurn]

As I mentioned I was looking around for different note taking programs and I came across "OneNote" which looks to be an amazing product and has the ability to work with Excel spreadsheets which would be great for me since I have already gone pretty far with Excel. The problem is that even though it is free you can't download an offline installer for it. I just want to be able to download a program and have it, I am not a fan of online installers and all this account info requirements, so unfortunately this product probably isn't for me, but I am sure others may enjoy it, so give it a look if you don't know about it.

Others have complained about this as well, looking for offline installers, but it seems it does not exist in the simple just download approach method I am looking for. If anyone knows of a way to download an offline installer please let me know, because I would love to try this program out.

I have also looked into Evernote, but unfortunately get errors when trying to download it for Windows so I have not been able to try this one either. I thought I would be able to find many simple free programs to try, when you think of it, for the most part a note taking tool is just a text editor that allows you to save multiple tabs. If anyone knows of any programs I could try, please let me know.

Thank You

 

[miketurn]

@My Aswer Is This
Thank You for your response, to be honest I did not even know "textbox" tool existed, but I would imagine this method would eliminate all the features I enjoy about using Excel for note taking.

@Tonyyy
Thank You for your response, now what you have provided looks to have some amazing potential for what I am looking to do.
I tried out the macro you provided but unfortunately I was not able to get it to work properly.
Once I insert this macro into the worksheet and I try to create / edit notes I get the following error.....

"438: Object doesn't support this property or method" OK (button)

If I hit enter or click the "ok" button it repeats the last line of text about 9 times.

Overall though, as I mentioned this seems like it may be an amazing tool for what I am looking to achieve.
I like how it gets implemented to work automatically without having to manually run a macro each time, as long as it does not affect other Excel functionality, that appears to be the way to go.
Because I have a lot of spreadsheets / tabs within those spreadsheets, I would have to make the decision to maybe try putting this into my "Personal.xls" spreadsheet so it applies to all my spreadsheets, but not sure yet how that would function.

If anyone knows why this error occurs, or if I am doing something wrong, please let me know.

Thank You again

 

[Taul]

Hi,
May I suggest Microsoft Onenote, I'm not sure which version of Office you are using but it came included in my Home and Student version (2010)
I must admit, I don't use OneNote but both my children (now young adults) used it for note taking during lectures. It looks like a combination between Excel and Word, such that you have page tabs along the top for topics and also page tabs on the side for subjects. You don't get the functions that you would get in Excel but for note taking, my kids recon it's the best.
I think its also available as an App for android.

cheers
Paul.

 

[My Aswer Is This]

Not sure I understand:
Your quote:
"but I would imagine this method would eliminate all the features I enjoy about using Excel for note taking."

Using Textbox does not effect other Excel functions
And the Textbox could be programmed to hide or be show when you want.
Excel also has a comment function.
Right click on any cell and choose "Insert Comment"
Not sure about the limits on how many characters can be entered in comments.

Wanting to use cells as places to store large amounts of text even if you can extend text down into below cells I think you will find is a difficult task.

 

[Taul]

Sorry, I missed your post #5 about OneNote

 

[miketurn]

@Taul
Thank You for the response, no problem.
Yeah I am also using an older version of Office that did not include OneNote.
Appears to be a really cool product, main thing that is odd to me about it is that they did not include the ability to support macros, which after over ten years of development is kind of crazy. The only way to get macros to work is you have to purchase a third party product that offers their own language to create a form of macro.
Not sure how much macro scripting would be needed in a program like this, but would be nice if it was included, regardless, especially from a company like Microsoft, that supports macros in all their other Office products.
Anyways, thanks again.

@My Aswer Is This
Probably wouldn't use comments, not really what I would be looking for.
I finally found out how to use them a bit, and I was messing around with textboxes.
Thank You again for mentioning this, it is a nice thing to be aware of but as I mention, I feel it eliminates some things of Excel that I use when typing notes.
Such as.... Use of macros, the ability to be able to move lines of text (cell rows) around, add rows in between exist lines, etc.

I am still getting used to messing around with them but it just feels that textboxes, I don't know, they just feel a little clunky, like a separate entity placed on top of Excel. I will need to use them more to see, but just not sure yet. Cool though how they don't mess with row height (obviously because they are sitting on top of them).



If anyone is interested in updating the macro that was provided by Tonyyy, I would love to see if this could be a possible solution.
Really will depend on how smooth it functions.
Does anyone else receive an error when trying it?

Thanks again for the responses and to anyone who has read this thread.