UltraSleuth FAQ

1.1  Excel behaves strangely when I click on a hyperlink in a report
Excel 97 users may experience strange behavior when clicking on a hyperlink. For example:

1. you may be prompted to save the workbook containing the hyperlink
2. the workbook containing the hyperlink may appear to close after the hyperlink is clicked

With regard to i, if you save the workbook when prompted, you should not be asked again.

With regard to ii, the workbook has not been closed. It has been hidden from view. To unhide the workbook:

1. Open the Window menu from Excel’s main menu
2. Click on Unhide
3. When the "Unhide" dialog box opens, select the workbook from the list of hidden windows
4. Click on OK

1.2  When I click on a hyperlink in a report I am taken to a different worksheet
This happens when the target worksheet is hidden. You will need to unhide the worksheet. Follow these steps to unhide the worksheet.

1. Select the workbook containing the worksheet that you want to unhide
2. Next open the Format menu from Excel’s main menu
3. Click on Sheet and then on Unhide…
4. Select the sheet that you want to unhide from the Unhide sheet list in the Unhide dialog box
5. Click on OK

1.3  The double click feature in an UltraSleuth report does not work.
Nothing happens when you double click. There are two reasons for this:

1. You double clicked on a cell that doesn’t contain a cell address
2. Your security settings prevent macros from running

With regard to i, to activate the double click feature you must double click on a cell that contains a cell address.

With regard to ii, change your security settings to a lower level:

1. Open the Tools menu from Excel’s main menu
2. Click on Macro and then on Security …
3. When the "Security" dialog box opens, click on the "Security Level" tab
4. Select a lower security setting
5. Click on OK

1.4  The double click feature in an UltraSleuth report jumps to the wrong location.
When you double click you jump to a cell on the current worksheet instead of the cell containing the target cell.

This problem arises when you double click on a cell border. Ensure the cursor is clearly inside the cell before you double click.

1.5  The double click feature in an UltraSleuth report jumps to the wrong cell in the target worksheet.
Later versions of Excel allow the owner of the spreadsheet to prevent the selection of locked cells when a worksheet is protected. When a user is not allowed to select locked cells in a protected worksheet, the double click feature will jump to the first unlocked cell in that worksheet. To resolve this issue, the worksheet should be unprotected. Follow these steps to unprotect the worksheet:

1. Select the worksheet that you want to unprotect
2. Next open the Tools menu from Excel’s main menu
3. Click on Protection and then on Unprotect Sheet…
4. The Unprotect Sheet dialog box opens if a password is required. Type in your password.
5. Click on OK

1.6  AdvancedSearch CopyFinder, InputFinder and Summarizer do not correctly handle "long" formulas.
Due to an Excel limitation, UltraSleuth cannot read long formulas in full. UltraSleuth may not be able to read formulas that are longer than 1024 characters in R1C1 format. This has implications for some features:

1. InputFinder might not detect (or falsely detect) some input cells
2. CopyFinder might falsely identify some formulas as being copied
3. Summarizer might not list some formulas correctly
4. AdvancedSearch might not find the search string in some formulas

We strongly suggest you break up long formulas into smaller ones. You can do this by using names or by using a series of cells to do intermediate calculations before assembling the result. This will help UltraSleuth and has the added benefit of making your spreadsheets easier to understand and debug.

1.7  UltraSleuth's results are obscured by formatting.
Some UltraSleuth features (such as InputFinder and CopyFinder) "mark up" your spreadsheets. Unfortunately some spreadsheet formatting can hide or obscure the marked up cells. For example, hidden rows or columns might prevent you from seeing an input cell or a copied formula. Also, merged cells can obscure the border colors that CopyFinder uses.

To ensure that you do not miss obscured results, we suggest you unhide all rows/columns and unmerge cells before running UltraSleuth.

1.8  How do I turn off the nag screens?
If you bought UltraSleuth, please ensure that you've entered the serial number that came with the registered version of UltraSleuth. To enter the new serial number click on UltraSleuth in the menu, then click on Register. The UltraSleuth Registration dialog box will open. Enter your serial number into the text box and then click OK. UltraSleuth should now be properly registered and the nag screens should no longer appear.

3.1  Why do worksheets and workbooks have to be unprotected?
InputFinder reads formulas on worksheets and may need to color some cells. It is not possible to do this if the worksheet is protected. Formulas on the worksheet might have precedents on other worksheets. In this case, it may be necessary to read formulas and color cells on other worksheets. This is only possible if worksheets and workbooks are unprotected.

InputFinder checks that external sheets are unprotected before the precedent tracing process begins.

3.2  Can InputFinder automatically unprotect worksheets and workbooks?
Yes. If InputFinder finds that an open worksheet or workbook is protected then it will open the ProtectionRemover dialog box. You will need to click on the check box to indicate that you have permission from the spreadsheet author to unprotect the worksheets and workbooks and enable the OK button. Click on the OK button to remove worksheet and workbook protection. If any workbook or worksheet is password protected, the password will be removed permanently.

3.3  I pressed the "Undo InputFinder" button but some cells remained highlighted. Why did this happen?
InputFinder remembers the positions of input cells and then uses this information during the undo process. If in the interim you have inserted or deleted cells then the positions of some input cells might have changed. This can interfere with the removal of cell highlighting because InputFinder has no way of knowing that cell positions have changed.

3.4  I pressed the "Undo InputFinder" button but my original cell colors were not reinstated. Is this normal?
InputFinder clears the color from cells rather than returning those cells to their original color.

For reasons of efficiency, InputFinder does not look at any worksheet's initial coloring scheme. This allows InputFinder to run as quickly as possible but it means that original colors cannot be reinstated.

3.5  Input cells in some external workbooks seem to be highlighted with slightly different colors. Why is this?
Workbooks have a color palette containing 56 user selectable colors. It is possible that the color you have chosen to use for highlighting does not appear in some workbook palettes. In this case, InputFinder uses the nearest matching color.

3.6  Can InputFinder find input cells in an external workbook which isn't open?
No. InputFinder can find input cells in external workbooks but only if the external workbooks are open. If the external workbooks are not open, InputFinder will not detect the external input cells and no warning will be given.

You should ensure that all linked workbooks are open before running InputFinder. You can obtain a list of linked workbooks by selecting Edit then Links... from Excel's main menu.

4.1  Why does the active sheet have to be unprotected?
CopyFinder reads formulas on the active sheet and may need to color some cells on that sheet. It is not possible to do this if the worksheet is protected.

CopyFinder checks that the active sheet is unprotected before scanning for copied formulas.

4.2  Can CopyFinder automatically unprotect the active sheet?
Yes. If CopyFinder finds that the active sheet is protected then it will open the ProtectionRemover dialog box. You will need to click on the check box to indicate that you have permission from the spreadsheet author to unprotect the worksheets and workbooks and enable the OK button. Click on the OK button to remove worksheet and workbook protection. If any workbook or worksheet is password protected, the password will be removed permanently.

4.3  I pressed the "undo copy coloring" button but some cells remained highlighted. Why did this happen?
CopyFinder remembers the positions of copied formulas and then uses this information during the undo process. If in the interim you have inserted or deleted cells then the positions of some formulas might have changed. This can interfere with the removal of cell highlighting because CopyFinder has no way of knowing that cell positions have changed.

4.4  I pressed the "undo copy coloring" button but my original cell colors were not reinstated. Is this normal?
CopyFinder clears the color from cells rather than returning those cells to their original color.

For reasons of efficiency, CopyFinder does not look at the active worksheet's initial coloring scheme. This allows CopyFinder to run as quickly as possible but it means that original colors cannot be reinstated.

5.1  Summarizer does not detect VBA code in the active workbook.
The active workbook contains VBA code but the "Procedures" worksheet does not list your VBA subroutines. There are two reasons for this problem:

1. Your VBA code has been protected
2. You are running Excel XP or later and your security settings disallow access to VBA code

With regard to i, unprotect your VBA code and re-run Summarizer.

With regard to ii, change your security settings so that Summarizer can access your VBA code. Follow these steps to change your security settings:

1. Open the Tools menu from Excel’s main menu
2. Click on Macro and then on Security …
3. When the "Security" dialog box opens, click on the "Trusted Sources" tab
4. Check the "Trust access to Visual Basic project" check box
5. Click on OK

6.1  Are worksheet and workbook passwords removed permanently?
Yes. Provided you have permission from the spreadsheet author to remove protection, this method is the same as manually unprotecting worksheets and workbooks that have passwords.