Prev: Excel 2003 Function Help
Next: Delete an Add-In?
From: Gary McCarthy on 24 May 2010 16:18
Gord,

Is there a way to do this referencing a cell on another tab? For example
Sheet2!B1 ?

Thanks.

"Gord Dibben" wrote:

> Sub Comment_Add_Cell()
> Dim cmt As Comment
> Dim r As Range
> Set r = Range("A1")
> On Error Resume Next
> r.Comment.Delete
> Set cmt = r.Comment
> If cmt Is Nothing Then
> Set cmt = r.AddComment
> cmt.Text Text:=r.Offset(1, 1).Text
> End If
> End Sub
>
> 'For a range of cells in A with text in B
>
> Sub Comment_Add_Range()
> Dim cmt As Comment
> Dim r As Range
> For Each r In Range("A1:A10")
> On Error Resume Next
> r.Comment.Delete
> Set cmt = r.Comment
> If cmt Is Nothing Then
> Set cmt = r.AddComment
> cmt.Text Text:=r.Offset(0, 1).Text
> End If
> Next r
> End Sub
>
> Either of these you could call from a Workbook_Open event in Thisworkbook
> module
>
> Private Sub Workbook_Open()
> Sheets("Sheet1").Activate
> Comment_Add_Range
> 'Comment_Add_Cell
> End Sub
>
>
> Gord
>
> On Tue, 29 Jul 2008 12:38:01 -0700, NervousFred
> <NervousFred(a)discussions.microsoft.com> wrote:
>
> >Gord your awesome, got it working how I want it.
> >
> >One last question. I noticed, while trying to figure out the indexes for
> >Cells, that I had to delete the old comments first before the macro would
> >re-write the new comments over them.
> >
> >Is there a delete comments line of code I could put ahead of the rest of the
> >code to make sure that everytime the macro is run it will put the new
> >information without me having to delete the old stuff first.
> >
> >Also will this macro run automaticlly when the spreadsheet is opened? Just
> >trying to make it all autonomus without any user inputs.
>
>
From: Gord Dibben on 24 May 2010 19:10
Sub Comment_Add_Cell()
Dim cmt As Comment
Dim r As Range
Dim rr As Range
Set r = ActiveSheet.Range("A1")
Set rr = Sheets("Sheet2").Range("B1")
On Error Resume Next
r.Comment.Delete
Set cmt = r.Comment
If cmt Is Nothing Then
Set cmt = r.AddComment
cmt.Text Text:=rr.Text
End If
End Sub

Code for a range of cells will have to wait until after the hockey game.

Go Philly!!


Gord

On Mon, 24 May 2010 13:18:02 -0700, Gary McCarthy
<GaryMcCarthy(a)discussions.microsoft.com> wrote:

>Gord,
>
>Is there a way to do this referencing a cell on another tab? For example
>Sheet2!B1 ?
>
>Thanks.
>
>"Gord Dibben" wrote:
>
>> Sub Comment_Add_Cell()
>> Dim cmt As Comment
>> Dim r As Range
>> Set r = Range("A1")
>> On Error Resume Next
>> r.Comment.Delete
>> Set cmt = r.Comment
>> If cmt Is Nothing Then
>> Set cmt = r.AddComment
>> cmt.Text Text:=r.Offset(1, 1).Text
>> End If
>> End Sub
>>
>> 'For a range of cells in A with text in B
>>
>> Sub Comment_Add_Range()
>> Dim cmt As Comment
>> Dim r As Range
>> For Each r In Range("A1:A10")
>> On Error Resume Next
>> r.Comment.Delete
>> Set cmt = r.Comment
>> If cmt Is Nothing Then
>> Set cmt = r.AddComment
>> cmt.Text Text:=r.Offset(0, 1).Text
>> End If
>> Next r
>> End Sub
>>
>> Either of these you could call from a Workbook_Open event in Thisworkbook
>> module
>>
>> Private Sub Workbook_Open()
>> Sheets("Sheet1").Activate
>> Comment_Add_Range
>> 'Comment_Add_Cell
>> End Sub
>>
>>
>> Gord
>>
>> On Tue, 29 Jul 2008 12:38:01 -0700, NervousFred
>> <NervousFred(a)discussions.microsoft.com> wrote:
>>
>> >Gord your awesome, got it working how I want it.
>> >
>> >One last question. I noticed, while trying to figure out the indexes for
>> >Cells, that I had to delete the old comments first before the macro would
>> >re-write the new comments over them.
>> >
>> >Is there a delete comments line of code I could put ahead of the rest of the
>> >code to make sure that everytime the macro is run it will put the new
>> >information without me having to delete the old stuff first.
>> >
>> >Also will this macro run automaticlly when the spreadsheet is opened? Just
>> >trying to make it all autonomus without any user inputs.
>>
>>

 | 
Pages: 1
Prev: Excel 2003 Function Help
Next: Delete an Add-In?