Sleeper: Preventing comments

[lior03]

hello
i am trying to disable comments altogether.
namely prevent the user from entering comments in a
workbook.

Sub nocomments()
Dim ws As Worksheet
Dim cell As Range
For Each ws In ActiveWorkbook.Worksheets
cell.Comment.Visible = False
MsgBox "no comments"
Next
End Sub

please help.
thanks

[erikvangeit]

Hello, lior03,

try this

Option Explicit

Public PrevRng As Range
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
'Erik Van Geit
'050814 1738
'no comments allowed
Dim check As Boolean
Dim cell As Range
On Error Resume Next
For Each cell In PrevRng
check = cell.NoteText <> ""
If check Then Exit For
Next cell
On Error GoTo 0
If check Then
PrevRng.ClearComments
MsgBox "you're not allowed to write comments", 48, "ERROR"
End If
Set PrevRng = Selection
End Sub

TO INSTALL IN THISWORKBOOK CODE WINDOW:
1. Rightclick the little Excel-icon on the topleft of your page just beside the Filemenu
2. Select "View Code" in drop down menu
4. VBE window will open ... paste code in and exit VBE

will this do what you need ?
Erik

EDIT: you could put the four lines If check ... End If within the For ... Next, but I found it more clear this way

[MWE]

hello
i am trying to disable comments altogether.
namely prevent the user from entering comments in a
workbook.

Sub nocomments()
Dim ws As Worksheet
Dim cell As Range
For Each ws In ActiveWorkbook.Worksheets
cell.Comment.Visible = False
MsgBox "no comments"
Next
End Sub

please help.
thanks

this is a bit of brute force, but it works. It uses the worksheet selection change procedure to sweep the range of cells with any comments and clear any comments.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim xlLastCol As Long
    Dim xlLastRow As Long
On Error GoTo EndSub
With ActiveSheet
        xlLastCol = .Cells.Find("*", .Cells(1), xlComments, _
            xlWhole, xlByColumns, xlPrevious).Column
        xlLastRow = .Cells.Find("*", .Cells(1), xlComments, _
            xlWhole, xlByRows, xlPrevious).Row
    End With
Range(Cells(1, 1), Cells(xlLastRow, xlLastCol)).ClearComments
EndSub:
End Sub

[Insomniac]

Another possibilty:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Me.Comments.Count > 0 Then Me.Cells.SpecialCells(xlCellTypeComments).ClearComments
End Sub

[MWE]

Another possibilty:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Me.Comments.Count > 0 Then Me.Cells.SpecialCells(xlCellTypeComments).ClearComments
End Sub

very nice !

[Ken Puls]

Another possibilty:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Me.Comments.Count > 0 Then Me.Cells.SpecialCells(xlCellTypeComments).ClearComments
End Sub

Agree with MWE! Very nice!

Modifying the above slightly, this could go in the ThisWorkbook module to avoid you having to put the above in every sheet module. Also will work even if you add a new sheet.

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
If Sh.Comments.Count > 0 Then _
    Sh.Cells.SpecialCells(xlCellTypeComments).ClearComments
End Sub

[mdmackillop]

KB Item?

[MOS MASTER]

KB Item?

Indeed! Please do submit!

[TonyJollans]

I have a problem with this!!

Comments are usually a good thing. Why do you want to stop people entering them?

[MOS MASTER]

I have a problem with this!!

Comments are usually a good thing. Why do you want to stop people entering them?

I'd agree on this but the only reason that I can think of to do this if you really don't want people to make those comments. (And the code seams to cater for that scenario)

However like you I've never needed a solution like this cause I like people to place comments. (In particular cause they take little place in Excel...)

Curious what the OP's motivation is...

[MWE]

I have a problem with this!!

Comments are usually a good thing. Why do you want to stop people entering them?

I agree that comments are usually a good thing. The key here is usually.

I have encountered several cases where users added comments to the point where there was too much clutter and it was hard to see the real data (and many of the comments were misleading if not wrong). Also, although comments are common to most of us, users not familiar with them can be confused.

I also am not a fan of how Excel manages comments. There seems to be an algorithm operating behind the scenes that moves comments to the most useless location on the sheet/window. I have never been able to get comments to consistently stay where I want them (size and location). I also do not like the inability (I think) to automatically timestamp comments nor the ability to manage the color of the small (red) triangle that appears in the top right of the cell when there are comments attached to that cell.
[see new post on this]

I think we should encourage the KB to be developed and submitted because it has real use. A warning about the implications of clearing comments should be included in the KB discussion.

[Insomniac]

I see some extended interest here, just a caveat to the use of SpecialCells:
http://support.microsoft.com/default...b;en-us;832293
as unlikely as it is Excel only supports a maximum of 8,192 non-contiguous cells through VBA macros. So if anyone applied the code as is to a sheet exceeding those specifications it will crash!
Also to point out it is normal practice to use On Error Resume Next when checking for SpecialsCells, but since we get the Comments.Count 1st we dont need that.

The rationale of course is to optimize the speed of execution if the 'If Sh.Comments.Count > 0 is true then specialcells do exist, else exit the if

Further more if you want a truly failsafe method without resorting to actually disabling insertcomment commands it may also be prudent to add the code to Worksheet Activate / Deactivate events,etc. in case the user adds a comment without selecting another cell....

In any case what about just protecting the worksheet?

"I have a problem with this!!

Comments are usually a good thing. Why do you want to stop people entering them?"

If I may Tony, I will give speak from my own experience only.
I personally programme Workbooks for multiple users at my work, it is often necessary to limit the users ability to alter comments, formats, formulas,etc...but required for them to enter data. It would be for instance a nightmare if any user could change font sizes or add comments or alter print settings........
1)it may mess up formulas
2)may stuff up print outs
3)filters may not reflect the actual used range
4)objects may be moved or deleted
5)on & on the list just grows

OK, so enough rambling, what I generally do in reality is provide a template where the user can do nearly whatever they want re-format,comment,etc. but when they save the workbook it just dumps the cell values to txt files to be read in by the real Master workbook.

What I'm saying is that the Excel front end may be used by many & varied skilled/unskilled users, most of my users are unskilled & are intimidated if they even accidently click on insert comment (what the hell is this? , why cant I type in the cell any more? I hate computers they never do what I want!!!). Anyway some will argue strongly for training of users, but in workplace reality this does not happen.
My point really is accuracy of end data - My personal experience is my work expects me to provide dummy proof interface using only excel/word! So I just work around my users abilities & knowledge.

[TonyJollans]

MWE,

I agree with most of what you say about Excel's Comment UI.

But I don't think disallowing comments is the answer to the problem of 'comment clutter'. The same could be said about comments in programs - sometimes it can be hard to see the code because of all the comments, and sometimes the comments can be misleading (not changed in line with code changes, perhaps) but few people would see banning comments from code as the solution.

Insomniac.

In any case what about just protecting the worksheet?

That is the obvious solution - and it seems to address most of the points you raise afterwards.

All,

Far too often the first thought of IT departments, and applications developers, is to try to disallow built-in functionality as a substitute for proper design and build, and/or user training. It is rarely entirely successful and, IMHO, is never the right solution.

[Ken Puls]

Far too often the first thought of IT departments, and applications developers, is to try to disallow built-in functionality as a substitute for proper design and build, and/or user training. It is rarely entirely successful and, IMHO, is never the right solution.

Tony,

I agree with you on this, however I would say that there can and will be times when a user requires/asks for this type of code. While we may ask why, out of curiosity, it is not our place to judge them on their choice here.

I say put it in the KB. Someone may want it one day, and who knows... maybe the methods there will teach someone else how to just access a comment for a different purpose... or how to use a Worksheet_SelectionChange event... or... Learing is based on seeing something and applying it to your own problems, which most of the time takes some modification anyway.

My 2 cents!

[MWE]

Far too often the first thought of IT departments, and applications developers, is to try to disallow built-in functionality as a substitute for proper design and build, and/or user training. It is rarely entirely successful and, IMHO, is never the right solution.

As the bard said "first we shoot the lawyers ..."; but, I have it on the best of authority that he has subsequently edited that paragraph and it now reads, "... first we shoot the IT folks and then we shoot the lawyers ..."

My approach to appl development is/was to fully engage the users during the development process so the end product is what they want and works the way they want it to work. I have not always been successful, but the "fun" is figuring out how to "get the job done" with an interface, method, etc., that satisfied the user. Most often, the "comment clutter" was feedback from the users and we would evolve something that did what was practical and efficient.