Show comment indicator and comment: indicatorType = xlCommentAndIndicatorĭisplaying and printing comments on a separate page Show comment indicator: indicatorType = xlCommentIndicatorOnly To apply the other settings change this line of code: indicatorType = xlNoIndicator 'Create variables Dim indicatorType As Long 'Set variablesĪpplication.Displa圜ommentIndicator = indicatorType The following macro will hide the comment and indicator. The comment is hidden but the indicator (i.e., the red triangle) is visible.'Do something to each cell 'Use c to reference each cell Next cĬomment display settings can be changed to one of three options: Sub LoopThroughAllCellsWithCommentsWorkbook()
#Excel 2016 show comments when printing code
The following expands on the code above and loops through all cells in the workbook which have comments attached. 'Do somthing to each cell 'Use c to reference each cell Next cĮnd Sub Loop through all cells with comments in the workbook 'Loop through each comment on worksheet For Each c In ws.Cells.SpecialCells(xlCellTypeComments)
Sub LoopThroughAllCellsWithCommentsWorksheet() The code below will loop through each cell in the active worksheet which contains a comment. Where methods must be applied to each cell individually, it is necessary to loop through them one-by-one. Ws.Cells.SpecialCells(xlCellTypeComments).SelectĮnd Sub Loop through all cells with comments in the worksheet report this adSelect all the cells with commentsĮxcel provides a method to select all the cells with comments, which is used in the macro below. The following examples specifically relate to those types of cells. When thinking about comments, we also need to consider the cell to which the comment is attached. 'Loop through each worksheet For Each ws In wb.Worksheets The following expands on the code above and loops through all the comments in the active workbook. to reference each comment Next comĮnd Sub Loop through all comments in the workbook 'Loop through each comment on worksheet For Each com In ws.Comments The following VBA code will loop through all the comments in the active worksheet. Loop through all comments in the worksheet But, other actions must be applied individually by looping through each of the comments one-by-one. Some actions can be applied to all comments at the same time (such as clearing them). The following macro will clear the comments from a specified set of cells (A1-B20 of the active sheet) in this example. The following macro will clear the existing comments from the worksheet. On Error GoTo 0 'If comment exists then display comment If commentText "" ThenĮnd If End Sub Clear comments from a worksheet The following code will capture text from a comment and display it within a message box.
The following code will insert the text “Insert my comment” as a comment attached to the active cell. On Error GoTo 0 'If comment exists then display message box If commentText "" Then If error then comment does not exist On Error Resume Next Sub TestCellForComment()ĭim commentText As String 'Set variables Set c = ActiveCell If so, it will output a message box to state that a comment exists. The following code will check if the active cell has a comment. This first group of macros feature some of the basic actions you may include as part of a longer procedure. By changing those variables, the code can easily be changed and combined with other code snippets to meet your specific requirements. Every code snippet uses variables to hold either the worksheet, workbook comment, cell or a setting value. It is unlikely that any of the code below will meet your exact requirements. Whatever your preference, you can control them with VBA add, delete, list on a worksheet – it can all be done with VBA. Other users despise them as they are unsightly and annoying. Some Excel users love comments they put comments all over their workbooks.
0 kommentar(er)
