Link cell wiht comment-pop up message

  • Thread starter Thread starter Bill
  • Start date Start date
B

Bill

Dear all,

I have an infomration to a cell and i would like to link
this cell with a comment. Ie. the comment must show the
sentense from the cell.
Do you have any idea how can i do this?
Or there is any way that i can make this to flashing or to
look like as a comment or a pop up message, even form the
validation data?
There is anyway that i can add a message each time that
the file before it close to show to the user a message
that links form the cell descibed it above?

I.e. "Don't forget to reconcile sheet 5, 6,..."

Thanks in advance Bill
 
You can retrieve the text from a comment with a userdefined function like:

Option Explicit
Function GetComment(FCell As Range) As Variant
Application.Volatile

Set FCell = FCell(1)

If FCell.Comment Is Nothing Then
GetComment = ""
Else
GetComment = FCell.Comment.Text
End If

End Function

Then you can use it like any other function:

=getcomment(a1)

But be aware that the function won't evaluate when you just change the comment.
It'll be correct when excel recalculates. (Hit F9 to force a recalc.)

And you could have some code in the Auto_close procedure (in a general module)
or in the Workbook_BeforeClose (in the ThisWorkbook module).

sub auto_close()
msgbox "Please remember to reconcile your sheets"
end sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
Unfortunately my knowledges around the visual basic is not
so advance and i can't make it to work
I like the idea of the auto message at the end at the
closing of the workbook.
There is anyway to make this message to work on specific
numbers?
Like too have numbers at the first sheet named Cover
and each time the user put the number 3 or 6 or 9 or 12
to give him thsi message?

TA
 
If you're saying that you want to check a specific cell (I used A1) on that
Cover worksheet and if it contains 3, 6, 9, 12, then issue a message, this code
might work ok:

This code goes in the module behind the ThisWorkbook object.

Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim Resp As Long

Select Case Worksheets("Cover").Range("a1").Value
Case Is = 3, 6, 9, 12
Resp = MsgBox(Prompt:="Did you reconcile your workbook?", _
Buttons:=vbYesNo, Title:="Reconcile?")
If Resp = vbNo Then
Cancel = True 'don't let the close happen
Else
'do nothing special
End If
Case Else
'no message?
End Select
End Sub
 
Back
Top