Can I reference comments by Cell Name?

[flo1730]

I'm creating an Excel checklist for other users and I have added context to
each item on the list using the "comments" function.

I have an issue regarding the way the comments are set out for printing. I
have selected the "print comments at end of sheet" option in page setup. This
is fine except that the comments are referenced by cell number which won't
make much sense to my intended audience.

Is there any way to reference the printed comments by "Cell Name"?

Thanks,

[Jim Rech]

Since you can't change how Excel prints cell comment you might consider
listing cell comments in a range and printing the list. Here's a routines
to do the listing. It operates on the active sheet. It hasn't been tested
extensively.

Sub ListNotes()
Dim ListSheet As Worksheet, CurrSheet As Worksheet
Dim Cell As Range, Counter As Integer
Dim CellName As String, CommentsRg As Range
On Error GoTo NoNotes
Set CommentsRg = Cells.SpecialCells(xlCellTypeComments)
Set CurrSheet = ActiveSheet
Set ListSheet = Worksheets.Add
ListSheet.Range("A1").Value = ActiveWorkbook.Name & " " &
ActiveSheet.Name
Counter = 1
On Error Resume Next
For Each Cell In CommentsRg
Counter = Counter + 1
With ListSheet
Err.Clear
.Cells(Counter, 1).Value = Cell.Name.Name
If Err.Number = 0 Then GoTo SkipAddress
.Cells(Counter, 1).Value = Cell.Address
SkipAddress:
.Cells(Counter, 2).Value = Cell.Comment.Text
End With
Next
Exit Sub
NoNotes:
MsgBox "No cellnotes on this sheet"
End Sub


--
Jim
"flo1730" <[email protected]> wrote in message
news:[email protected]...
| I'm creating an Excel checklist for other users and I have added context
to
| each item on the list using the "comments" function.
|
| I have an issue regarding the way the comments are set out for printing. I
| have selected the "print comments at end of sheet" option in page setup.
This
| is fine except that the comments are referenced by cell number which won't
| make much sense to my intended audience.
|
| Is there any way to reference the printed comments by "Cell Name"?
|
| Thanks,

[flo1730]

Thanks Jim,

Sorry to sound like a newbie but where would I paste that code? I've never
used code in Excel before. Do I use the VB editor or the MS script editor?

Flo

"Jim Rech" wrote:

> Since you can't change how Excel prints cell comment you might consider
> listing cell comments in a range and printing the list. Here's a routines
> to do the listing. It operates on the active sheet. It hasn't been tested
> extensively.
>
> Sub ListNotes()
> Dim ListSheet As Worksheet, CurrSheet As Worksheet
> Dim Cell As Range, Counter As Integer
> Dim CellName As String, CommentsRg As Range
> On Error GoTo NoNotes
> Set CommentsRg = Cells.SpecialCells(xlCellTypeComments)
> Set CurrSheet = ActiveSheet
> Set ListSheet = Worksheets.Add
> ListSheet.Range("A1").Value = ActiveWorkbook.Name & " " &
> ActiveSheet.Name
> Counter = 1
> On Error Resume Next
> For Each Cell In CommentsRg
> Counter = Counter + 1
> With ListSheet
> Err.Clear
> .Cells(Counter, 1).Value = Cell.Name.Name
> If Err.Number = 0 Then GoTo SkipAddress
> .Cells(Counter, 1).Value = Cell.Address
> SkipAddress:
> .Cells(Counter, 2).Value = Cell.Comment.Text
> End With
> Next
> Exit Sub
> NoNotes:
> MsgBox "No cellnotes on this sheet"
> End Sub
>
>
> --
> Jim
> "flo1730" <[email protected]> wrote in message
> news:[email protected]...
> | I'm creating an Excel checklist for other users and I have added context
> to
> | each item on the list using the "comments" function.
> |
> | I have an issue regarding the way the comments are set out for printing. I
> | have selected the "print comments at end of sheet" option in page setup.
> This
> | is fine except that the comments are referenced by cell number which won't
> | make much sense to my intended audience.
> |
> | Is there any way to reference the printed comments by "Cell Name"?
> |
> | Thanks,
>
>
>

[Dave Peterson]

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste Jim's code in there.

Now go back to excel and test it out via:
tools|macro|macros...

flo1730 wrote:
>
> Thanks Jim,
>
> Sorry to sound like a newbie but where would I paste that code? I've never
> used code in Excel before. Do I use the VB editor or the MS script editor?
>
> Flo
>
> "Jim Rech" wrote:
>
> > Since you can't change how Excel prints cell comment you might consider
> > listing cell comments in a range and printing the list. Here's a routines
> > to do the listing. It operates on the active sheet. It hasn't been tested
> > extensively.
> >
> > Sub ListNotes()
> > Dim ListSheet As Worksheet, CurrSheet As Worksheet
> > Dim Cell As Range, Counter As Integer
> > Dim CellName As String, CommentsRg As Range
> > On Error GoTo NoNotes
> > Set CommentsRg = Cells.SpecialCells(xlCellTypeComments)
> > Set CurrSheet = ActiveSheet
> > Set ListSheet = Worksheets.Add
> > ListSheet.Range("A1").Value = ActiveWorkbook.Name & " " &
> > ActiveSheet.Name
> > Counter = 1
> > On Error Resume Next
> > For Each Cell In CommentsRg
> > Counter = Counter + 1
> > With ListSheet
> > Err.Clear
> > .Cells(Counter, 1).Value = Cell.Name.Name
> > If Err.Number = 0 Then GoTo SkipAddress
> > .Cells(Counter, 1).Value = Cell.Address
> > SkipAddress:
> > .Cells(Counter, 2).Value = Cell.Comment.Text
> > End With
> > Next
> > Exit Sub
> > NoNotes:
> > MsgBox "No cellnotes on this sheet"
> > End Sub
> >
> >
> > --
> > Jim
> > "flo1730" <[email protected]> wrote in message
> > news:[email protected]...
> > | I'm creating an Excel checklist for other users and I have added context
> > to
> > | each item on the list using the "comments" function.
> > |
> > | I have an issue regarding the way the comments are set out for printing. I
> > | have selected the "print comments at end of sheet" option in page setup.
> > This
> > | is fine except that the comments are referenced by cell number which won't
> > | make much sense to my intended audience.
> > |
> > | Is there any way to reference the printed comments by "Cell Name"?
> > |
> > | Thanks,
> >
> >
> >

--

Dave Peterson

[flo1730]

Thanks Dave,

I think I've got it now. The only problem is that our security setting at
work is set to high so the macro won't run!

I'll have to try it at home.

Flo


"Dave Peterson" wrote:

> If you're new to macros, you may want to read David McRitchie's intro at:
> http://www.mvps.org/dmcritchie/excel/getstarted.htm
>
> Short course:
>
> Open your workbook.
> Hit alt-f11 to get to the VBE (where macros/UDF's live)
> hit ctrl-R to view the project explorer
> Find your workbook.
> should look like: VBAProject (yourfilename.xls)
>
> right click on the project name
> Insert, then Module
> You should see the code window pop up on the right hand side
>
> Paste Jim's code in there.
>
> Now go back to excel and test it out via:
> tools|macro|macros...
>
> flo1730 wrote:
> >
> > Thanks Jim,
> >
> > Sorry to sound like a newbie but where would I paste that code? I've never
> > used code in Excel before. Do I use the VB editor or the MS script editor?
> >
> > Flo
> >
> > "Jim Rech" wrote:
> >
> > > Since you can't change how Excel prints cell comment you might consider
> > > listing cell comments in a range and printing the list. Here's a routines
> > > to do the listing. It operates on the active sheet. It hasn't been tested
> > > extensively.
> > >
> > > Sub ListNotes()
> > > Dim ListSheet As Worksheet, CurrSheet As Worksheet
> > > Dim Cell As Range, Counter As Integer
> > > Dim CellName As String, CommentsRg As Range
> > > On Error GoTo NoNotes
> > > Set CommentsRg = Cells.SpecialCells(xlCellTypeComments)
> > > Set CurrSheet = ActiveSheet
> > > Set ListSheet = Worksheets.Add
> > > ListSheet.Range("A1").Value = ActiveWorkbook.Name & " " &
> > > ActiveSheet.Name
> > > Counter = 1
> > > On Error Resume Next
> > > For Each Cell In CommentsRg
> > > Counter = Counter + 1
> > > With ListSheet
> > > Err.Clear
> > > .Cells(Counter, 1).Value = Cell.Name.Name
> > > If Err.Number = 0 Then GoTo SkipAddress
> > > .Cells(Counter, 1).Value = Cell.Address
> > > SkipAddress:
> > > .Cells(Counter, 2).Value = Cell.Comment.Text
> > > End With
> > > Next
> > > Exit Sub
> > > NoNotes:
> > > MsgBox "No cellnotes on this sheet"
> > > End Sub
> > >
> > >
> > > --
> > > Jim
> > > "flo1730" <[email protected]> wrote in message
> > > news:[email protected]...
> > > | I'm creating an Excel checklist for other users and I have added context
> > > to
> > > | each item on the list using the "comments" function.
> > > |
> > > | I have an issue regarding the way the comments are set out for printing. I
> > > | have selected the "print comments at end of sheet" option in page setup.
> > > This
> > > | is fine except that the comments are referenced by cell number which won't
> > > | make much sense to my intended audience.
> > > |
> > > | Is there any way to reference the printed comments by "Cell Name"?
> > > |
> > > | Thanks,
> > >
> > >
> > >
>
> --
>
> Dave Peterson
>

[Dave Peterson]

Close your workbook with the macro.
Tools|macro|security|Security level
change it there.

Then reopen your workbook.

===
Some Network IT folks block you from saving that setting, but I think if you
toggle it when you need it (then open the workbook), it should work.



flo1730 wrote:
>
> Thanks Dave,
>
> I think I've got it now. The only problem is that our security setting at
> work is set to high so the macro won't run!
>
> I'll have to try it at home.
>
> Flo
>
> "Dave Peterson" wrote:
>
> > If you're new to macros, you may want to read David McRitchie's intro at:
> > http://www.mvps.org/dmcritchie/excel/getstarted.htm
> >
> > Short course:
> >
> > Open your workbook.
> > Hit alt-f11 to get to the VBE (where macros/UDF's live)
> > hit ctrl-R to view the project explorer
> > Find your workbook.
> > should look like: VBAProject (yourfilename.xls)
> >
> > right click on the project name
> > Insert, then Module
> > You should see the code window pop up on the right hand side
> >
> > Paste Jim's code in there.
> >
> > Now go back to excel and test it out via:
> > tools|macro|macros...
> >
> > flo1730 wrote:
> > >
> > > Thanks Jim,
> > >
> > > Sorry to sound like a newbie but where would I paste that code? I've never
> > > used code in Excel before. Do I use the VB editor or the MS script editor?
> > >
> > > Flo
> > >
> > > "Jim Rech" wrote:
> > >
> > > > Since you can't change how Excel prints cell comment you might consider
> > > > listing cell comments in a range and printing the list. Here's a routines
> > > > to do the listing. It operates on the active sheet. It hasn't been tested
> > > > extensively.
> > > >
> > > > Sub ListNotes()
> > > > Dim ListSheet As Worksheet, CurrSheet As Worksheet
> > > > Dim Cell As Range, Counter As Integer
> > > > Dim CellName As String, CommentsRg As Range
> > > > On Error GoTo NoNotes
> > > > Set CommentsRg = Cells.SpecialCells(xlCellTypeComments)
> > > > Set CurrSheet = ActiveSheet
> > > > Set ListSheet = Worksheets.Add
> > > > ListSheet.Range("A1").Value = ActiveWorkbook.Name & " " &
> > > > ActiveSheet.Name
> > > > Counter = 1
> > > > On Error Resume Next
> > > > For Each Cell In CommentsRg
> > > > Counter = Counter + 1
> > > > With ListSheet
> > > > Err.Clear
> > > > .Cells(Counter, 1).Value = Cell.Name.Name
> > > > If Err.Number = 0 Then GoTo SkipAddress
> > > > .Cells(Counter, 1).Value = Cell.Address
> > > > SkipAddress:
> > > > .Cells(Counter, 2).Value = Cell.Comment.Text
> > > > End With
> > > > Next
> > > > Exit Sub
> > > > NoNotes:
> > > > MsgBox "No cellnotes on this sheet"
> > > > End Sub
> > > >
> > > >
> > > > --
> > > > Jim
> > > > "flo1730" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > | I'm creating an Excel checklist for other users and I have added context
> > > > to
> > > > | each item on the list using the "comments" function.
> > > > |
> > > > | I have an issue regarding the way the comments are set out for printing. I
> > > > | have selected the "print comments at end of sheet" option in page setup.
> > > > This
> > > > | is fine except that the comments are referenced by cell number which won't
> > > > | make much sense to my intended audience.
> > > > |
> > > > | Is there any way to reference the printed comments by "Cell Name"?
> > > > |
> > > > | Thanks,
> > > >
> > > >
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson