Link to home
Create AccountLog in
Avatar of auraorange
auraorangeFlag for United Kingdom of Great Britain and Northern Ireland

asked on

I want to add a counter onto my delivery note excel spreadsheet to stop duplications or shall I used an access database?

I have an excel spreadsheet that we fill out for delivery notes.

Sometimes we forget to manually change the delivery note numbers and end up duplicated numbers.

I've never created a macro before but I was wondering if I could install a counter on the spreadsheet

However I am not sure if this is the right thing to do as it wont be automated.

So am i better off creating a database, this may be a little more complicated to set up but then I can link my client details, product details and never have a duplicate delivery note number.

Advice on this would be appreciated.

Thank you

Aura
ASKER CERTIFIED SOLUTION
Avatar of bk_jreinstein
bk_jreinstein

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of bk_jreinstein
bk_jreinstein

Anything else?
If you just want an incremental count everytime you open a sheet.

Open Visual basic editor under tools macros

In the left window right click VBAProject and select insert module

Window opens paste the following in to it....


Sub Auto_open()

On Error GoTo ErrorHandler

One:
Open "c:\Counter.txt" For Input As #1
Input #1, x
Close #1
x = x + 1

Two:
Sheets(1).Range("A1").Value = x
Open "c:\Counter.txt" For Output As #1
Write #1, x
Close #1

Exit Sub
ErrorHandler:
    Select Case Err.Number
        Case 53  'If Counter file does not exist...
            x = InputBox("Enter Number to Begin Counting With", "Create 'Counter.txt' File")
            Resume Two
        Case Else
            Resume Next
    End Select
   
End Sub
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of auraorange

ASKER

Thanks for the great feedback.

What I have suggested to the client is a database afterall so we can also link invoices into the same database etc etc

once again

thank you

Aura
Sound advice! Thanks Aura.
Thank You.