cell formula to change value by one

[Alan R]

excel 2003 in ( I9 ) there is a value 1000
I want to be be able to + 1 on close can anyone help

 

[Cimjet]

Hi Alan
This will do it...
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Range("i9").Value = Range("i9").Value + 1
End Sub
If you don't know where to place it.select the small XL icon to the left of the
File menu on top, right click select View Code. that should bring you to the VBA
editor in the Workbook Event module.
Just paste the macro on the big white page.
HTH
Cimjet

 

[Cimjet]

Hi again
The problem with the macro I gave you is that it will always increment by 1 at
closing. It's your choice but how about a macro that Ask you at opening if you
want to increment the number, then you can chose yes or no.
In any case here it is...
Don't use both delete one but they go at the same place.
Private Sub Workbook_Open()
answer = MsgBox("Do you want to Update Invoice number ?", vbYesNo)
If answer = vbNo Then Exit Sub
Range("i9").Value = Range("i9").Value + 1
ActiveWorkbook.Save
End Sub
HTH
Cimjet

 

[Don Guillett]

Hi again
The problem with the macro I gave you is that it will always increment by1 at
closing. It's your choice but how about a macro that Ask you at opening if you
want to increment the number, then you can chose yes or no.
In any case here it is...
Don't use both delete one but they go at the same place.
Private Sub Workbook_Open()
answer = MsgBox("Do you want to Update Invoice number ?", vbYesNo)
    If answer = vbNo Then Exit Sub
Range("i9").Value = Range("i9").Value + 1
ActiveWorkbook.Save
End Sub
HTH
Cimjet

You may also want to specify the SHEET

Private Sub Workbook_Open()
answer = MsgBox("Do you want to Update Invoice number ?", vbYesNo)
    If answer = vbNo Then Exit Sub

with sheets("sheet1"). Range("i9")
.Value = .Value + 1
end with

ActiveWorkbook.Save
End Sub

 

[Cimjet]

Hi Don
Hope you're doing well.
Thank you for the tip, it's not always sheet1 that the O.P. would use so, it's a
very good idea to specify the sheet name.
Thank you Don
Regards
Cimjet

Hi again
The problem with the macro I gave you is that it will always increment by 1 at
closing. It's your choice but how about a macro that Ask you at opening if you
want to increment the number, then you can chose yes or no.
In any case here it is...
Don't use both delete one but they go at the same place.
Private Sub Workbook_Open()
answer = MsgBox("Do you want to Update Invoice number ?", vbYesNo)
If answer = vbNo Then Exit Sub
Range("i9").Value = Range("i9").Value + 1
ActiveWorkbook.Save
End Sub
HTH
Cimjet

You may also want to specify the SHEET

Private Sub Workbook_Open()
answer = MsgBox("Do you want to Update Invoice number ?", vbYesNo)
If answer = vbNo Then Exit Sub

with sheets("sheet1"). Range("i9")
.Value = .Value + 1
end with

ActiveWorkbook.Save
End Sub