Comments, can they be programmed?

[ed ayers315]

I am looking for a way when you pass the cursor over a cell it Clouds what the cell contents reflect with out having to go other places.

Example:

C2 = 12 12 represent a code for "not produced"

there are other information around that cell so I can not just place a formula next to that cell to show what the code would represent.

Any help is greatly appreciated.

[mikerickson]

I'm not exactly sure what you want.
The attached workbook has a VLookup table of Codes and Meanings in A1:B4.

If you enter a Code in cell of column E, this Worksheet_Change event will add a comment to that cell containing the meaning of the Code entered.

Please Login or Register  to view this content.

[ed ayers315]

Mike,

Fantastic, works like a charm. I only wish I could put the code language in my head; some I can figure out but so far I have not found literature on what the code really stands for.

Your great and thanks!!

[ed ayers315]

Hi Mike,

I can not figure out how to expand the code to include more code and meaning ranges. I up the range from a1:b14 and typed in new codes and meaning in those ranges but the comments stop updating past the "3" code.

[romperstomper]

Try changing this line:

Please Login or Register  to view this content.

to this:

Please Login or Register  to view this content.

and see if that helps.

[ed ayers315]

Hello RomperStomper,

Thanks for the reply but it did not change, same result, stops at the 3rd code. What is funny is that it works down through with codes 1-3 and anything after that it just using the 3rd meaning not matter how far down you go.

[romperstomper]

Could you post a workbook showing that? I don't see how that's possible at first glance.

[ed ayers315]

Here you go

[mikerickson]

Probably the quickest way to convert the code would be to use a dynamic named Range.

Name: CodeRange
RefersTo: =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),2)

and then use change the line to

Please Login or Register  to view this content.

This assumes that there is nothing below the codes in Sheet1! A:A and that there are no blank rows in between data rows.

[mikerickson]

I just thought of something else. For this to work there must be an existing entry in the Code/Meaning list.

If you have an existing list of codes (say in D1:D100) that you want comments added to:
1) make a complete list of codes/meanings
2) alter the Change event code to reflect the range of the C/M list.
3) run this from a normal module.

Please Login or Register  to view this content.

That should put comments in D1:D100.

I've also been thinking...if the code/meaning list changes (particularly if the meaning of a code changes) a different approach would be better. (I have a notion involving Validation.) Is that the case?

If the Code/Meaning list is not going to change, this is a good approach.

[ed ayers315]

Thanks

The codes stay the same in this case but I have things turning in my head now about the possiblity of the "Validation" you write about.

[mikerickson]

The basic idea is that, using Validation, you can have a message pop-up when you select a cell. By (automaticaly) copying the Validation from a MasterList Cell to the cells of interest, one can change all the validation messages just by altering the Validation of the Master Cell, while using the "Change all the same validation" option.

The down side is that one has to actualy select the cell to see the message (Comments only require a hover), plus one has to dismiss the pop-up message by pressing Enter.

With a static Code/Meaning list, Comments seems the way to go.

When I get off work, I'll look into the sheet you posted in #8 to see what I can see.

[romperstomper]

Your workbook works fine for me making the change I suggested. Are you sure you changed the line exactly as I posted it?

[shg]

You can do this with a UDF:

Please Login or Register  to view this content.

Then in F2 and copy down,

=SetComment(E2, VLOOKUP(E2, $A$2:$B$8, 2))

[mikerickson]

Altering the Change event in the posted workbook to

Please Login or Register  to view this content.

fixed the problem.

I added a named Range CMList refersto: Sheet1!$A$1:$B$8 and changed that line to use the Named Range instead.
If the list needs to be expanded, the Name will adjust if blank rows are inserted into the middle of the list.

I also altered the code to acomidate more than one cell being added to column C. (e.g. copy/pasting a range)

I also added a section that, if the CMList is altered, the comments will be updated with the new meanings.

Please Login or Register  to view this content.

[mikerickson]

Shg, that's a nice UDF.

(Who says UDF's can't "change the environment"? )

[ed ayers315]

Thanks folks,

I'll mark this as solved