Protected Excel File manipulation

[Bee]

My situation:
I am generating an Excel file from an Access database using VB (Access 2003).

Then I send this file to an outside agency.

They are supposed to update some information and send it back.

I have some formulae in the file (columns as well rows).

I would like to lock and hide the formula.

I can't do that if I don't protect the worksheet.

My problem is that the agency should be able to add rows also.

I tried worksheet.protect method and set allowinsertingrows = true

This allows me to insert rows but not copy/paste and does not copy the
formula from the previous row.

I'll Appreciate any suggestions/help.


Thanks in advance,

bee.

 

[excel-ant]

My situation:
I am generating an Excel file from an Access database using VB (Access 2003).

Then I send this file to an outside agency.

They are supposed to update some information and send it back.

I have some formulae in the file (columns as well rows).

I would like to lock and hide the formula.

I can't do that if I don't protect the worksheet.

My problem is that the agency should be able to add rows also.

I tried worksheet.protect method and set allowinsertingrows = true

This allows me to insert rows but not copy/paste and does not copy the
formula from the previous row.

I'll Appreciate any suggestions/help.

Thanks in advance,

bee.

Bee,

Have you thought about block filling enough formulas to cover
If the agency are simply enterring data but need to see the results of
the formulas this should work OK.

Example,

Access produces 15 records, but you populate formulae to 100 rows,
allowing the Agency up to 85 rows to enter source data for new
records?

My only other thought is to turn the workbook into a small application
and use macro's userforms etc to allow the agency to update.

Anthony

 

[Bee]

Anthony,

Thank you for this response.

I thought about allowing some extra blank rows at the end. One agency we
send this to has 150 records at this time but could insert 200 or so rows
easily.

So far my solution is:

1. Unlock the entire file.
2. Lock the header rows (first 15 or so).
3. Lock & Hide the summary column formula (column 35 or so - varies by
month).
(I am using row() and column() in the formula in stead of a row/column
counter)
4. LOck the summary column.
5. Lock & hide the totals row formula. (Varies)
6. Lock the totals row.
7. Protect the file with a password but have allowinsertingrows = true.

This allows the insertion of rows. But the total column value stays blank -

The grand total is calculated correctly however.

This is not the cleanest probably but I'll live with this.
I don't have too much time as I have already spent more time than I was
planning.


Thank you,

Bee.