Never save Sheet1 of my workbook. Or some other solution to my problem?

[Marvin Hlavac]

Happy New Year to all very helpful people in this NewsGroup.

Could someone please help me with this one... What I need is:

Everytime I open my workbook I want in an unprotected cell A1 to have a
formula. As an example let us use formula =SUM(1,1)

Again, that cell is unprotected and I would like to be able to enter to that
cell anything but when the workbook is saved, closed and reopened I would
like in cell A1 to have again the formula even when there was something else
before the workbook was saved.

If that is difficult so achieve, would it perhaps be possible to set it up
in such a way that Sheet1 of this workbook would never be saved when I save
my workbook?

 

[Don Guillett]

Why not have a workbook_open event that places the formula?

 

[Marvin Hlavac]

Why not have a workbook_open event that places the formula?



Then it *is* possible. Great!!! Would you help me with that Don? I promise I
will start learning a little bit more about all this in 2004 ;-)

 

[John Wilson]

Marvin,

Go to the VBA Editor (Alt + F11)
Double click on the "ThisWorkbook" module in the
VBA Project Window.
Copy the following code in the code panel on the right:

Private Sub Workbook_Open()
Worksheets("Sheet1").Range("A1").Formula = "=Sum(1,1)"
End Sub

John

 

[Marvin Hlavac]

John & Don,

You are the greatest! It works, it does exactly what I needed! Thanks both a
million!

 

[omar88]

Hi John,

I just found your thread. I am trying to do a similar thing to Marvin.
The only difference is I would like to keep the formula of all the range : A1:A99 rather than just A1, upon closing the spreadsheet.

Also, the formula in: A1 is =IF(A2="","",L2)
and the formula in A2 is =IF(A3="","",L3)
and the formula in A3 is = IF (A4="","",L4)
and so the pattern goes on.

Could you please help me get the correct VBA code?

Thanks heaps
Omar Hanouni
Sydney, Australia