Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 02-17-2015, 09:24 AM
pringle747 pringle747 is offline Add a serial number to an invoice Windows 7 32bit Add a serial number to an invoice Office 2007
Novice
Add a serial number to an invoice
 
Join Date: Feb 2015
Posts: 4
pringle747 is on a distinguished road
Question Add a serial number to an invoice

I write a maximum of 100 invoices per year. All invoices for 2015 should be in workbook 2015'´.
Every time I access workbook 2015 and enter data, I want an incremental serial number to appear in E15.



Can this be done, or is there a better solution for invoices?
Reply With Quote
  #2  
Old 02-17-2015, 07:51 PM
excelledsoftware excelledsoftware is offline Add a serial number to an invoice Windows 7 64bit Add a serial number to an invoice Office 2003
IT Specialist
 
Join Date: Jan 2012
Location: Utah
Posts: 455
excelledsoftware will become famous soon enough
Default

This can be done using an on open command. This will be in the visual basic editor under thisworkbook.

Code:
Private Sub Workbook_Open()
  Range("E15").Value = Range("E15").Value + 1
End Sub
Now there are ways you could probably do it with a formula or even having a button that says "New Invoice" which you would just attach the middle of the code to it.

Let me know if this is what you are looking for.

Thanks
Reply With Quote
  #3  
Old 02-18-2015, 01:36 AM
pringle747 pringle747 is offline Add a serial number to an invoice Windows 7 32bit Add a serial number to an invoice Office 2007
Novice
Add a serial number to an invoice
 
Join Date: Feb 2015
Posts: 4
pringle747 is on a distinguished road
Red face Invoice numbering

Many thanks, but unfortunately you are talking to a dumbo! I can handle Excel but have no idea what an Open command is, where and how do I insert the code you sent me etc..

What I am doing right now is opening Workbook called invoices 2015, renaming next new sheet as 101 (for example), and then manually typing 101 into E15.

New invoice.....new worksheet....and so on.

Not at all satisfactory.

Thanks for the help
Reply With Quote
  #4  
Old 02-18-2015, 07:18 PM
excelledsoftware excelledsoftware is offline Add a serial number to an invoice Windows 7 64bit Add a serial number to an invoice Office 2003
IT Specialist
 
Join Date: Jan 2012
Location: Utah
Posts: 455
excelledsoftware will become famous soon enough
Default

You are not a dumbo. I am the dumbo for assuming that everyone knows how to access certain things. In order to have this code work for your workbook you will need to do the following.

When your workbook is open press ALT + F11 This will open up the Visual Basic Editor
The Visual Basic Editor VBE is where code goes. On the left of this screen you will see some small Icons saying Sheet1, Sheet2, Sheet3 Or whatever your worksheets are called. and one called ThisWorkbook.
Double click on ThisWorkbook and you will see 2 dropdown lists above the white background editor. Click the one to the left and select Workbook. Then click the one to the right and select Open.

Some code will automatically fill in to the white background. Clear this out and then paste in the code I provided you. Save your workbook as a backup and then it should run everytime the workbook is open.

If you have issues with Excel saying Macros are disabled let me know and I will guide you through on how to fix that for the version of Excel you are using. You can find a lot of guides out there on the internet if you dont want to wait.

Let me know how it goes.

Thanks
Reply With Quote
  #5  
Old 02-19-2015, 12:00 AM
pringle747 pringle747 is offline Add a serial number to an invoice Windows 7 32bit Add a serial number to an invoice Office 2007
Novice
Add a serial number to an invoice
 
Join Date: Feb 2015
Posts: 4
pringle747 is on a distinguished road
Thumbs up Invoice numbering

Thanks a million Excelled software. This has opened a whole new world for me. I shall now follow your instructions and I shall send you the results!

Finally I can get behind the printed User Interface to the code!

Regards
Reply With Quote
  #6  
Old 02-19-2015, 01:28 AM
pringle747 pringle747 is offline Add a serial number to an invoice Windows 7 32bit Add a serial number to an invoice Office 2007
Novice
Add a serial number to an invoice
 
Join Date: Feb 2015
Posts: 4
pringle747 is on a distinguished road
Default

Ok all worked as you instructed, but "The following features cannot be saved in macro-free workbook:
* VB project

Could you tell me where to find File Type list, or what are next step.

Thanks again.
Reply With Quote
  #7  
Old 02-19-2015, 11:19 AM
excelledsoftware excelledsoftware is offline Add a serial number to an invoice Windows 7 64bit Add a serial number to an invoice Office 2003
IT Specialist
 
Join Date: Jan 2012
Location: Utah
Posts: 455
excelledsoftware will become famous soon enough
Default

No problem you just need to save your workbook as a xlsm this is the second option for file extensions in the save as dialog box.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Add a serial number to an invoice Converting serial number to years, months and days hannu Excel 6 02-14-2013 09:21 PM
Add a serial number to an invoice Increase serial number for every print atomtm Word 1 06-15-2012 05:39 AM
Add a serial number to an invoice Serial Number Issue rehan5001 Excel 1 06-08-2011 02:46 AM
Add a serial number to an invoice Invoice Number Generation mrphilk Excel 2 06-08-2010 12:39 PM
Help needed using the serial number date with sumifs - whole office is stumped FraserKitchell Excel 3 01-06-2010 12:24 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 12:21 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft