Some questions on Comments....

slyde

New Member
Joined
Aug 27, 2006
Messages
20
Hi guys,

I am trying to add some comments to some cells of my Excel sheet. Some questions...

1) I already have much of this information that i want in the comments in other cells of another sheet of my excel file. Is there a way i can make a comment linked to another cell, to show that data. Or, will i be forced to just copy and paste the info into each comment?

2) How can i set up my comment so that it only shows when i mouse over the cell that contains it, and dissapear when the user moves the mouse off it, without the user having to click the cell or unclick the comment once it appears...
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
Which comments are you using their are four different types, the code for each is different!


For a standard in cell comment, from the Right-Click add Comment option:

If you build a list of possible comments on Sheet3 then code like this will add a comment to any active cell on any sheet, from the text in your master list of comments!



Sub myLoadComTest()
'Standard module code, like: Module1!
'Load Comment: This is a Test!


With ActiveCell
On Error Resume Next

.Comment.Delete

On Error GoTo 0
'Get comment text from list of possible comments on sheet3!
.AddComment Text:=Sheets("Sheet3").Range("A3").Value

'Comment format options!
'.Interior.Color = vbYellow
'.Comment.Shape.Line.ForeColor.SchemeColor = 48

End With

End Sub
 

slyde

New Member
Joined
Aug 27, 2006
Messages
20

ADVERTISEMENT

.

thanks joe.

Please keep in mind that i am a total excell newbie.. i didnt even know there were different kinds of comments.

Regarding the 2nd part of my question, i think i need to clarify...

i dont want to link a comment to an existing COMMENT on another page, but an existing cell.

in other words:

I would like a comment that i have for cell A1 in worksheet 1, to actually take the text from CELL B20 in worksheet 2.

Any ideas?
 

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
That is what my code does!
It loads a comment into the active cell using text that is in another cell!
 

slyde

New Member
Joined
Aug 27, 2006
Messages
20

ADVERTISEMENT

.

oh ok, great!

Can i trouble you to give me an idea how i would insert this code? As a excell newbie, all i really know how to do is from the screen level. Ive never put code in a spreadsheet.....
 

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
Hit Alt+F11

If the Project manager box is not up, hit: Ctrl+R

From the Editor Toolbar: Insert - Module
Then paste a copy of the code I posted.

You will need to change the code to use the Sheet and Range that holds your text for the comment!

When done editing the code, Click the top-most close "x" at the upper Right.

To run the code, select the cell that gets the comment. Then from the Excel Toolbar: Tools - Macro - Macros...

Then select the macro name and hit run.

If you select "Options" on the macro utility you can assign a Hot-Key to the macro so you will only need to hit the Key-Combo you assigned, to run the code!

Like: Ctrl+z
 

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
If you have a list of possible comments in a table of cells on Sheet3 and Column "A" then this code will let you pick which comment to use from the list of possible comment texts. It then adds that text to a comment it builds in the cell that was active when you ran the code!


Sub myLoadComFromLst()
'Standard module code, like: Module1!
Dim myCell$, mySheet$
Dim myCom As Range

mySheet = ActiveSheet.Name
myCell = ActiveCell.Address(RowAbsolute:=False, ColumnAbsolute:=False)

'This is the list of comment text location, Column "A" one "Comment text/Row!"
Sheets("Sheet3").Select
Sheets("Sheet3").Range("A1").Select

'Get comment text to use!
Set myCom = Application.InputBox(prompt:="Select your comment text.", Type:=8)

On Error Resume Next
'If prior comment exists, erase it first!
Sheets(mySheet).Range(myCell).Comment.Delete
On Error GoTo 0

'Make comment!
Sheets(mySheet).Select
Sheets(mySheet).Range(myCell).AddComment Text:=myCom.Value

'Comment format options!
'Sheets(mySheet).Range(myCell).Interior.Color = vbYellow
'Sheets(mySheet).Range(myCell).Comment.Shape.Line.ForeColor.SchemeColor = 48


Sheets(mySheet).Range(myCell).Select
End Sub
 

slyde

New Member
Joined
Aug 27, 2006
Messages
20
.

i'm a bit confused. So i would have to have the users hit a button each time the worksheet loads to get this to run? Ideally what i am shooting for is to have users just see the comment as a popup when they mousover a cell.

Also, i should have mentioned this before, but i have actually about 200 cells that i want to link thru comments to about 200 other cells, not just 1 cell.

Is your idea still feasible?
 

Forum statistics

Threads
1,136,709
Messages
5,677,324
Members
419,688
Latest member
sarahmichelle

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top