VBA - Passing Cell Contents Into Comment

**ajocius** · 07-29-2005, 08:30 AM

Group,
Is it possible to pass the contents of a cell into a comment in another cell? If so, how can I do this? Thank you for your assistance.

Tony

**dominicb** · 07-29-2005, 08:46 AM

Good afternoon Ajocios

You can only do this via VBA - a macro. The code below will copy the contents of cell A1 into a comment contained in the currently active cell.

Sub Macro1()
a = Range("A1").Value
usrcell = ActiveWindow.RangeSelection.Address
With Range(usrcell)
.AddComment
.Comment.Text Text:=a
End With
End Sub

Post back if you want any further help in implementing this.

HTH

DominicB

**ajocius** · 07-30-2005, 02:02 AM

Is usrcell and 'a' both String types? I'm having a problem with .AddComment. I get an error 1004.

Tony

**dominicb** · 07-30-2005, 02:50 AM

Hi Tony

What version of Excel are you using? I have just tested ths again using Excel 2000 and 2003 and it works fine.

If you are using XL 97 this could be why it doesn't work, though I wasn't aware that this command wouldn't work and cannot test on 97 because I no longer have access to it.

BTW, a is a variable that holds the contents of cell A1, and UsrCell the variable that holds the address of the currently active cell.

Interested to know.

DominicB

Norman Jones · 07-30-2005, 03:05 AM

Hi Tony,

Try something like:

Public Sub Tester02()
Dim rng1 As Range, rng2 As Range
Dim sStr As String
Dim sh As Worksheet

Set sh = ActiveSheet '<<======= CHANGE
Set rng1 = sh.Range("A1") '<<======= CHANGE
Set rng2 = sh.Range("B1") '<<======= CHANGE

sStr = rng1.Text

With rng2
If rng2.Comment Is Nothing Then
.AddComment
.Comment.Text Text:=sStr
End If
End With

End Sub

---
Regards,
Norman

"ajocius" <[email protected]> wrote in
message news:[email protected]...
>
> Group,
> Is it possible to pass the contents of a cell into a comment in
> another cell? If so, how can I do this? Thank you for your
> assistance.
>
> Tony
>
>
> --
> ajocius
> ------------------------------------------------------------------------
> ajocius's Profile:
> http://www.excelforum.com/member.php...o&userid=17695
> View this thread: http://www.excelforum.com/showthread...hreadid=391261
>

Norman Jones · 07-30-2005, 03:05 AM

Hi Tony,

Try, instead:

Public Sub Tester02()
Dim rng1 As Range, rng2 As Range
Dim sStr As String
Dim sh As Worksheet

Set sh = ActiveSheet '<<======= CHANGE
Set rng1 = sh.Range("A1") '<<======= CHANGE
Set rng2 = sh.Range("B1") '<<======= CHANGE

sStr = rng1.Text

With rng2
If .Comment Is Nothing Then .AddComment
.Comment.Text Text:=sStr
End With

End Sub

---
Regards,
Norman

"Norman Jones" <[email protected]> wrote in message
news:e68w%[email protected]...
> Hi Tony,
>
> Try something like:
>
> Public Sub Tester02()
> Dim rng1 As Range, rng2 As Range
> Dim sStr As String
> Dim sh As Worksheet
>
> Set sh = ActiveSheet '<<======= CHANGE
> Set rng1 = sh.Range("A1") '<<======= CHANGE
> Set rng2 = sh.Range("B1") '<<======= CHANGE
>
> sStr = rng1.Text
>
> With rng2
> If rng2.Comment Is Nothing Then
> .AddComment
> .Comment.Text Text:=sStr
> End If
> End With
>
> End Sub
>
>
> ---
> Regards,
> Norman
>
>
>
> "ajocius" <[email protected]> wrote in
> message news:[email protected]...
>>
>> Group,
>> Is it possible to pass the contents of a cell into a comment in
>> another cell? If so, how can I do this? Thank you for your
>> assistance.
>>
>> Tony
>>
>>
>> --
>> ajocius
>> ------------------------------------------------------------------------
>> ajocius's Profile:
>> http://www.excelforum.com/member.php...o&userid=17695
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=391261
>>
>
>

Norman Jones · 07-30-2005, 04:05 AM

Hi Dominic,

> What version of Excel are you using? I have just tested ths again
> using Excel 2000 and 2003 and it works fine.

The sub failed for me unless I prepended the lines:

AddComment
Comment.Text Text:=a

with a . (dot).

---
Regards,
Norman

"dominicb" <[email protected]> wrote in
message news:[email protected]...
>
> Hi Tony
>
> What version of Excel are you using? I have just tested ths again
> using Excel 2000 and 2003 and it works fine.
>
> If you are using XL 97 this could be why it doesn't work, though I
> wasn't aware that this command wouldn't work and cannot test on 97
> because I no longer have access to it.
>
> BTW, a is a variable that holds the contents of cell A1, and UsrCell
> the variable that holds the address of the currently active cell.
>
> Interested to know.
>
> DominicB
>
>
> --
> dominicb
> ------------------------------------------------------------------------
> dominicb's Profile:
> http://www.excelforum.com/member.php...o&userid=18932
> View this thread: http://www.excelforum.com/showthread...hreadid=391261
>

**dominicb** · 07-30-2005, 05:21 AM

Norman

On my system I can see the dots in front of the AddComment and Comment.Text. - I still see them now in this copied quote. In fact when I tested this piece of code I copied it straight from my posting to the VBE within Excel 2000 and 2003 without a problem.

Regards

DominicB

Originally Posted by dominicb

Good afternoon Ajocios

You can only do this via VBA - a macro. The code below will copy the contents of cell A1 into a comment contained in the currently active cell.

Sub Macro1()
a = Range("A1").Value
usrcell = ActiveWindow.RangeSelection.Address
With Range(usrcell)
.AddComment
.Comment.Text Text:=a
End With
End Sub

Post back if you want any further help in implementing this.

HTH

DominicB

Norman Jones · 07-30-2005, 12:05 PM

Hi Dominic,

Checking the forum in which you made your post, I see that the prepended
dots were indeed included.

Using my newsreader to download messages from the MS public NGs, the dots do
not appear in your message.

My sincere apologies for doubting! .

---
Regards,
Norman

"dominicb" <[email protected]> wrote in
message news:[email protected]...
>
> Norman
>
> On my system I can see the dots in front of the AddComment and
> Comment.Text. - I still see them now in this copied quote. In fact
> when I tested this piece of code I copied it straight from my posting
> to the VBE within Excel 2000 and 2003 without a problem.
>
> Regards
>
> DominicB
>
> dominicb Wrote:
>> Good afternoon Ajocios
>>
>> You can only do this via VBA - a macro. The code below will copy the
>> contents of cell A1 into a comment contained in the currently active
>> cell.
>>
>> Sub Macro1()
>> a = Range("A1").Value
>> usrcell = ActiveWindow.RangeSelection.Address
>> With Range(usrcell)
>> .AddComment
>> .Comment.Text Text:=a
>> End With
>> End Sub
>>
>> Post back if you want any further help in implementing this.
>>
>> HTH
>>
>> DominicB
>
>
> --
> dominicb
> ------------------------------------------------------------------------
> dominicb's Profile:
> http://www.excelforum.com/member.php...o&userid=18932
> View this thread: http://www.excelforum.com/showthread...hreadid=391261
>

**dominicb** · 07-30-2005, 01:21 PM

Hi Norman

Ahhhhhhh, I see. Thanks for taking the trouble to reply - and to hunt down where my original posting came from. Appreciated.

DominicB

**ajocius** · 07-30-2005, 01:45 PM

DominicB,
Below is the use of the same code you recommend. I get a run-time error 1004 at the .AddComment line. I'm not a proven VBA programmer yet, so the flow may seem not very VBA.

At the top of my program:
----------------------------------------

Dim UserCell As String
Dim CommentVariable As String

Further down the program
---------------------------------------

For Column = 3 To 52 Step 1
SheetCellPrevious2 = Sheets("Previous").Cells(Row, Column).Value
SheetCellCurrent2 = Sheets("Current").Cells(Row, Column).Value
If SheetCellCurrent2 <> SheetCellPrevious2 Then
Cells(Row, Column).Select
CommentVariable = Cells(Row, Column).Value
UserCell = ActiveWindow.RangeSelection.Address
With Range(UserCell)

.AddComment
.Comment.Text Text:=CommentVariable
End With
'Color Mismatched Cell in Largest Map
With Selection.Interior
.ColorIndex = 27
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
TotalCompareErrors = TotalCompareErrors + 1
End With
Else
End If ' End IF SheetCellCurrent2 <> SheetCellPrevious2
Next Column

Your thoughts and recommendations.........

Tony

**ajocius** · 07-30-2005, 02:57 PM

DomincB,
Oh, BTW the version of Excel I'm using is 2002.

Tony

**ajocius** · 07-30-2005, 03:55 PM

DominicB,
I found the error in my ways. I found it quite accidentally, maybe you could comment why this happens. Apparently the cell you insert comments cannot have comments already. That was the problem with my code. Without you seeing the spreadsheets you assumed no comments were in the sheet receiving comments. I'm sorry that I didn't make you aware of this. Nonetheless, your model is part of my program. Thank you and God Bless.

Tony

**dominicb** · 07-30-2005, 06:50 PM

Hi ajocius

The fact that there might have been a comment in the cell never actually occurred to me. If you want to edit a comment, then you miss out the .AddComment command - this is only used to add a new comment.

I must admit to being a little confused as to why you reported this instruction as halting the program - glad you got it sorted and pleased to have helped out in some way.

Take care

DominicB