Incrementing a cell on opening a workbook!

Steve_B

Board Regular
Joined
Feb 9, 2004
Messages
86
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
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

tmischler

Well-known Member
Joined
Jun 17, 2004
Messages
669
In the workbook open event

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

Steve_B

Board Regular
Joined
Feb 9, 2004
Messages
86
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

Well-known Member
Joined
Jun 17, 2004
Messages
669

ADVERTISEMENT

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

Legend
Joined
Sep 3, 2002
Messages
10,473
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

Board Regular
Joined
Feb 9, 2004
Messages
86

ADVERTISEMENT

Still no joy!

Im I missing something obvious?
 

tmischler

Well-known Member
Joined
Jun 17, 2004
Messages
669
that last one works for me when I tried it. Can someone else try it and see if it works for them?
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
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

Well-known Member
Joined
Jun 17, 2004
Messages
669
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...
 

Forum statistics

Threads
1,147,844
Messages
5,743,510
Members
423,800
Latest member
IuneKeiki

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top