Sleeper: Comment position

[lior03]

hello
suppose i have a column of data in column b.
the last cell in that column contain a comment with the formula i used in the column.
now i added another column, and column b becomes column c.
my question- is there a way to "refresh" the comment to include the new range?
what about an event -sheet selection change?
thanks

[malik641]

What does the comment say? What's the formula?

[lior03]

hello
attached is a file- "draft.xls"
it has some columns and sum formula in the bottom.
column g conatin a comment - the formula i used.
now suppose i add another column between columns d & e. the comment's
content should change-what was column g is now column h.
is there a code for such change?
maybe an event - sheet change?
thanks

[lior03]

hello
is this the solution.
it did not work on my computer.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim t As Range
On Error Resume Next
For Each t In Target
If Left(Target.Comment.Text, 7) = "Formula" Then
If t.HasFormula Then
t.Comment.Text Text:="Formula" & Target.Formula
Else
t.ClearComments
End If
End If
Next t
End Sub

[malik641]

This is the best I can come up with....I keep getting problems with it though...
Be careful with this because when you delete a value in a cell excel crashes...

I don't mean to give you a faulty code, but someone had to responde! AND maybe you (or anybody else) can see what's the matter with my code.

Take a look:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cmt As Comment
Dim keyword As Variant
Dim Ctr As Long
Dim loc As Long
Dim locComp As Long
Dim str As String
Dim CellFormula As String
Dim commFormula As String
keyword = Array("Formula")
For Each cmt In ActiveSheet.Comments
If cmt Is Nothing Then
'Do nothing
Else
For Ctr = LBound(keyword) To UBound(keyword)
str = cmt.Text
Do
loc = InStr(1, str, keyword(Ctr), vbTextCompare)
If loc > 0 Then
If cmt.Parent.HasFormula = True Then
CellFormula = cmt.Parent.Formula
commFormula = Mid(cmt.Text, 8, 15)
locComp = InStr(1, CellFormula, commFormula, vbTextCompare)
If locComp = 0 Then
cmt.Delete
cmt.Parent.AddComment Text:="Formula" & CellFormula
Else
'Do nothing
End If
Else
End If
Else
Exit Do
End If
Loop
Next Ctr
End If
Next cmt
End Sub

If you select the column that has the comment and insert a new column OR delete a previous column, it will ask you to debug. If you press End, you'll see the formula in the comment was replaced. But I can't get passed the errors!!!!

See if you can do anything with this. I hope I shed some light on your problem.

[Zack Barresse]

Hello moshe, [uvba]some info..[/uvba] I took the liberty to adjust your post, hope you don't mind.

[malik641]

Here ya go!!!

Check it out!!

[malik641]

Didn't realize what would happen when you have more than one comment with a formula in it....Here's a newer version of the macro. Works pretty well!!!

[lior03]

hello
your code is great ,how do i turn it to an entire workbook event.

[malik641]

Just copy the code except for the 'Private Sub Worksheet_Change' and 'End Sub'

Place it in the "This workbook" module
Place it between these lines:

Private Sub Workbook_Open() 
 
End Sub

[malik641]

your code is great

Why thank ya!