Help required on accounts spreadsheet

ager2001 · Mar 30th, 2004, 03:57 PM

I have an accounts spreadsheet that renews each week. What I am trying to do is to have the accounts serialised, 001, 002, 003 etc on a weekly basis. I want the serial no to increment automatically when spreadsheet is archived.

Andy

Davers · Mar 30th, 2004, 04:10 PM

Hey Andy, is the serial number the name of the Sheet? or a Column heading?...or something else...

Could you post a small example?

Dave

ager2001 · Mar 30th, 2004, 10:16 PM

Dave - the serial number is to be used to avoid fraud on the account - if each time the account is saved the serial number (a formula in a protected cell??) is incremented by 1. That way I can have a trail of all entries onto the account. Several people use the spreadsheet in different locations. Does that explain the problem better

Davers · Mar 31st, 2004, 04:39 AM

Ok...let's see if I got this right...so the number is in a cell, and you want the number to increase by 1 each time you save the workbook without any input by a user? If that's right, let me know...I'm on my Mac at home right now...and I hate Excel on the Mac...it's not the same even though they say it is....

I'll take a look at it tomorrow morning...

Have a good day,

Dave

P.S...and is that every time the workbook is saved...or everytime it's opened?

ager2001 · Mar 31st, 2004, 02:35 PM

Dave - Yes that is what I am trying to do. Increment the number everytime the spreadsheet is closed.

Andy

Davers · Mar 31st, 2004, 03:56 PM

This should do the trick...copy and paste this into the "Thisworkbook" object...

Code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Worksheets("Sheet1").Range("A1") = Range("A1") + 1
    ActiveWorkbook.Save
End Sub

This assumes your number is in cell A1 on Sheet1...

Hope this helps and have a good day,

Dave

ager2001 · Mar 31st, 2004, 10:25 PM

Dave - Thanks a lot for the time. I haven't tried it yet but will do as soon as I get the chance. Andy

Davers · Mar 31st, 2004, 10:39 PM

Good luck Andy, let me know how it goes....

Dave

Mar 30th, 2004, 03:57 PM	#1
ager2001 Join Date: Mar 2004 Location: Sheffield, Yorkshire Posts: 4	Help required on accounts spreadsheet I have an accounts spreadsheet that renews each week. What I am trying to do is to have the accounts serialised, 001, 002, 003 etc on a weekly basis. I want the serial no to increment automatically when spreadsheet is archived. Andy

Mar 30th, 2004, 04:10 PM	#2
Davers Join Date: Sep 2002 Location: Michigan Posts: 1,132	Re: Help required on accounts spreadsheet Hey Andy, is the serial number the name of the Sheet? or a Column heading?...or something else... Could you post a small example? Dave __________________ Using Windows XP with Office XP

Mar 30th, 2004, 10:16 PM	#3
ager2001 Join Date: Mar 2004 Location: Sheffield, Yorkshire Posts: 4	Re: Help required on accounts spreadsheet Dave - the serial number is to be used to avoid fraud on the account - if each time the account is saved the serial number (a formula in a protected cell??) is incremented by 1. That way I can have a trail of all entries onto the account. Several people use the spreadsheet in different locations. Does that explain the problem better

Mar 31st, 2004, 04:39 AM	#4
Davers Join Date: Sep 2002 Location: Michigan Posts: 1,132	Re: Help required on accounts spreadsheet Ok...let's see if I got this right...so the number is in a cell, and you want the number to increase by 1 each time you save the workbook without any input by a user? If that's right, let me know...I'm on my Mac at home right now...and I hate Excel on the Mac...it's not the same even though they say it is.... I'll take a look at it tomorrow morning... Have a good day, Dave P.S...and is that every time the workbook is saved...or everytime it's opened? __________________ Using Windows XP with Office XP

Mar 31st, 2004, 02:35 PM	#5
ager2001 Join Date: Mar 2004 Location: Sheffield, Yorkshire Posts: 4	Re: Help required on accounts spreadsheet Dave - Yes that is what I am trying to do. Increment the number everytime the spreadsheet is closed. Andy

Mar 31st, 2004, 03:56 PM	#6
Davers Join Date: Sep 2002 Location: Michigan Posts: 1,132	Re: Help required on accounts spreadsheet This should do the trick...copy and paste this into the "Thisworkbook" object... Code: Private Sub Workbook_BeforeClose(Cancel As Boolean) Worksheets("Sheet1").Range("A1") = Range("A1") + 1 ActiveWorkbook.Save End Sub This assumes your number is in cell A1 on Sheet1... Hope this helps and have a good day, Dave __________________ Using Windows XP with Office XP

Mar 31st, 2004, 10:25 PM	#7
ager2001 Join Date: Mar 2004 Location: Sheffield, Yorkshire Posts: 4	Re: Help required on accounts spreadsheet Dave - Thanks a lot for the time. I haven't tried it yet but will do as soon as I get the chance. Andy

Mar 31st, 2004, 10:39 PM	#8
Davers Join Date: Sep 2002 Location: Michigan Posts: 1,132	Re: Help required on accounts spreadsheet Good luck Andy, let me know how it goes.... Dave

Thread Tools
Show Printable Version Email this Page
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode