How to you quick toggle cell Notes in excel?-CodePudding

I work with excel files that use a lot of "Notes".

I know I can right-click on a cell to toggle note visibility, and I also know the shortcut ALT R T O. However these are cumbersome when I look to toggle notes constantly.

I've added a macro, on Ctrl Q that reads:

Sub ToggleNote()
'
' ToggleNote Macro
'
' Keyboard Shortcut: Ctrl Q
'
    ActiveCell.Comment.Visible = Not ActiveCell.Comment.Visible
End Sub

But there are a few limitations with this:

I receive a new excel every day (by email) and It's cumbersome to convert it to .xlsm & add the macro.
Some of the tool we use in the company blocks .xlsm files making them hard to share.
The macro is a little glitchy if I accidentally use it on a cell with no comment.

Is there a good way to do this that I'm not aware of?

PS. The excel files are written using Python & XlsxWriter - it is feasible to modify the code that creates the excels.

CodePudding user response：

There are two easy commands for this:

' Show all comments/notes
Application.DisplayCommentIndicator = xlCommentAndIndicator
' Hide all comments/notes (but show the indicator)
Application.DisplayCommentIndicator = xlCommentIndicatorOnly

There also is:

' Hide all comments/notes, even the indicator:
Application.DisplayCommentIndicator = xlNoIndicator

But this makes it difficult to see which cells have comments/notes and which not.

CodePudding user response：

You could use an Add In which would have the macro enable for each use of Excel. You can modify this to just happen with certain workbooks etc.

In the workbook_open of the Addin you could have

Private Sub Workbook_Open()
    Application.OnKey "^q","mdlFunctions.DisplayNotes"
End Sub

Then in a module called mdlFunctions in the AddIn, have your toggling code, mine's DisplayNotes(). You may want to check for a comment being present within that code too.

You can just have this add in installed, then workboks won't become macro enabled, but you will have the macro function from the addin.

CodePudding user response：

The excel files are written using Python & XlsxWriter - it is feasible to modify the code that creates the excels.

Yes. It is possible in XlsxWriter to generate the notes/comments as initially visible or hidden, either individually or for all comments in a worksheet. See