XL:Unable to View or Retrieve Cell Note on Protected Worksheet

• Microsoft Excel for Windows 95, version 7.0
• Microsoft Excel for Windows, version 3.0, 4.0, 5.0, 5.0c
• Microsoft Excel for the Macintosh, version 3.0, 4.0, 5.0, 5.0a
• Microsoft Excel for Power Macintosh, version 5.0, 5.0a
• Microsoft Excel for NT, version 5.0

SYMPTOMS

In Microsoft Excel, on a protected sheet that has cell notes, you may experience any of the following behaviors:

• When you click Notes on the Insert menu, you may be unable to see all of the cell notes on the sheet.

  -or-

• When you attempt to retrieve the cell note text with a macro that uses the GET.NOTE function (Microsoft Excel 4.0 macro code), you may receive the following error value:

        #VALUE
  

  -or-

• When you attempt to retrieve the cell note text with a macro that uses the NoteText method (Visual Basic for Applications), you may receive the following error code:

        Error 2402
  

CAUSE

This behavior occurs only when you have cells on a protected worksheet that have the Hidden cell protection option set.

WORKAROUND

Method 1

Clear the Hidden cell protection check box for all the cells from which you want to retrieve cell notes.

If you are using Microsoft Excel version 5.0 or 7.0

1. On the Tools menu, point to Protection, and then click Unprotect Sheet.

2. On the Format menu, click Cells.

3. In the Format Cells dialog box, click the Protection tab, clear the Hidden check box, and then click OK.

If you are using Microsoft Excel version 4.0 or earlier

1. On the Options menu, click Unprotect Document.

2. On the Format menu, click Cell Protection.

3. In the dialog box, clear the Hidden check box, and then click OK.

Method 2

If you are using a macro to retrieve the cell note text, then you may want the macro to un-protect the worksheet and then re-protect the worksheet after the text is retrieved. The following sample code illustrates how you can do this.

Microsoft Visual Basic for Applications Example

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact the Microsoft fee-based consulting line at (800) 936-5200. For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

   http://www.microsoft.com/support/supportnet/refguide/default.asp

   Sub GetCellNoteText()
      ActiveSheet.Unprotect          ' Unprotect the active sheet.
      cellnote = ActiveCell.NoteText ' Retrieve the Note of active cell.
      ActiveSheet.Protect            ' Protect the active sheet.
      MsgBox cellnote                ' Display retrieved cell note text.
   End Sub

Microsoft Excel 4.0 (XLM) Macro Example

   A1: GetCellNote
   A2: =PROTECT.DOCUMENT()
   A3: =GET.NOTE()
   A4: =PROTECT.DOCUMENT()
   A5: =ALERT(A3)
   A6: =RETURN()

Explanation of Microsoft Excel 4.0 (XLM) Macro

   A1: Macro name
   A2: Unprotects the active document (assuming no password)
   A3: Retrieves the cell note of the active cell
   A4: Protects the active document with no password
   A5: Display the retrieved cell note
   A6: End of the macro

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION

Similar to the Lock cell protection option, the Hidden cell protection option is active only when a worksheet is protected. It is used to prevent formulas from being displayed on the formula bar while a worksheet is protected.