I am very new to VBA and my boss is having work on a spreadsheet that dynamically updates the comment box with a value from a designated cell.
Here is what I am using for the first target address of C39 to be displayed in a text box in C5. Problem is I have to do it for 140 cells. I don’t know the best way to write this code. My next target is C40 with comment cell of C6 and continues on.
Private Sub Worksheet_Change(ByVal Target As Range)
mystr = Format(5459.4, "##,##0.00")
If Target.Address = "$C$39" Then
On Error Resume Next
ActiveSheet.Range("C5").Comment.Text Text:="MTD Volume Var " & Chr(10) & ActiveSheet.Range("C67").Value
If Err.Number = 91 Then ActiveSheet.Range("C5").AddComment Text:="Cell value is " & Chr(10) & ActiveSheet.Range("C5").Value
Private Sub Worksheet_Calculate()
September 24, 2008 9:43 PM
September 30, 2008 1:22 AM