Display cell comment in text box

Hi,

I've knocked up a calendar in Excel 2010 and i have buttons that fill a cell with a given colour for different tasks, as well as this ive made it such that a message box appears so that i can add a cell comment for that date.

I'd like to be able to highlight a cell that has a comment and have that comment display in a text box / other cell to make for easier reading, is this doable?

I should add im using Excel 2010.

Thanks in advance!

not sure if you can do this with cell linking or pointing, but you can obviously do it with code. see these:

https://docs.microsoft.com/en-us/off.../excel.comment

https://docs.microsoft.com/en-us/off....range.comment

Thanks, although im trying to get the text box to display the comment for whichever cell is highlighted, rather than for a set cell reference. I've played around with lots of variations of 'activecell.value' etc. but cant seem to find the right syntax

post the code you already tried.

So i've tried:

TextBox1.Value = ActiveCell.NoteText
TextBox1.Value = ActiveCell.Comment
TextBox1.Text = ActiveCell.NoteText
TextBox1.Text = ActiveCell.Comment

Plus probably a few others ive since deleted, am i completely barking up the wrong tree?

I've also tried using the following to get the comment text into a cell and then linking the text box that cell:


Function ReturnComment(cell As Range)

CellComment = ActiveCell.Comment.Text

End Function

But no joy, I keep getting various run time errors, also thanks again for the help!

where is the box? sitting on top of the sheet, or on a userform? where are you putting this statement?

Please Login or Register  to view this content.

in a code routing?

Originally Posted by vba_php

where is the box? sitting on top of the sheet, or on a userform? where are you putting this statement?

Please Login or Register  to view this content.

in a code routing?

Its on top of the sheet, the code is just in a subroutine in VBA, ive tried attaching to the text box but didnt work, dont think thats right anyway? I tried using it in a workbook_open sub but didnt work either. I also tried creating a button to test it but nope!

what TYPE of control is it? activeX, or Form? post a screenshot of it sitting on top of the worksheet

regarding the RIGHT way to do what you're doing, yeah, you're going through a lot of wasted time here. there must be a better way

So i've cracked it, although probably not the most elegant way. I've created a button (using a text box) and attached the macro to that, code reads as follows:


Sub TextBox2_Click()

Dim CellComment As String

CellComment = ActiveCell.Comment.Text

Range("AQ15").Value = CellComment

End Sub


I've then just linked the display text box to cell AQ15 in the formula bar and set the text in AQ15 to white so you wouldnt know the difference. Thanks for the help anyway!