Excel: Formulas and comments not shown in Elements list dialog

[nvdaes]

Bug detected:
Is usual find enterprises where the workers must give information to
administration department, using forms created in excel. In order to
make easier the browse through the form using the key “tab”, the sheet
is protected and contains all cells blocked, except the cells in which
the user must fill the information. Every Cell not blocked has a
comment to indicate the data that the user must fill.
If the sheet is protected, the NVDA list of elements (Insert + F7)do
not displays the formulas and comments contained. When user unprotect
the sheet, NVDA can’t read any information contained into that sheet
unprotected, if there are cells blocked. NVDA worgs correctly in other
sheets in the same workbook.
The bub described has been detected using NVDA 2015.3, NVDA 2015.4,
EXCEL 2007 AND Excel 2010.
We attach a XLS file, used to test the bug. The workbook has 5 sheets:
1.- . “Insturucciones”: Contains only text within instructions.
2.- “Gastos”: Is the sheet where the user must fil all the
information. Is protected and contains cells blocked and unblocked.
The user press the tab hey and the focus is moved to the next cell
where the user must fil information. The user press alt+ insert+ c.
Then NVDA read the comment describing the kind of information to put
in this cell. The dialog box “list of elements” do not displays the
list of comments neither the formulas, but displays correctly the
sheets. The user can unprotect the sheet (ribbon, Revision, Portection
tab), then NVDA can read any information. The list of elements (insert
+F7) shows then the formulas, but do not shows the comments. The sheet
is protected without password. The user can protect the sheet again.
NVDA restore then the previous behavior.
3.- “Versión imprimible”, used to print the information filed in the
previos sheet. The list of elements ((insert+F7) shows correctly the
sheets, formulas and comments. The sheet is not protected and do not
contains cells blocked.
4 and 5.- “órdenes de coste” and “centros de coste” are two sheets
containing lists of data useful for the user. The conten of this
sheets has been deleted for privacy reasons. NVDA works correctly in
this two sheets.
The Excel file for testing can be found at
https://www.dropbox.com/s/pjnu6hzs9jhq1m6/Plantilla_Liquidacion_Gastos8.xlsx?dl=0
Thanks.

[jcsteh]

@dineshkaushal, would you or one of your team be able to take a look at this? Thanks.

[michaelDCurran]

The bug where comments are not listed even when the sheet is unprotected is fixed in #5704.
However, to fix listing of comments and formulas for a protected sheet will probably take a bit more work. We may be able to use the comments collection directly, and then get the associated cell from the comment's parent property etc, rather than searching with 'specialCells' which does not seem to work in protected sheets.

[LeonarddeR]

@michaelDCurran: Mind a p3 for this?

[michaelDCurran]

p3 is fine. Looks like we have a rough idea how to fix it.

[Adriani90]

@michaelDCurran is this something which could be solved in connection with the speed up PR?

[Adriani90]

@nvdaes could you test if this is still reproducible with last NVDA Alpha? Thank you very much.

[nvdaes]

Adriani90 commented 8 hours ago

@nvdaes could you test if this is still reproducible with last NVDA Alpha? Thank you very much.

Thanks for this reminder. I don't know when I could test this since I don't have Excel at home and the sample document to try this was removed from Dropbox by the original reporter (I just created the issue).
Please, could someone take care about this? If nobody can, I will try to do it when I can, but I think this should be tested with latest versions of Excel, not 2007 and 2010, which are the versions installed in my job.
What do you think?

[Adriani90]

ok @nvdaes I can reproduce this issue in every excel sheet when protecting and unprotecting it. To make it very fast, the cells containing formulas and / or comments are not shown when the sheet is protected. Formulas and comments are shown correctly in elements list when the sheet is unprotected. I guess this is also the case for all other elements like diagrams etc. This is visible in both Office 2010 and 2016.
cc: @Qchristensen

[Adriani90]

This is the case even if there are regions which are unprotected. So, the whole sheet must be unprotected to get all formulas and comments in the elements list.

cc: @michaelDCurran