Hide comments from all except me

[glenlee1]

Is this possible?
I have a sensitive spreadsheet with many cell comments that I would
like to have available to my eyes only. How can I hide them? I know,
I can go to Tools-Options-View-Comments-None, but then any reader can
also go there and click show the comments. I don't want that.

I still want the comment indicator to show in the cell; I just don't
want anybody to see the comment except me....is this possible?

[Vasant Nanavati]

Hi:

I don't think that's easily doable.

A crude solution might be to have a "twin" worksheet in the same workbook,
with the original worksheet hidden and the workbook protected with a
password. Each cell in the twin worksheet would have a formula referring to
the corresponding cell in the hidden worksheet. Zeros would be suppressed
either through the formula or using <Tools> <Options>. The comments on the
hidden worksheet would remain hidden.

It's crude, but I can't think of another way.
--
Regards,

Vasant.

"glenlee1" <glen...@nospam.com> wrote in message
news:3cddd339...@news.newsguy.com...

[Sandy Mann]

If the comments are in cells with formulas then you can use the N function
to insert a comment into the formula while having a blank comment to give
you a comment marker. For example:

=A1 *B15 +N("This is a comment meant only for me")

Then hide the formulas by selecting Hide in the Format > Cells > Protecting
dialogue box and finally protect the sheet. (Remember to unlock all cells
that will be used for data entry though.

Worksheet password protection is not great but will keep out the casual user

HTH

Sandy

"glenlee1" <glen...@nospam.com> wrote in message
news:3cddd339...@news.newsguy.com...

[Dave Peterson]

I'm not sure if this is any less crude than Vasant's suggestion, but it might be
something to explore:

You have two macros. One macro that saves the comments to a "comment" worksheet
(and clears the comments against the "real" one). And one macro that restores
the comments from the "comment" worksheet back to the "real" one.

Kind of like this:

Option Explicit

Sub SaveComments()

Dim origWks As Worksheet
Dim commWks As Worksheet
Dim commRng As Range
Dim myCell As Range
Dim oRow As Long

Set origWks = Worksheets("sheet1")
Set commWks = Worksheets("sheet2")

On Error Resume Next
Set commRng = origWks.Cells.SpecialCells(xlCellTypeComments)
On Error GoTo 0

If commRng Is Nothing Then
MsgBox "No Comments found"
Exit Sub
End If

commWks.Cells.Delete

oRow = 0
For Each myCell In commRng.Cells
oRow = oRow + 1
commWks.Cells(oRow, 1).Formula = "=" & myCell.Address(external:=True)
commWks.Cells(oRow, 2).Value = "'" & myCell.Comment.Text
Next myCell

'origWks.Cells.ClearComments 'I'd do this by hand, after checking

End Sub

Sub RestoreComments()

Dim origWks As Worksheet
Dim commWks As Worksheet
Dim commRng As Range
Dim myCell As Range
Dim iRow As Long
Dim myAddr As String

Set origWks = Worksheets("sheet1")
Set commWks = Worksheets("sheet2")

'origWks.Cells.ClearComments 'I'd do this by hand before starting

With commWks
If Application.CountA(.Columns(1)) = 0 Then
MsgBox "The comment sheet is blank. Please use a backup copy!!"
Exit Sub
End If
For iRow = 1 To .Cells(.Rows.Count, "A").End(xlUp).Row
If IsError(.Cells(iRow, 1).Value) Then
MsgBox "Something bad happened with one of cells" & _
"that contained a comment!" & vbLf & "on Row #: " & iRow
Else
myAddr = Range(Mid(.Cells(iRow, 1).Formula, 2)) _
.Address(external:=True)
Range(myAddr).ClearComments
Range(myAddr).AddComment commWks.Cells(iRow, 2).Value
Range(myAddr).Comment.Visible = False
End If
Next iRow
'commWks.Cells.Delete 'by hand???
End With

End Sub

There are a few things (commented in the code) that I'd do by hand, just to
verify that nothing went wrong. (I like to save my workbook first, then run
this kind of macro. If it doesn't do things correctly, then I can close without
saving.)

By putting the formula in Column A, it should take care of inserted/deleted
rows/columns automatically.

Be aware that any special formatting of the comment gets lost. (I'm not sure if
that's important to you. (It's not usually important to me!))

--

Dave Peterson
ec3...@msn.com

[Dana DeLouis]

Not the most secure option, but just something else to consider...

Sub HideComments()
Dim BigRng As Range, Cell As Range
Set BigRng = Cells.SpecialCells(xlCellTypeComments).Cells
If BigRng Is Nothing Then Exit Sub
For Each Cell In BigRng.Cells
Cell.Comment.Shape.TextFrame.Characters.Font.ColorIndex = 2
Next Cell
End Sub

Sub UnhideComments()
Dim BigRng As Range, Cell As Range
Set BigRng = Cells.SpecialCells(xlCellTypeComments).Cells
If BigRng Is Nothing Then Exit Sub
For Each Cell In BigRng.Cells
Cell.Comment.Shape.TextFrame.Characters.Font.ColorIndex = 1
Next Cell
End Sub

--
Dana DeLouis
Windows XP & Office XP
= = = = = = = = = = = = = = = = =

"glenlee1" <glen...@nospam.com> wrote in message
news:3cddd339...@news.newsguy.com...

[glenlee1]

Thanks for the suggestions (I guess nothing's impossible), I'm trying
them all.........