Incrementing a cell on opening a workbook!

[Steve_B]

Hi All

I am sure that this question has been answered before and I would be grateful if someone could point me in the right direction.

I want to have a workbook that will increment a value in say A1 by 1 each time it opens, this value needs to be recorded even if the workbook is saved under another name.

First time A1 = 1
Second time A1 = 2
Etc, etc

 

[tmischler]

In the workbook open event

Static i As Long
i = i + 1
ActiveWorkbook.ActiveSheet.Range("A1").Value = i

 

[Steve_B]

Thanks for the reply but I must be doing something wrong

Sub Workbook_Open()
Static i As Long
i = i + 1
ActiveWorkbook.ActiveSheet.Range("A1").Value = i
End Sub

When opend cell a1 remains blank

 

[tmischler]

mmm - not sure about that one - anyone else got any thoughts?

 

[tmischler]

Private Sub Workbook_Open()

Static i As Long
i = ActiveWorkbook.ActiveSheet.Range("A1") + 1
ActiveWorkbook.ActiveSheet.Range("A1").Value = i

End Sub
---------------------------------------------------------
STATIC only lasts as long as the code is running. Is there a way of declaring it so that it remembers forever?

 

[just_jon]

Might also want an event macro, bound to watching A1, to make sure no one manually alters it .

Note that all this can be defeated by not enabling macros at open...

 

[Steve_B]

Still no joy!

Im I missing something obvious?

 

[tmischler]

that last one works for me when I tried it. Can someone else try it and see if it works for them?

 

[Smitty]

How's this:

<font face=Tahoma><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_Open()
    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range
        <SPAN style="color:#00007F">Set</SPAN> rng = Sheets("Sheet1").Range("A1")
        
    <SPAN style="color:#007F00">'   Not necessary if there's already a value in A1</SPAN>
    <SPAN style="color:#00007F">If</SPAN> rng.Value = "" <SPAN style="color:#00007F">Then</SPAN>
        rng.Value = 1
        <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    
    <SPAN style="color:#007F00">'   Increment existing value</SPAN>
    rng.Value = rng.Value + 1
    
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Hope that helps,

Smitty

 

[tmischler]

Hi Smitty - I didn't want to specify any sheet names either since I figured that if people might change the name of the workbook, they might change the name of the tab as well - not that that makes my code free from problems either...