I have been trying to use this macro:
http://www.contextures.com/xlcomment...l#CopyAdjacent
to copy comments into a different cell. I have seen numerious links to this
same macro, but I can't get it to work. I type it into VB just as it is
written, but when I run the macro, I always get the dialog box that says "no
comments found", even though there are comments. Any ideas what I'm doing
wrong?

Hi SL,

Works fine for me.
Are you sure the cells to the right of the ones containing the
comments are blank?

If you take out the lines:
If mycell.Offset(0, 1).Value = "" Then
&
End If

It should overwrite anything found in the cells to the right - any use
to you?

HtH,
JF

On 25 Dec, 10:17, Still Learning
wrote:
I have been trying to use this macro:http://www.contextures.com/xlcomment...l#CopyAdjacent
to copy comments into a different cell. *I have seen numerious links to this
same macro, but I can't get it to work. *I type it into VB just as it is
written, but when I run the macro, I always get the dialog box that says "no
comments found", even though there are comments. *Any ideas what I'm doing
wrong?

Okay, I took out the lines you said. I still get the no comments found msg.

Here is what I'm doing to test this;
I highlight the cell that has a comment (with the little red triangle) and
select run macro. That's when I get the msg.

I've also tried running the macro on the sheet, it did the same, that's why
I tried just the one cell.

I very new to programming in VB. I would really like to get this to work.

"Joshua Fandango" wrote:

Hi SL,

Works fine for me.
Are you sure the cells to the right of the ones containing the
comments are blank?

If you take out the lines:
If mycell.Offset(0, 1).Value = "" Then
&
End If

It should overwrite anything found in the cells to the right - any use
to you?

HtH,
JF

On 25 Dec, 10:17, Still Learning
wrote:
I have been trying to use this macro:http://www.contextures.com/xlcomment...l#CopyAdjacent
to copy comments into a different cell. I have seen numerious links to this
same macro, but I can't get it to work. I type it into VB just as it is
written, but when I run the macro, I always get the dialog box that says "no
comments found", even though there are comments. Any ideas what I'm doing
wrong?

Is the worksheet protected?

On 25 Dec, 11:05, Still Learning
wrote:
Okay, I took out the lines you said. *I still get the no comments found msg.

Here is what I'm doing to test this;
I highlight the cell that has a comment (with the little red triangle) and
select run macro. *That's when I get the msg.

I've also tried running the macro on the sheet, it did the same, that's why
I tried just the one cell.

I very new to programming in VB. *I would really like to get this to work.



"Joshua Fandango" wrote:
Hi SL,

Works fine for me.
Are you sure the cells to the right of the ones containing the
comments are blank?

If you take out the lines:
If mycell.Offset(0, 1).Value = "" Then
&
End If

It should overwrite anything found in the cells to the right - any use
to you?

HtH,
JF

On 25 Dec, 10:17, Still Learning
wrote:
I have been trying to use this macro:http://www.contextures.com/xlcomment...l#CopyAdjacent
to copy comments into a different cell. *I have seen numerious links to this
same macro, but I can't get it to work. *I type it into VB just as it is
written, but when I run the macro, I always get the dialog box that says "no
comments found", even though there are comments. *Any ideas what I'm doing
wrong?- Hide quoted text -

- Show quoted text -

No it is not. I'm assuming that the macro doesn't need anything typed into
it (ie ranges (a1:b4) or sheet names or anythng).

Thanks for trying to help.

PS, is there somewhere that lists what these comands do? (as I said, I'm
still learning)

"Joshua Fandango" wrote:

Community Message Not Available

Hi SL,

You're right, there's no need to specify a range to act on as the code
works on every cell in the active worksheet with a comment in it (or
it is supposed too!)

See if the following will work; you need to highlight the cells
containing comments before running it.

Sub Comment_Text_In_Cell_To_Right()
Dim Cell As Object
On Error Resume Next
For Each Cell In Selection
If Cell.Comment = True Then
Cell.Offset(0, 1) = Cell.Comment.Text
End If
Next Cell
On Error GoTo 0
End Sub


On 25 Dec, 11:35, Still Learning <Still
wrote:
No it is not. *I'm assuming that the macro doesn't need anything typed into
it (ie ranges (a1:b4) or sheet names or anythng).

Thanks for trying to help.

PS, is there somewhere that lists what these comands do? *(as I said, I'm
still learning)



"Joshua Fandango" wrote:
Community Message Not Available- Hide quoted text -

- Show quoted text -

Give this macro a try; it is a little more compact than the one posted on
Debra's website and it operates slightly differently also (no error checking
is required with it)...

Sub ShowCommentsNextCell()
Dim C As Comment
If ActiveSheet.Comments.Count 0 Then
Application.ScreenUpdating = False
For Each C In ActiveSheet.Comments
With C.Parent.Offset(, 1)
If .Value = "" Then .Value = C.Text
End With
Next
Application.ScreenUpdating = True
End If
End Sub

By the way, you should always copy/paste code from newsgroups and/or
websites rather than typing them into the VB code windows (as your original
post indicated you did).

--
Rick (MVP - Excel)


"Still Learning" <Still wrote in message
...
No it is not. I'm assuming that the macro doesn't need anything typed
into
it (ie ranges (a1:b4) or sheet names or anythng).

Thanks for trying to help.

PS, is there somewhere that lists what these comands do? (as I said, I'm
still learning)

"Joshua Fandango" wrote:

Community Message Not Available

On Dec 26, 12:26*pm, "Rick Rothstein"
wrote:
Give this macro a try; it is a little more compact than the one posted on
Debra's website and it operates slightly differently also (no error checking
is required with it)...

Sub ShowCommentsNextCell()
* Dim C As Comment
* If ActiveSheet.Comments.Count 0 Then
* * Application.ScreenUpdating = False
* * For Each C In ActiveSheet.Comments
* * * With C.Parent.Offset(, 1)
* * * * If .Value = "" Then .Value = C.Text
* * * End With
* * Next
* * Application.ScreenUpdating = True
* End If
End Sub

By the way, you should always copy/paste code from newsgroups and/or
websites rather than typing them into the VB code windows (as your original
post indicated you did).

--
Rick (MVP - Excel)

"Still Learning" <Still wrote in ...



No it is not. *I'm assuming that the macro doesn't need anything typed
into
it (ie ranges (a1:b4) or sheet names or anythng).

Thanks for trying to help.

PS, is there somewhere that lists what these comands do? *(as I said, I'm
still learning)

"Joshua Fandango" wrote:

Community Message Not Available- Hide quoted text -

- Show quoted text -

I think the problem with the original macro was that it only picks up
comments on the current page. so if you apply the macro to a command
button, it will only scan the current worksheet for comments in which
the command button that executed the macro resides. If there are no
comments posted on the current worksheet, the dialogue box will return
"no comments found" even thought there could be several in the
remainder of the workbook.

Try manipulating the language so that it applies to the entire
workbook when executing so that it doesnt just scan an indidual sheet.

hopefully that helps, if not, disregard.

If that it the case, here is my macro modified to copy all comments on all
sheets over to the next cell provided that cell has nothing in it...

Sub ShowCommentsNextCell()
Dim C As Comment
Dim WS As Worksheet
Application.ScreenUpdating = False
For Each WS In Worksheets
If WS.Comments.Count 0 Then
For Each C In WS.Comments
With C.Parent.Offset(, 1)
If .Value = "" Then .Value = C.Text
End With
Next
End If
Next
Application.ScreenUpdating = True
End Sub

--
Rick (MVP - Excel)


"brian the great" wrote in message
...
On Dec 26, 12:26 pm, "Rick Rothstein"
wrote:
Give this macro a try; it is a little more compact than the one posted on
Debra's website and it operates slightly differently also (no error
checking
is required with it)...

Sub ShowCommentsNextCell()
Dim C As Comment
If ActiveSheet.Comments.Count 0 Then
Application.ScreenUpdating = False
For Each C In ActiveSheet.Comments
With C.Parent.Offset(, 1)
If .Value = "" Then .Value = C.Text
End With
Next
Application.ScreenUpdating = True
End If
End Sub

By the way, you should always copy/paste code from newsgroups and/or
websites rather than typing them into the VB code windows (as your
original
post indicated you did).

--
Rick (MVP - Excel)

"Still Learning" <Still wrote in
...



No it is not. I'm assuming that the macro doesn't need anything typed
into
it (ie ranges (a1:b4) or sheet names or anythng).

Thanks for trying to help.

PS, is there somewhere that lists what these comands do? (as I said, I'm
still learning)

"Joshua Fandango" wrote:

Community Message Not Available- Hide quoted text -

- Show quoted text -

I think the problem with the original macro was that it only picks up
comments on the current page. so if you apply the macro to a command
button, it will only scan the current worksheet for comments in which
the command button that executed the macro resides. If there are no
comments posted on the current worksheet, the dialogue box will return
"no comments found" even thought there could be several in the
remainder of the workbook.

Try manipulating the language so that it applies to the entire
workbook when executing so that it doesnt just scan an indidual sheet.

hopefully that helps, if not, disregard.

I notice that I did not include the warning message box you mentioned about
the active sheet not having any comments on it; here is my code, modified to
show a message box when there are no comments on the active worksheet...

Sub ShowCommentsNextCell()
Dim C As Comment
If ActiveSheet.Comments.Count 0 Then
Application.ScreenUpdating = False
For Each C In ActiveSheet.Comments
With C.Parent.Offset(, 1)
If .Value = "" Then .Value = C.Text
End With
Next
Application.ScreenUpdating = True
Else
MsgBox "There are no comments on this sheet."
End If
End Sub

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
Give this macro a try; it is a little more compact than the one posted on
Debra's website and it operates slightly differently also (no error
checking is required with it)...

Sub ShowCommentsNextCell()
Dim C As Comment
If ActiveSheet.Comments.Count 0 Then
Application.ScreenUpdating = False
For Each C In ActiveSheet.Comments
With C.Parent.Offset(, 1)
If .Value = "" Then .Value = C.Text
End With
Next
Application.ScreenUpdating = True
End If
End Sub

By the way, you should always copy/paste code from newsgroups and/or
websites rather than typing them into the VB code windows (as your
original post indicated you did).

--
Rick (MVP - Excel)


"Still Learning" <Still wrote in
message ...
No it is not. I'm assuming that the macro doesn't need anything typed
into
it (ie ranges (a1:b4) or sheet names or anythng).

Thanks for trying to help.

PS, is there somewhere that lists what these comands do? (as I said, I'm
still learning)

"Joshua Fandango" wrote:

Community Message Not Available

In keeping with my other post in this thread, here is modified code that
includes a message if no sheets have comments or, if sheets do have
comments, the next cell is not empty...

Sub ShowCommentsNextCell()
Dim B As Boolean
Dim C As Comment
Dim WS As Worksheet
Application.ScreenUpdating = False
For Each WS In Worksheets
If WS.Comments.Count 0 Then
For Each C In WS.Comments
With C.Parent.Offset(, 1)
If .Value = "" Then
.Value = C.Text
If Not B Then B = True
End If
End With
Next
End If
Next
Application.ScreenUpdating = True
If Not B Then MsgBox "Either no sheets had any comments or, if any " & _
"sheets had comments, the next cell was not empty."
End Sub

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
If that it the case, here is my macro modified to copy all comments on all
sheets over to the next cell provided that cell has nothing in it...

Sub ShowCommentsNextCell()
Dim C As Comment
Dim WS As Worksheet
Application.ScreenUpdating = False
For Each WS In Worksheets
If WS.Comments.Count 0 Then
For Each C In WS.Comments
With C.Parent.Offset(, 1)
If .Value = "" Then .Value = C.Text
End With
Next
End If
Next
Application.ScreenUpdating = True
End Sub

--
Rick (MVP - Excel)


"brian the great" wrote in message
...
On Dec 26, 12:26 pm, "Rick Rothstein"
wrote:
Give this macro a try; it is a little more compact than the one posted on
Debra's website and it operates slightly differently also (no error
checking
is required with it)...

Sub ShowCommentsNextCell()
Dim C As Comment
If ActiveSheet.Comments.Count 0 Then
Application.ScreenUpdating = False
For Each C In ActiveSheet.Comments
With C.Parent.Offset(, 1)
If .Value = "" Then .Value = C.Text
End With
Next
Application.ScreenUpdating = True
End If
End Sub

By the way, you should always copy/paste code from newsgroups and/or
websites rather than typing them into the VB code windows (as your
original
post indicated you did).

--
Rick (MVP - Excel)

"Still Learning" <Still wrote in
...



No it is not. I'm assuming that the macro doesn't need anything typed
into
it (ie ranges (a1:b4) or sheet names or anythng).

Thanks for trying to help.

PS, is there somewhere that lists what these comands do? (as I said,
I'm
still learning)

"Joshua Fandango" wrote:

Community Message Not Available- Hide quoted text -

- Show quoted text -

I think the problem with the original macro was that it only picks up
comments on the current page. so if you apply the macro to a command
button, it will only scan the current worksheet for comments in which
the command button that executed the macro resides. If there are no
comments posted on the current worksheet, the dialogue box will return
"no comments found" even thought there could be several in the
remainder of the workbook.

Try manipulating the language so that it applies to the entire
workbook when executing so that it doesnt just scan an indidual sheet.

hopefully that helps, if not, disregard.

Hey, I just got back to work and your macro worked perfectly. Maybe the
reason was because I did type it instead of coping it.

Can you recomned sites/books that will help me learn the codes for these
macros?

Thanks a whole lot,
Chris

"Rick Rothstein" wrote:

I notice that I did not include the warning message box you mentioned about
the active sheet not having any comments on it; here is my code, modified to
show a message box when there are no comments on the active worksheet...

Sub ShowCommentsNextCell()
Dim C As Comment
If ActiveSheet.Comments.Count 0 Then
Application.ScreenUpdating = False
For Each C In ActiveSheet.Comments
With C.Parent.Offset(, 1)
If .Value = "" Then .Value = C.Text
End With
Next
Application.ScreenUpdating = True
Else
MsgBox "There are no comments on this sheet."
End If
End Sub

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
Give this macro a try; it is a little more compact than the one posted on
Debra's website and it operates slightly differently also (no error
checking is required with it)...

Sub ShowCommentsNextCell()
Dim C As Comment
If ActiveSheet.Comments.Count 0 Then
Application.ScreenUpdating = False
For Each C In ActiveSheet.Comments
With C.Parent.Offset(, 1)
If .Value = "" Then .Value = C.Text
End With
Next
Application.ScreenUpdating = True
End If
End Sub

By the way, you should always copy/paste code from newsgroups and/or
websites rather than typing them into the VB code windows (as your
original post indicated you did).

--
Rick (MVP - Excel)


"Still Learning" <Still wrote in
message ...
No it is not. I'm assuming that the macro doesn't need anything typed
into
it (ie ranges (a1:b4) or sheet names or anythng).

Thanks for trying to help.

PS, is there somewhere that lists what these comands do? (as I said, I'm
still learning)

"Joshua Fandango" wrote:

Community Message Not Available

These can get you started with macros...

http://www.bettersolutions.com/excel...R646543531.htm

http://www.anthony-vba.kefra.com/

http://www.tushar-mehta.com/excel/vb...rder/index.htm

The key to programming macros, after you get the basics down, is (in my
opinion) to familiarize yourself with the various Objects and their
Properties as well as the various Collection take Excel maintains. I'm not
saying to memorize them, just read through them (and their descriptions) a
few times so your mind has seen them... then when the opportunity to use one
of them crops up, you might remember you saw something like it in the help
files somewhere and you can then search it out. If you have never seen them,
then it will be impossible for your mind to remember having seen them and
the opportunity to use them will be lost. Like any other programming
language, none of this will come overnight... you need to practice,
practice, practice. And don't be afraid to make mistakes... trying to figure
out why a piece of code that you thought should have worked, but didn't, is
the best way to learn.

--
Rick (MVP - Excel)


"Still Learning" wrote in message
...
Hey, I just got back to work and your macro worked perfectly. Maybe the
reason was because I did type it instead of coping it.

Can you recomned sites/books that will help me learn the codes for these
macros?

Thanks a whole lot,
Chris

"Rick Rothstein" wrote:

I notice that I did not include the warning message box you mentioned
about
the active sheet not having any comments on it; here is my code, modified
to
show a message box when there are no comments on the active worksheet...

Sub ShowCommentsNextCell()
Dim C As Comment
If ActiveSheet.Comments.Count 0 Then
Application.ScreenUpdating = False
For Each C In ActiveSheet.Comments
With C.Parent.Offset(, 1)
If .Value = "" Then .Value = C.Text
End With
Next
Application.ScreenUpdating = True
Else
MsgBox "There are no comments on this sheet."
End If
End Sub

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
Give this macro a try; it is a little more compact than the one posted
on
Debra's website and it operates slightly differently also (no error
checking is required with it)...

Sub ShowCommentsNextCell()
Dim C As Comment
If ActiveSheet.Comments.Count 0 Then
Application.ScreenUpdating = False
For Each C In ActiveSheet.Comments
With C.Parent.Offset(, 1)
If .Value = "" Then .Value = C.Text
End With
Next
Application.ScreenUpdating = True
End If
End Sub

By the way, you should always copy/paste code from newsgroups and/or
websites rather than typing them into the VB code windows (as your
original post indicated you did).

--
Rick (MVP - Excel)


"Still Learning" <Still wrote in
message ...
No it is not. I'm assuming that the macro doesn't need anything typed
into
it (ie ranges (a1:b4) or sheet names or anythng).

Thanks for trying to help.

PS, is there somewhere that lists what these comands do? (as I said,
I'm
still learning)

"Joshua Fandango" wrote:

Community Message Not Available