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

I have quite lengthy texts I want to add as comments in my Excel workbook, but these comments become very difficult to read because of all the text in them (I have to scroll the page and the comments are not displayed instantly but neighbouring comments overlap sometimes).

And neighbouring text comments I have added, they stay there even when I have closed the comment.
LVL 1
hermesalphaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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.
Martin LissOlder than dirtCommented:
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.
Comments
hermesalphaAuthor Commented:
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.
OWASP: Avoiding Hacker Tricks

Learn to build secure applications from the mindset of the hacker and avoid being exploited.

Martin LissOlder than dirtCommented:
Is there any possibility that you could attach your workbook?
hermesalphaAuthor Commented:
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.
Martin LissOlder than dirtCommented:
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.
Martin LissOlder than dirtCommented:
Maybe this Contexture's article will help.
hermesalphaAuthor Commented:
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.
Martin LissOlder than dirtCommented:
I'm sorry but I'm out of ideas.
hermesalphaAuthor Commented:
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.
Martin LissOlder than dirtCommented:
Try this. You can change or replace the blue callout any way you want.
28713182.xlsm
hermesalphaAuthor Commented:
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).
Martin LissOlder than dirtCommented:
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
hermesalphaAuthor Commented:
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?
Martin LissOlder than dirtCommented:
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.
Martin LissOlder than dirtCommented:
BTW if there are blank cells anywhere in that range (even B99), the userform will show up with a blank label. Is that a problem? If it is then use this code. this will also work if some time in the future you want it to work for cells in column B after B99.

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

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
hermesalphaAuthor Commented:
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.
Martin LissOlder than dirtCommented:
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.
hermesalphaAuthor Commented:
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
hermesalphaAuthor Commented:
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?
Martin LissOlder than dirtCommented:
Yes. Either send it to be using an EE Massage or regular email. You'll find my email address in my profile.
hermesalphaAuthor Commented:
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.
hermesalphaAuthor Commented:
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).
Martin LissOlder than dirtCommented:
Do you still need help with this?
hermesalphaAuthor Commented:
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.
Martin LissOlder than dirtCommented:
Can you attach your current workbook?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.