Comment Boxes

R

Rob Fenn

I have a spreadsheet I use at work to monitor incoming stock, it has two
spreadsheets "Inbound" and "Order No".

Inbound shows in cells :E6 to AE729 the quantity that is arriving by product
and by week
Order No shows in the same cells the order no of the quantity that is
arriving

To make it far easier to read I would like for each cell in "Inbound" where
the quantity is greater than 0 a comment box to be inserted automatically
with the value from the same cell in the "Order No" sheet

For example if INBOUND E15 is greater than 0 then a comment box is inserted
stating the value in ORDER NO E15

Also though I need the comment boxes removed if the cell equals zero at a
later date.

I hope this is clear and am greatful for all help

TIA
 
R

Ron de Bruin

Try this in the sheetmodule of "Inbound"

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("E6:AE729"), Target) Is Nothing Then
With Target
On Error Resume Next
.Comment.Delete
If .Value > 0 Then
.AddComment Text:=Sheets("Order No") _
.Range(Target.Address).Text
End If
On Error GoTo 0
End With
End If
End Sub


More info about comments you find here

David McRitchie
http://www.mvps.org/dmcritchie/excel/ccomment.htm

Or Debra's site
http://www.contextures.com/xlcomments03.html
 
D

Dave Peterson

You didn't like Tuesday's suggestions?

Rob said:
I have a spreadsheet I use at work to monitor incoming stock, it has two
spreadsheets "Inbound" and "Order No".

Inbound shows in cells :E6 to AE729 the quantity that is arriving by product
and by week
Order No shows in the same cells the order no of the quantity that is
arriving

To make it far easier to read I would like for each cell in "Inbound" where
the quantity is greater than 0 a comment box to be inserted automatically
with the value from the same cell in the "Order No" sheet

For example if INBOUND E15 is greater than 0 then a comment box is inserted
stating the value in ORDER NO E15

Also though I need the comment boxes removed if the cell equals zero at a
later date.

I hope this is clear and am greatful for all help

TIA
Click to expand...
 
R

Rob Fenn

Dave, Thankyou for your suggestions, for some reason I did not notice these
when I checked yesterday so reposted following Max's comments, I am
greatfull for the assistance from Ron and yourselves.

Thanks again
 
Top