PDA

View Full Version : Solved: Excel: comment pulled from cell text?



patrickm96
02-14-2008, 01:20 PM
Is there a VBA code for Excel that can pull text from another worksheet to populate all my comments?

The cells' comment would match the same location. For example the comment on Sheet1 (E5) would be taken from the cell on Sheet2 (E5) -


This way I can have a separate page just to edit comments for all the cells on Sheet1.

If this can be done, can then too the comments be made to display by clicking on and off, and not displayed by hovering?

Thanks in advance for any help.

mdmackillop
02-14-2008, 02:40 PM
Sub UpdateComments()
Dim cel As Range
For Each cel In Sheets(2).UsedRange.SpecialCells(xlCellTypeConstants)
With Sheets(1).Range(cel.Address)
.ClearComments
.AddComment Text:=cel.Text
End With
Next
End Sub

Sub ShowComments()
Dim c As Comment
For Each c In Sheets(1).Comments
c.Visible = Not c.Visible
Next
End Sub

patrickm96
02-14-2008, 08:34 PM
This works very nicely! Thank you!

Is it possible to modify the code so that I can click on a single cell in Sheet1 and have it display a individual cell's comment and then click it off?

This way I'd like to be able to click and display two or three comments and compare notes - and then click off the comments I don't need displayed.

I believe I will end up with 60 columns and up to 70 rows as I build this spreadsheet...

I've uploaded the Excel file that I'm building for our group.

Bob Phillips
02-15-2008, 03:46 AM
Sub UpdateComments()
Dim cell As Range
For Each cell In Sheets(2).UsedRange.SpecialCells(xlCellTypeConstants)
With Sheets(1).Range(cell.Address).Validation
.Delete
.Add Type:=xlValidateInputOnly, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween
.InputMessage = cell.Text
End With
Next
End Sub

mdmackillop
02-15-2008, 10:41 AM
Hi Bob
Never come across that before. Is it new?

Bob Phillips
02-15-2008, 11:50 AM
It's only simple Data Validation Malcolm, allowing any input. It provides what I understood the OP to want, a comment that pops-up by selecting a cell rather than hovering.

I use it quite a bit, I am no fan of cell comments.

patrickm96
02-20-2008, 06:55 AM
Thank you for your help! This will help our group out greatly in our monthly meetings.
Best Regards,

Pat M