New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

MS Excel 2010 - cannot access and interact with form controls placed on the sheet as macros #4953

Closed
nvaccessAuto opened this Issue Feb 26, 2015 · 22 comments

Comments

Projects
None yet
6 participants
@nvaccessAuto

nvaccessAuto commented Feb 26, 2015

Reported by manish on 2015-02-26 23:55
Excel allows buttons and probably other form controls to be placed on sheets that can be used to trigger macros or user defined functions in the sheet. There is no way in NVDA currently to access these.

@nvaccessAuto

This comment has been minimized.

Show comment
Hide comment
@nvaccessAuto

nvaccessAuto Mar 31, 2015

Attachment Print-invoice.xls added by siddhartha_iitd on 2015-03-31 11:10
Description:
Sample File containing Button Form Controls

nvaccessAuto commented Mar 31, 2015

Attachment Print-invoice.xls added by siddhartha_iitd on 2015-03-31 11:10
Description:
Sample File containing Button Form Controls

@nvaccessAuto

This comment has been minimized.

Show comment
Hide comment
@nvaccessAuto

nvaccessAuto Apr 24, 2015

Comment 3 by siddhartha_iitd on 2015-04-24 14:13
I'm able to get the form controls available in a sheet in the List Dialog on pressing NVDA + F7. However, while using key f to navigate them, the focus goes to the first form control only and stays there. Pressing key f again doesn't move the focus to the next form control(s).
I checked the items collection in iterate() method of class ExcelQuicknavIterator in excel.py. All the form controls are coming in the items collection.
Can you please take a look at the code and help me find the problem ? It's available in branch in_t4953_bm at following url:
​​​​https://bitbucket.org/manish_agrawal/nvda
I've also attached the excel sheet I'm using to test this functionality.

nvaccessAuto commented Apr 24, 2015

Comment 3 by siddhartha_iitd on 2015-04-24 14:13
I'm able to get the form controls available in a sheet in the List Dialog on pressing NVDA + F7. However, while using key f to navigate them, the focus goes to the first form control only and stays there. Pressing key f again doesn't move the focus to the next form control(s).
I checked the items collection in iterate() method of class ExcelQuicknavIterator in excel.py. All the form controls are coming in the items collection.
Can you please take a look at the code and help me find the problem ? It's available in branch in_t4953_bm at following url:
​​​​https://bitbucket.org/manish_agrawal/nvda
I've also attached the excel sheet I'm using to test this functionality.

@nvaccessAuto

This comment has been minimized.

Show comment
Hide comment
@nvaccessAuto

nvaccessAuto Apr 29, 2015

Comment 4 by siddhartha_iitd on 2015-04-29 06:13
Sorry for the confusion caused due to my mistake. I should have checked the commit before posting here. I've rectified the error as suggested in the call. New build is available in branch in_t4953_bm at following url:
https://bitbucket.org/manish_agrawal/nvda

P.S: This is just a simplistic solution as not all features are implemented yet.

nvaccessAuto commented Apr 29, 2015

Comment 4 by siddhartha_iitd on 2015-04-29 06:13
Sorry for the confusion caused due to my mistake. I should have checked the commit before posting here. I've rectified the error as suggested in the call. New build is available in branch in_t4953_bm at following url:
https://bitbucket.org/manish_agrawal/nvda

P.S: This is just a simplistic solution as not all features are implemented yet.

@nvaccessAuto

This comment has been minimized.

Show comment
Hide comment
@nvaccessAuto

nvaccessAuto Apr 29, 2015

Comment 5 by siddhartha_iitd on 2015-04-29 06:43
Following is a part of the code from class ExcelBrowseModeTreeInterceptor in module excel.py and a query afterwards:

def _iterNodesByType(self,nodeType,direction="next",pos=None):
        ...
        elif nodeType=="formField":
           return FormControlExcelCollectionQuicknavIterator(nodeType, self.rootNVDAObject.excelWorksheetObject, direction, None).iterate()

Why are we calling iterate() method through a new instance of FormControlExcelCollectionQuicknavIterator everytime flow-of-control enters _iterNodesByType? Shouldn't it be that the object-instantiation is done only once and iterate method is called using the same instance?

nvaccessAuto commented Apr 29, 2015

Comment 5 by siddhartha_iitd on 2015-04-29 06:43
Following is a part of the code from class ExcelBrowseModeTreeInterceptor in module excel.py and a query afterwards:

def _iterNodesByType(self,nodeType,direction="next",pos=None):
        ...
        elif nodeType=="formField":
           return FormControlExcelCollectionQuicknavIterator(nodeType, self.rootNVDAObject.excelWorksheetObject, direction, None).iterate()

Why are we calling iterate() method through a new instance of FormControlExcelCollectionQuicknavIterator everytime flow-of-control enters _iterNodesByType? Shouldn't it be that the object-instantiation is done only once and iterate method is called using the same instance?

@nvaccessAuto

This comment has been minimized.

Show comment
Hide comment
@nvaccessAuto

nvaccessAuto Jul 20, 2015

Comment 6 by siddhartha_iitd on 2015-07-20 14:52
Please review the codefix in branch in_t4953_bm at url https://bitbucket.org/manish_agrawal/nvda
Thanks!

nvaccessAuto commented Jul 20, 2015

Comment 6 by siddhartha_iitd on 2015-07-20 14:52
Please review the codefix in branch in_t4953_bm at url https://bitbucket.org/manish_agrawal/nvda
Thanks!

@nvaccessAuto

This comment has been minimized.

Show comment
Hide comment
@nvaccessAuto

nvaccessAuto Jul 21, 2015

Attachment US.xlsx added by siddhartha_iitd on 2015-07-21 10:06
Description:
Updated Test File

nvaccessAuto commented Jul 21, 2015

Attachment US.xlsx added by siddhartha_iitd on 2015-07-21 10:06
Description:
Updated Test File

@nvaccessAuto

This comment has been minimized.

Show comment
Hide comment
@nvaccessAuto

nvaccessAuto Jul 22, 2015

Comment 7 by mdcurran on 2015-07-22 05:07
Work on this is looking good so far.
Moving previous by form field is currently broken. In the iterator method, You need to remove the len(items) call as len does not work on a reverse iterator, nor is it needed later used in the code as far as I can tell. Also, you need to reverse the row comparison logic in the for loop if the direction is previous.

Further work is to start using NVDA objects to represent the form controls. We need to create a new NVDA object class that can wrap an Excel form control object. Name can use title / alternative text etc. Role should be calculated from the shape's formControlType or autoShapeType properties.
ExcelWindow's _getSelection method should return an instance of this NVDAObject if the selection is a form control. The treeInterceptor's selection property also should make use of _getSelection.
The form control NVDA object's parent should be the worksheet. Next and previous should be calculated using zOrderPosition.
MoveTo on the FormControlQuicknavItem should also create an instance of this NVDA object and focus it, after instructing Excel to select the form control (as it is doing now).

nvaccessAuto commented Jul 22, 2015

Comment 7 by mdcurran on 2015-07-22 05:07
Work on this is looking good so far.
Moving previous by form field is currently broken. In the iterator method, You need to remove the len(items) call as len does not work on a reverse iterator, nor is it needed later used in the code as far as I can tell. Also, you need to reverse the row comparison logic in the for loop if the direction is previous.

Further work is to start using NVDA objects to represent the form controls. We need to create a new NVDA object class that can wrap an Excel form control object. Name can use title / alternative text etc. Role should be calculated from the shape's formControlType or autoShapeType properties.
ExcelWindow's _getSelection method should return an instance of this NVDAObject if the selection is a form control. The treeInterceptor's selection property also should make use of _getSelection.
The form control NVDA object's parent should be the worksheet. Next and previous should be calculated using zOrderPosition.
MoveTo on the FormControlQuicknavItem should also create an instance of this NVDA object and focus it, after instructing Excel to select the form control (as it is doing now).

@nvaccessAuto

This comment has been minimized.

Show comment
Hide comment
@nvaccessAuto

nvaccessAuto Aug 21, 2015

Comment 8 by siddhartha_iitd on 2015-08-21 08:15
This is regarding our discussion that knowing the macro name for a form control and then executing it from NVDA, when user performs action on the form control. I couldn't find any associated events for form controls represented through Shape object in Excel.
I'm now planning to use SendMessage or WM_APPCOMMAND to trigger and send mouse click to excel. What would you suggest of this approach ? Is there any other way to solve this ?
Thanks.

nvaccessAuto commented Aug 21, 2015

Comment 8 by siddhartha_iitd on 2015-08-21 08:15
This is regarding our discussion that knowing the macro name for a form control and then executing it from NVDA, when user performs action on the form control. I couldn't find any associated events for form controls represented through Shape object in Excel.
I'm now planning to use SendMessage or WM_APPCOMMAND to trigger and send mouse click to excel. What would you suggest of this approach ? Is there any other way to solve this ?
Thanks.

@nvaccessAuto

This comment has been minimized.

Show comment
Hide comment
@nvaccessAuto

nvaccessAuto Sep 24, 2015

Comment 9 by siddhartha_iitd on 2015-09-24 14:39
Can gainFocus event cause to exit from browse mode ?
I'm redesigning the code to prevent user from entering design mode of form controls. So, form controls will not be selected now. When I navigate using f-key, NVDA moves to(without selection) the first form control nearest to the active cell. On pressing f-key again, it exits browse mode and starts editing the active excel cell.
I thought exiting browse mode should need a call to script_activatePosition.

nvaccessAuto commented Sep 24, 2015

Comment 9 by siddhartha_iitd on 2015-09-24 14:39
Can gainFocus event cause to exit from browse mode ?
I'm redesigning the code to prevent user from entering design mode of form controls. So, form controls will not be selected now. When I navigate using f-key, NVDA moves to(without selection) the first form control nearest to the active cell. On pressing f-key again, it exits browse mode and starts editing the active excel cell.
I thought exiting browse mode should need a call to script_activatePosition.

@nvaccessAuto

This comment has been minimized.

Show comment
Hide comment
@nvaccessAuto

nvaccessAuto Sep 24, 2015

Comment 10 by jteh on 2015-09-24 22:37
I'm guessing your NVDAObject is not being treated as part of the TreeInterceptor. The TreeInterceptor's contains method needs to return True for your objects. Alternatively, if you're generating them on the fly from within the TreeInterceptor, you can probably get away with just doing obj.treeInterceptor = self.

nvaccessAuto commented Sep 24, 2015

Comment 10 by jteh on 2015-09-24 22:37
I'm guessing your NVDAObject is not being treated as part of the TreeInterceptor. The TreeInterceptor's contains method needs to return True for your objects. Alternatively, if you're generating them on the fly from within the TreeInterceptor, you can probably get away with just doing obj.treeInterceptor = self.

@nvaccessAuto

This comment has been minimized.

Show comment
Hide comment
@nvaccessAuto

nvaccessAuto Oct 12, 2015

Comment 11 by siddhartha_iitd on 2015-10-12 05:51
Source Code is available in branch in_t4953_NoSel. Please review the code and let me know if any modifications are required. Thanks.

nvaccessAuto commented Oct 12, 2015

Comment 11 by siddhartha_iitd on 2015-10-12 05:51
Source Code is available in branch in_t4953_NoSel. Please review the code and let me know if any modifications are required. Thanks.

@michaelDCurran

This comment has been minimized.

Show comment
Hide comment
@michaelDCurran

michaelDCurran Nov 16, 2015

Contributor

Review:

  • Merge master and fix conflicts. Because #4952 is in master, Keep the ExcelBrowseModeTreeInterceptor._get_selection property already in master and remove the one from this branch. ExcelFormControlQuickNavIterator.iterate therefore must also be changed to handle the fact that position will now be an ExcelCell or ExcelMergedCell NVDAObject, rather than a raw Excel range object.
  • Buttons, checkboxes and radio buttons do not have to switch to focus mode when activated. They should simply do their action (I.e. activate or check/uncheck), but stay in browse mode.
  • It may be possible to provide a better implementation for listboxes and combo boxes. See the controlFormat property on Shape objects: https://msdn.microsoft.com/en-us/library/office/ff840398.aspx, specifically: linkedCell, listIndex and listFillRange.
Contributor

michaelDCurran commented Nov 16, 2015

Review:

  • Merge master and fix conflicts. Because #4952 is in master, Keep the ExcelBrowseModeTreeInterceptor._get_selection property already in master and remove the one from this branch. ExcelFormControlQuickNavIterator.iterate therefore must also be changed to handle the fact that position will now be an ExcelCell or ExcelMergedCell NVDAObject, rather than a raw Excel range object.
  • Buttons, checkboxes and radio buttons do not have to switch to focus mode when activated. They should simply do their action (I.e. activate or check/uncheck), but stay in browse mode.
  • It may be possible to provide a better implementation for listboxes and combo boxes. See the controlFormat property on Shape objects: https://msdn.microsoft.com/en-us/library/office/ff840398.aspx, specifically: linkedCell, listIndex and listFillRange.
@michaelDCurran

This comment has been minimized.

Show comment
Hide comment
@michaelDCurran

michaelDCurran Jan 5, 2016

Contributor

Further review:

  • When using the US.xlsx file to test form controls in MS Office 2013, if I switch to browse mode, press f until I get to the dropdown, I receive the following error:
ERROR - scriptHandler.executeScript (08:39:17):
error executing script: <bound method ExcelBrowseModeTreeInterceptor.script_nextFormField of <NVDAObjects.window.excel.ExcelBrowseModeTreeInterceptor object at 0x05B81AD0>> with gesture u'f'
Traceback (most recent call last):
  File "scriptHandler.py", line 186, in executeScript
    script(gesture)
  File "browseMode.py", line 272, in <lambda>
    script = lambda self,gesture: self._quickNavScript(gesture, itemType, "next", nextError, readUnit)
  File "browseMode.py", line 255, in _quickNavScript
    item = next(iterFactory(direction, info))
  File "NVDAObjects\window\excel.py", line 1581, in iterate
    item=self.quickNavItemClass(self.itemType,self.document,collectionItem,items,self.treeInterceptorObj)
  File "NVDAObjects\window\excel.py", line 1519, in __init__
    self.nvdaObj=ExcelFormControlDropDown(windowHandle=document.Application.Hwnd,excelWindowObject=document.Application.ActiveWindow,excelFormControlObject=formControlObject)
  File "NVDAObjects\__init__.py", line 69, in __call__
    obj.__init__(**kwargs)
  File "NVDAObjects\window\excel.py", line 1653, in __init__
    self.listRange=excelWindowObject.Application.ActiveSheet.Range(excelFormControlObject.ControlFormat.ListFillRange)
  File "C:\Users\Michael\programming\git\nvda\miscDeps\python\comtypes\client\lazybind.py", line 52, in __call__
    *args)
  File "C:\Users\Michael\programming\git\nvda\miscDeps\python\comtypes\automation.py", line 664, in _invoke
    dp, var, None, argerr)
COMError: (-2147352567, 'Exception occurred.', (None, None, None, 0, None))

Therefore I can not test dropdowns further than this.

Also:

  • As previously pointed out (I think), _activatePosition should not set passthrough to true for buttons, checkboxes and radio buttons, rather they should just be activated, staying in browse mode.
  • ExcelFormControl NVDAObject's role property: the role mapping should be a dictionary rather than many if/elifs.
Contributor

michaelDCurran commented Jan 5, 2016

Further review:

  • When using the US.xlsx file to test form controls in MS Office 2013, if I switch to browse mode, press f until I get to the dropdown, I receive the following error:
ERROR - scriptHandler.executeScript (08:39:17):
error executing script: <bound method ExcelBrowseModeTreeInterceptor.script_nextFormField of <NVDAObjects.window.excel.ExcelBrowseModeTreeInterceptor object at 0x05B81AD0>> with gesture u'f'
Traceback (most recent call last):
  File "scriptHandler.py", line 186, in executeScript
    script(gesture)
  File "browseMode.py", line 272, in <lambda>
    script = lambda self,gesture: self._quickNavScript(gesture, itemType, "next", nextError, readUnit)
  File "browseMode.py", line 255, in _quickNavScript
    item = next(iterFactory(direction, info))
  File "NVDAObjects\window\excel.py", line 1581, in iterate
    item=self.quickNavItemClass(self.itemType,self.document,collectionItem,items,self.treeInterceptorObj)
  File "NVDAObjects\window\excel.py", line 1519, in __init__
    self.nvdaObj=ExcelFormControlDropDown(windowHandle=document.Application.Hwnd,excelWindowObject=document.Application.ActiveWindow,excelFormControlObject=formControlObject)
  File "NVDAObjects\__init__.py", line 69, in __call__
    obj.__init__(**kwargs)
  File "NVDAObjects\window\excel.py", line 1653, in __init__
    self.listRange=excelWindowObject.Application.ActiveSheet.Range(excelFormControlObject.ControlFormat.ListFillRange)
  File "C:\Users\Michael\programming\git\nvda\miscDeps\python\comtypes\client\lazybind.py", line 52, in __call__
    *args)
  File "C:\Users\Michael\programming\git\nvda\miscDeps\python\comtypes\automation.py", line 664, in _invoke
    dp, var, None, argerr)
COMError: (-2147352567, 'Exception occurred.', (None, None, None, 0, None))

Therefore I can not test dropdowns further than this.

Also:

  • As previously pointed out (I think), _activatePosition should not set passthrough to true for buttons, checkboxes and radio buttons, rather they should just be activated, staying in browse mode.
  • ExcelFormControl NVDAObject's role property: the role mapping should be a dictionary rather than many if/elifs.
@siddhartha-iitd

This comment has been minimized.

Show comment
Hide comment
@siddhartha-iitd

siddhartha-iitd Jan 7, 2016

Excel Form Controls Test Sheet
t4953.xlsx

siddhartha-iitd commented Jan 7, 2016

Excel Form Controls Test Sheet
t4953.xlsx

@siddhartha-iitd

This comment has been minimized.

Show comment
Hide comment
@siddhartha-iitd

siddhartha-iitd Jan 8, 2016

Thanks for the code review!
I have made changes suggested in the review. Document t4953.xlsx, attached on this page, contains non-empty ListBox and DropDowns. Please use this document. The updated code is available in branch in_t4953_NEW at URL: https://github.com/nvda-india/nvda/tree/in_t4953_NEW

siddhartha-iitd commented Jan 8, 2016

Thanks for the code review!
I have made changes suggested in the review. Document t4953.xlsx, attached on this page, contains non-empty ListBox and DropDowns. Please use this document. The updated code is available in branch in_t4953_NEW at URL: https://github.com/nvda-india/nvda/tree/in_t4953_NEW

@nvaccessAuto

This comment has been minimized.

Show comment
Hide comment
@nvaccessAuto

nvaccessAuto commented Apr 12, 2016

Incubated in f5a030c.

@jcsteh

This comment has been minimized.

Show comment
Hide comment
@jcsteh

jcsteh May 6, 2016

Contributor

A few questions:

  • In ExcelFormControl, _roleMap is defined inside the constructor rather than at class level. This means a separate copy gets created for every instance. Is there a reason for this? I think it should instead be defined at class level.
  • Can you explain why xlEditBox is mapped to a new role ROLE_EDITBOX rather than the usual ROLE_EDITABLETEXT?
  • ROLE_EDITBOX is missing a translators comment in controlTypes.roleLabels. However, the previous point might mean this gets removed entirely.
  • Is there any need for the new controlTypes.ROLE_LISTBOX? controlTypes.ROLE_LIST is the correct mapping for list boxes and that seems to be correct now for Excel, so it seems this is an unused role.
Contributor

jcsteh commented May 6, 2016

A few questions:

  • In ExcelFormControl, _roleMap is defined inside the constructor rather than at class level. This means a separate copy gets created for every instance. Is there a reason for this? I think it should instead be defined at class level.
  • Can you explain why xlEditBox is mapped to a new role ROLE_EDITBOX rather than the usual ROLE_EDITABLETEXT?
  • ROLE_EDITBOX is missing a translators comment in controlTypes.roleLabels. However, the previous point might mean this gets removed entirely.
  • Is there any need for the new controlTypes.ROLE_LISTBOX? controlTypes.ROLE_LIST is the correct mapping for list boxes and that seems to be correct now for Excel, so it seems this is an unused role.

@jcsteh jcsteh added this to the 2016.3 milestone May 6, 2016

@nvaccessAuto

This comment has been minimized.

Show comment
Hide comment
@nvaccessAuto

nvaccessAuto commented Jun 24, 2016

Incubated in 3209e91.

@jcsteh jcsteh assigned michaelDCurran and unassigned jcsteh Jun 24, 2016

@nvaccessAuto

This comment has been minimized.

Show comment
Hide comment
@nvaccessAuto

nvaccessAuto commented Jun 24, 2016

Incubated in 7bacec8.

@nvaccessAuto nvaccessAuto removed the incubating label Aug 8, 2016

@jcsteh jcsteh closed this in 0bd2550 Aug 8, 2016

@leonardder

This comment has been minimized.

Show comment
Hide comment
@leonardder

leonardder Aug 9, 2016

Collaborator

I assume that form fields in Word are now a little step forward?

Collaborator

leonardder commented Aug 9, 2016

I assume that form fields in Word are now a little step forward?

@michaelDCurran

This comment has been minimized.

Show comment
Hide comment
@michaelDCurran

michaelDCurran Aug 9, 2016

Contributor

What do you mean? the Excel form fields code has nothing to do with form
fields in Word...

On 9/08/2016 10:22 PM, Leonard de Ruijter wrote:

I assume that form fields in Word are now a little step forward?


You are receiving this because you were assigned.
Reply to this email directly, view it on GitHub
#4953 (comment),
or mute the thread
https://github.com/notifications/unsubscribe-auth/ANf5nUHsBplD9rz21J3SgRh--Qy6c4Gyks5qeHDygaJpZM4GivnN.

Michael Curran
Executive Director, NV Access Limited
Phone: +61 7 3149 3306
Website: http://www.nvaccess.org/
Twitter: @nvaccess
Facebook: http://www.facebook.com/NVAccess

Contributor

michaelDCurran commented Aug 9, 2016

What do you mean? the Excel form fields code has nothing to do with form
fields in Word...

On 9/08/2016 10:22 PM, Leonard de Ruijter wrote:

I assume that form fields in Word are now a little step forward?


You are receiving this because you were assigned.
Reply to this email directly, view it on GitHub
#4953 (comment),
or mute the thread
https://github.com/notifications/unsubscribe-auth/ANf5nUHsBplD9rz21J3SgRh--Qy6c4Gyks5qeHDygaJpZM4GivnN.

Michael Curran
Executive Director, NV Access Limited
Phone: +61 7 3149 3306
Website: http://www.nvaccess.org/
Twitter: @nvaccess
Facebook: http://www.facebook.com/NVAccess

@sidnc86

This comment has been minimized.

Show comment
Hide comment
@sidnc86

sidnc86 Jul 4, 2017

What if we need to access shapes embedded in a sheet having macros assigned to them? Are those available from NVDA? I don't see them appearing in form field list. Nor can I browse to those using browse mode and pressing "f".

sidnc86 commented Jul 4, 2017

What if we need to access shapes embedded in a sheet having macros assigned to them? Are those available from NVDA? I don't see them appearing in form field list. Nor can I browse to those using browse mode and pressing "f".

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment