An Excel workbook in a SAP InPlace view (or embedded in within a window) stopped working, displaying a run-time 1004 error: Method 'Sheets' of object '_Global' failed
. The problem was caused by a UserForm dialog in that workbook which the user can open to enter a value. The code in the dialog then filtered the data in all the sheets based on the user's input. The code failed when it tried to call the Sheets() method.
The Sheets() is a short-cut for Application.ActiveWorkbook.Sheets(), so when a workbook is embedded in a view and loses focus (such as when the dialog was opened), Application.ActiveWorkbook doesn't have a valid value so the Sheets() method fails. When the workbook is opened in an Excel window, there is always an active workbook (and a valid ActiveWorkbook value).
The VBA code was part of the workbook, not in a separate add-in file, so a solution was to use ThisWorkbook.Sheets(). Another solution could be to use Application.Workbooks(1).Activate.
There is also an Application.IsInPlace property which returns TRUE if Excel is embedded within another window.
2010-05-20: Hm ... I could have also used ThisWorkbook.Activate to ensure that there was always an active workbook.
No comments:
Post a Comment