How to suppress all code in an Excel Workbook

[C2345]

We have a workbook which when opened, shows the user critical data on a given page and then erases it. Opening the VBA editor in the workbook shows no code in the form of modules nor coded behind the worksheets. There are no forms in the workbook. I have tried to open the workbook using code from another workbook so that any macros would be suppressed (there are none) and have used the setting: application.enablevents = False to try to suppress stop any code as well as putting the macro security setting to very high.

Nothing seems to stop the code. Is there a way to prevent this code from running?

Alternatively, is there a way to extract the data from the worksheets. I've tried to pull the data out with Access but Access said the file couldn't be read. Perhaps the data was encrypted?

 

[PHV]

Anyway, did you try to open the workbook with automatic calculation disabled ?

 

[combo]

Concerning the original file:
- what sheets are protected (initially you told that all)?
- what sheets are visible (D1, D2 hidden CData visible)?

If all sheets are protected, how do you know that there is no conditional formatting or formulas in CData? (My code allows extracting formulas only.)

Basing on your previous posts, it seems that D1 stores full data and cells in D2 conditionally repeat contents of respective cells in D1 or are set to empty (by formulas).

So, using my formulas in external workbook:
- what is in D1: formulas or values?
- what is in CData, invisible cells: formulas/values/nothing?

If You have an access to CData (non-protected), select header, display pivot table toolbar and external data toolbar. Is one of them active?

combo

 

[C2345]

Combo/PHV:

The workbook can be opened up with the calculation set to Manual and the data are not erased as you indicated.


As for the formatting - it gets curiouser and curiouser. In the workbook all worksheets are protected and only CData is visible. I can place a cursor in a cell in CData and oddly, the conditional formatting option off the menu is not grayed out and no formatting is shown. This was the basis of my comment that there was no conditional formatting. What is odd is that when I take a blank workbook, fill in a range, protect the worksheet and select an item in the filled out range, the conditional formatting item on the menu is grayed out.

As for the contents of the worksheets, CData and D1 contain only data (with the exception of the two cells holding the =Now() and =DATE(2008,4,1)formulas) and D2 contains all formulas.

Although the CData is protected I could display the External Data and PivotTable tool bars. Neither toolbar was active. The external data option seems unlikely as the dissappearance of the data occurs even when there is no link to the internet.

 

[PHV]

the data are not erased
So, where is the problem ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[xlbo]

...and you can set protection options that allow various activities to still be allowed....

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[C2345]

The problem at this point is that I can't figure out how the data is being erased. It's true that the original problem has been solved -- initially by the observation that by setting the system clock back to 2007 the date weren't erased.

What I don't see is how a workbook with no code in it and no conditional formatting - (the protection didn't prohibit conditional formatting as that menu option wasn't grayed out and all cells showed no conditional formatting on the CData worksheet) - can erase the cells on a worksheet with no formulas on it. All the mechanisms proposed for having data dissappear involve either code, conditional formatting, hiding rows or worksheets don't seem to be happening.

 

[combo]

Another test:
- open the worksheet (with recalculation, data should dysappear),
- change system time,
- recalculate workbook (F9 key).

Will the data appear again?

Concerning rights in protected worksheet, you can allow formatting cells and some other actions. If the conditional formatting was applied, select cell that contents disappeared and check 'conditional formatting'. There shouldn't be any formula (if font colour was set to blank, you can't see it in the dialog).

combo

 

[C2345]

Thanks combo:

The recalculation status set to manual the data dissappears and reappears on CData depending on whether the system date is after or before 4/1/2008 when the calculate (F9) key is pressed.

Checking the cells where the data dissappeared however fails to show any conditional formatting. As you indicated there was no formula. This is not unexpected as the previous work indicated that the worksheet CData has no formulas on it -- only data. Furthermore, the conditional formatting box indicates that there was also no format set. Are you still thinking that there might be conditional formatting that is causing the dissappearance of the data?

 

[combo]

A question about protection of CData sheet: in any state select a cell where the data disappears/reappears. What can you see in the formula bar: hard value, formula or nothing?

Make sure there is no conditional formatting: check this option and delete all three conditions.

If you have single column and there are empty columns on the left, check cells on the left for data/conditional formatting.

combo

 

[DaveInIowa]

All that the code is doing is erasing the existing data and leaving the name of the employee in it's place. A parting gift from the disgruntled employee.

I'm not too sure how this suggestion is going to go over with the members here but, given that you've found a kill date (4/1/2008) and the spreadsheet is leaving the name of the employee, have you considered having your legal department contact this employee? It may be that this isn't the only document they've "booby-trapped".

 

[C2345]

DaveInIowa/Combo:

The legal department has contacted the employee.

When the cell is placed in a cell on the CData worksheet nothing appears in the formula bar -- even though text is showing in the cell. However, going to the VB Editor and typing ?ActiveCell.value or ?ActiveCell.formula yields text for the first and shows an error for the second.


CData has values in the range $A$1:$M$7651. Spot checking several of them shows no conditional formatting for any of them. All have values in them which dissappear when the system date is set to later than 4/1/2008l. If the file wasn't 16 megabytes I'd be tempted to post it assuming I could get the owners permission.

 

[DaveInIowa]

Forgive me if I missed this in the posts above. Have you tried Tools > Options, then checking Formulas on the View tab (or keyboard shortcut Ctrl + `) to get an overview of the formulas contained in the spreadsheet?

 

[C2345]

DaveInIowa:

All of the worksheets are protected. We did find out that only sheet D2 had formulas on it but using code. See post of 12/29/08 above for details.

 

[combo]

The CDate worksheet has formulas that refer to other sheets. Cell contents is protected from viewing, worksheet is protected, so in the formula bar you see nothing and ActiveCell.Formula returns error.
The easiest would be removing protection, try to google for 'worksheet protection remove', you will find either code or ready to use stuff.

If it is possible (no big difference between D1 and CData), you can try to get original data. Select whole D1, copy and paste to new workbook. As long as the contents is not hidden, you will get formulas unprotected. Your previous info indicate that D2 and Cdata are used mostly to conditionally hide contents of D1.

combo

 

[GVF]

Just a thought...

Is it possible D2 is an Excel 4.0 macro sheet with a hidden "Autpen" macro?

If: MsgBox Sheets("D2").Type returns a 3 (xlExcel4MacroSheet) or a 4 (xlExcel4IntMacroSheet) then all those formulas are the code.

Greg

 

[C2345]

Thanks Greg:

All worksheets are of type -4167 so it doesn't seem that macro worksheets are involved. I may have to try to crack the password locking the sheets that are protected.

I tried deleting enough rows so that I could put the worksheet on the site but I can't with the worksheet protection on.

 

[GVF]

I know it's driving you nuts there C2345 but this is a good one. Prior to you posting what you find, I think Combo's last post is the ticket. As an excercise I propose the following scenario...

Your buddy renames CData as D1. He adds a new sheet and names it CData and another sheet and names it D2. In each cell in the affected range of the NEW CData he enters
='D1'!RC (actually uses absolute reference) and then locks the cells, hides formulas for the cells, and password protects the sheet.
He enters the Now() and Date() formulas into cells in D1 that are 1 row below the cells that he knows will be affected when the formulas in D2 calculate when the file is opened. The D2 calculations blank the cells in D1 and the cells in CData = their counterparts in D1. He password protects D1 and D2 and sets them to xlVeryHidden.

SO...your data is actually in D1 (as was figured out in the posts above) and there is no code.

Please post your findings. My curiosity is up.

Greg

 

[combo]

In my case there are no problem with worksheet passwords (no need to use external tools) after opening the worksheet in OpenOffice 3, saving in flat OpenDoc format ('fods' extension) and reopening it in oo.

combo

 

[C2345]

gvf/combo:

Unfortunately there is only one sheet with formulas on it, D2 -- see post of 29 Dec 08 14:14 for more complete description of workbook. I managed to use code to get all the data/formulas in the worksheets.

I'm pursuing the approach of using OpenOffice which I don't have but may have a friend who does.

Thanks,

 

[combo]

Unfortunately there is only one sheet with formulas on it, D2

Seems that it can be wrong assumption. When (in cell formatting, protection tab) formulas are set to 'hidden' and sheet protection applied, you can see nothing in the formula bar and value in the cell (your post 7 Jan 09 12:29). Copy&paste returns always values.
The attempt to get gormula by code fails, after '? Activecell.Formula' in immediate window I have run-time error 1004. Seems that you too (8 Jan 09 5:27).
Without unlocking the CData sheet one can't know if it has formulas behind visible values.

combo