We have 2 add-in SW vendors which return values with comments to a cell. I
want to check the vendor ID in cell comment to determine if I need to do a
calculation on value. Is that possible via a simple formula or is a macro or
something more complicated required? Is it even possible?

We are using MS Excel 2003.

Hi

You would need to refer to the cell in VBA

EG....

if Range("A1").comment.text = "Hello" Then
MsgBox "Say Hello"
End If

I don't think you can refer to a comment in a formula without VB

Steve

Thanks, I've been reviewing the "Programming" section of Excel. It looks like
I'll have to teach myself VBA!


"SIR Knight" wrote:

Hi

You would need to refer to the cell in VBA

EG....

if Range("A1").comment.text = "Hello" Then
MsgBox "Say Hello"
End If

I don't think you can refer to a comment in a formula without VB

Steve

One way to learn how VBA refers to cell comments is to record a macro in
which you enter a comment in a cell, and then look at the VBA code that Excel
automatically returns.

That doesn't mean you'll have the precise syntax for what you're looking to
do, but it means you will have some idea of what code you're looking for.

Hope that helps/

"da@DRI" wrote:

Thanks, I've been reviewing the "Programming" section of Excel. It looks like
I'll have to teach myself VBA!


"SIR Knight" wrote:

Hi

You would need to refer to the cell in VBA

EG....

if Range("A1").comment.text = "Hello" Then
MsgBox "Say Hello"
End If

I don't think you can refer to a comment in a formula without VB

Steve

I don't know of any way to reference the .Comment in another cell with a
formula, so either a 'pure' VBA macro solution is needed, or you could look
at this hybrid solution.

I've used the worksheet _SelectionChange event to try to determine if you've
clicked on one of your cells that has the comment with information in it. If
that has happened (if the cell has a comment), the text content of the
comment is placed into another cell on the same row.

Then back in the place where you may or may not want to do your math, you
can now use an IF() statement to examine the cell where the text has
magically appeared to see if you want to do the math or not.

This code presumes the cells "of interest" are in column A, and that we can
use column H to display the text of a comment.

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim iSect As Range
Dim myVar As Variant

'did user choose cell in column A?
Set iSect = Application.Intersect(Range(Target.Address), Range("A:A"))
If iSect Is Nothing Then
Exit Sub ' no, do nothing
End If
'user selected cell in column of interest (A)
'check if cell has comment
Application.EnableEvents = False
If Range(Target.Address).Comment Is Nothing Then
'no comment, get out
Application.EnableEvents = True
Exit Sub
End If
'if any error occurs from here on, just ignore it without action
On Error GoTo ExitChange
'we will set value of another cell to contain contents of
'the comment, assume on the same row, 7 columns to right (column I)
myVar = Trim(Target.Comment.Text) ' remove leading/trailing white space
'if someone hit [Enter] at end, remove that also
Do While InStr(myVar, Chr$(10))
myVar = Replace(myVar, Chr$(10), "")
Loop
Target.Offset(0, 7) = myVar
iSect.Select
ExitChange:
If Err < 0 Then
Err.Clear
On Error GoTo 0
End If
Application.EnableEvents = True

End Sub

So now, you could enter a formula like this in a cell to actually do the
math, or not, based on contents of the comment - this was from B3 on my test
sheet:
=IF(H3="a041b5","Do The Math",0)

You can upload an Excel 2003 file with all of this in it from:
http://www.jlathamsite.com/uploads/for_da_at_dri.xls
and if you need help figuring out how to get into the area where you place
code into a Worksheet's event, this page should help:
http://www.jlathamsite.com/Teach/WorksheetCode.htm


"da@DRI" wrote:

We have 2 add-in SW vendors which return values with comments to a cell. I
want to check the vendor ID in cell comment to determine if I need to do a
calculation on value. Is that possible via a simple formula or is a macro or
something more complicated required? Is it even possible?

We are using MS Excel 2003.