Is there a better way to use the comments function in Excel 2007 than I do now?

If I understood your requirement correctly, you may consider enabling Wrap Text and increase the row height and and column width accordingly to fit within the page.

And neighbouring text comments I have added, they stay there even when I have closed the comment.

Do your options look like this? If so you should only see one comment at a time.

Yes, my options look like yours on the picture. I tried displaying indicators also, but it became more of a mess so I changed back again.

One complication is that I have freezed the panes for about almost half the page. So that makes it even more difficult to display lengthy comments properly. So the only way now is to open these comments for editing only for reading them.

Is there any possibility that you could attach your workbook?

Here is a screenshot of my Excel workbook:

http://screencast.com/t/WsLz1hqQF7 

You can see that only parts of the comment is displayed (the top row, in yellow, are locked for scrolling).

What I would prefer is that the comments are superimposed on the surrounding text when hovering with the mouse pointer over them.

I don't think there's any way to do that. The only other idea I have is to make the cell that now has the comment into a hyperlink which directs the user to a sheet with the explanatory text. The should could have a "Back" button to go back to where you were.

Maybe this Contexture's article will help.

But I have seen this feature in an Excel earlier: I could just hover with the mouse pointer over the cell and the text inside it displayed in full, flowing over surrounding text.

I'm sorry but I'm out of ideas.

What about a small click button that, when clicked on, opens up a pop-up window where all the text is displayed. Would that be possible? And then a X for closing the pop-up window.

Try this. You can change or replace the blue callout any way you want.
28713182.xlsm

Thanks Martin, this would definitely work, perfect with the font size, grey background and close button. But I'd like to remove the callout completely and have nothing, only need to click on the cell/in the cell area and the pop-up window opens (sorry, I changed my initial request that I wanted a small click button, but when I tried your suggestion now I see that the best would be no click button at all, just need to click on the cell to open the pop-up window).

Here's an update. Select G7. In the code for the sheet you'll find a reference to that range. You can change it to any range or ranges you want.
28713182a.xlsm

Ok, this is just perfect, thanks!

I tried now entering the code, but I don't remember the steps to get it saved and automatically run for a certain sheet:

1. In the VBA editor in Excel, should I first select "Worksheet" and "SelectionChange" and thereafter paste your code?:

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Not Intersect(Target, Range("B5..B99")) Is Nothing Then
        UserForm1.Label1.Caption = Target.Value
        UserForm1.Show vbModeless
    End If
End Sub

(In your code above, is it correct to enter the range in the format of "B5..B99"?)

2. Should I then choose Run Sub/UserForm? If I do, I get a new small pop-up window called "Macros":

"ThisWorkbook.copypasterange"

Should I choose to run here?

1. Yes. Assuming that there's not already code there just select the empty sub that's there and paste my code.
2. The range should be "B5:B99" and not "B5..B99". In other words replace the two periods with one colon. After that you don't have to "run" anything. Rather when you select any cell on the worksheet, the Worksheet_SelectionChange sub will automatically be run and if the cell address is in the range "B5:B99" the userform will be shown.

I tried your last code now, but after having returned to the Excel sheet from VBA Editor and selected a cell the VBA Editor automatically opens again and I receive this error message:

"Compile error: Variable not defined".

And "UserForm1" is highlighted in blue.

1.    Open the workbook that I attached above.
2.    Go to Visual Basic and in the Project-VBA Project window select Forms->UserForm1
3.    Right click on it and choose Export File saving it anywhere.
4.    Then in your project go to Visual Basic, select the File->Import menu item and import the file that you saved.


If you still have troubles after that please attach your workbook.

I received this error message now:

"Compile error. Sub or function not defined."

And the "Call RemoveTitleBar (Me)" (just above "End Sub") is highlighted in blue.

But do you think I have pasted the code incorrectly? Should "Option Explicit" be there for your second modified code? This is the code exactly as I pasted it first time:

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Not Intersect(Target, Range("B5:B" & Range("B1048576").End(xlUp).Row)) Is Nothing Then
        If Target <> "" Then
           UserForm1.Label1.Caption = Target.Value
           UserForm1.Show vbModeless
        End If
    End If
   
End Sub

I forgot to say, the above error message was displayed after I had followed your instructions about importing UserForm1.

I can't upload my Excel here, because I don't want to disclose all the contents in it to anyone. Can I send it in private message to you instead?

Yes. Either send it to be using an EE Massage or regular email. You'll find my email address in my profile.

There is one more complication: I have freezed the column just beside where the comment is displayed so that also affects the display of the comment.

Ok, great, almost there now. No error message anymore after your new module code. But what happens now is this:

I select a cell with a comment in and up pops a quite big grey popup window with small text in it (perfect, just like I want it). But it doesn't display the comment text (which is displayed as before). So it's this new quite big grey popup window plus the old comment in yellow background displayed at the same time.

The only text which is displayed in the new grey popup window is the cell's contents (for example "Arvika", the name of a harness racing track).

Do you still need help with this?

Is it possible to make the popup window close automatically when I move (by using the mouse and roll to a new cell) from the selected cell to a new cell? That way it would be easier to scroll and immediately be able to compare the different tracks and not need to click on the popup window to close it.

Can you attach your current workbook?