Automatic Comments Boxes

[Rob Fenn]

I have a spreadsheet with two tabs that I use for work, One tab is inbound
quantities and the other tab is the order no of that quantity.

What I would like to do is where the quantity in a cell in inbound ie D8 is
greater than 0 then a comment is automatically inserted quoting the value in
D8 in the other tab.

If this is possible it would be a great help.

TIA

 

[Max]

Assuming inbound quantities are in Sheet1,

Put in D8 in the other tab / sheet (for order no.)

: =IF(AND(ISNUMBER(Sheet1!D8),Sheet1!D8>0),Sheet1!D8,"")

 

[Rob Fenn]

Sorry I think I was unclear

If I have a value in D8 in my inbound tab I want to attach a yellow comment
box to this cell and insert in the comment box the value in D8 on the order
no sheet.

TIA

 

[Max]

Perhaps try the sub below (which goes into workbook module) ..

Right-click on the Excel icon just to the left of "File" > Choose "View
Code"
This will bring you direct into the workbook module

Copy and paste the sub below (everything within the dotted lines)
into the whitespace on the right
(clear the defaults appearing in the whitespace first)

----------begin vba------
Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Target As Excel.Range)

Set Alert = Sheets("Sheet1").Range("D8")
If Application.WorksheetFunction.IsNumber(Alert) = False _
Then Exit Sub
If Alert > 0 Then
Sheets("Sheet2").Select
With Range("D8")
.ClearComments
.AddComment
.Comment.Visible = False
.Comment.Text Text:="Quantity: " & Alert
End With
End If
End Sub
----------end vba----------

Press Alt+Q to get back to Excel

Test by entering a figure > 0 in D8 of Sheet1
(Sheet1 is the assumed tab for "inbound quantities")

A comment box will be auto-inserted in D8 of Sheet2
(Sheet2 is the assumed tab for "order no.")

with the phrase:
"Quantity: <Figure entered in D8 of Sheet1>"

 

[Max]

Just a little clarification ..

The description "workbook module"
should rightly read as "ThisWorkbook module"
(.. less ambiguous)

 

[Rob Fenn]

This works great however I need it too work across a huge range of date
E6:AE729 pulling the info each time from the respective cell in the other
sheet.

Also I need the comment to be removed if the cell is made blank later.

TIA

 

[Rob Fenn]

This works great however I need it too work across a huge range of date
E6:AE729 pulling the info each time from the respective cell in the other
sheet.

Also I need the comment to be removed if the cell is made blank later.

TIA

 

[Max]

Pleasure' Rob. Got you the first time <g>

But think I've just about reached my (in)competence level
(still learning vba ..)

Hang around this thread a while ..

Perhaps others more experienced in vba
(and there are many) would step-in to help extend/re-work
the sub to meet your revised specs below

Otherwise, you could try a post in .excel.programming

Good luck !