Avatar of auraorange
auraorange
Flag 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
Miscellaneous

Avatar of undefined
Last Comment
bk_jreinstein

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
bk_jreinstein

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
bk_jreinstein

Anything else?
bk_jreinstein

Got some links here for preventing duplications in excel, don't know if they'll be of help:
http://www.ozgrid.com/Excel/prevent-duplicates.htm
http://www.ozgrid.com/Excel/highlight-duplicates.htm
http://www.ozgrid.com/Services/Excel_Duplication_Add-in.htm

Regards,
bk
snerkel

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
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
SOLUTION
kbens0n

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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
kbens0n

Sound advice! Thanks Aura.
bk_jreinstein

Thank You.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.