excel vba - automatic invoice numbering

i am trying to set up something in cell K5 whereby it will produce upo
opening, a sequential number 1 higher than the last.

I think the end code is Range("K5").Value + 1

but how do i set it up to get that sequencing

--
Message posted from http://www.ExcelForum.com

0
7/7/2004 7:56:04 PM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
1097 Views

Similar Articles

[PageSpeed] 48

Hi

You need this event to run your code line when the workbook opens

Private Sub Workbook_Open()
    With Sheets("sheet1")
        .Range("K5").Value = .Range("K5").Value + 1
    End With
End Sub

Right click on the Excel icon next to file on the menu bar
Choose view code
The ThisWorkbook module is active now
Paste the code in there
Save/close and reopen the file


-- 
Regards Ron de Bruin
http://www.rondebruin.nl


"chief >" <<chief.191hs7@excelforum-nospam.com> wrote in message news:chief.191hs7@excelforum-nospam.com...
> i am trying to set up something in cell K5 whereby it will produce upon
> opening, a sequential number 1 higher than the last.
>
> I think the end code is Range("K5").Value + 1
>
> but how do i set it up to get that sequencing?
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
rondebruin (3790)
7/7/2004 8:11:34 PM
thanks for the reply, it worked very well.

another question

i have set it up so that when they click a command button i
automatically saves the invoice sheet into my documents with the K
cell contents as the file name.  However, i would like to have it se
up so that one they save and close, the next time they open the shee
to do another invoice it will be 1 number higher than the last save
invoice number.  The reply posted before took care of most of that, bu
now it seems that when i click the command button to auto save it to m
docs and close the sheet, when i open a new sheet it comes up as th
same number.  Should there be some sort of loop code in there

--
Message posted from http://www.ExcelForum.com

0
7/8/2004 1:39:17 PM
Hi chief

Are you saving only one sheet?

Post you Save macro

-- 
Regards Ron de Bruin
http://www.rondebruin.nl


"chief >" <<chief.192uzf@excelforum-nospam.com> wrote in message news:chief.192uzf@excelforum-nospam.com...
> thanks for the reply, it worked very well.
>
> another question
>
> i have set it up so that when they click a command button it
> automatically saves the invoice sheet into my documents with the K5
> cell contents as the file name.  However, i would like to have it set
> up so that one they save and close, the next time they open the sheet
> to do another invoice it will be 1 number higher than the last saved
> invoice number.  The reply posted before took care of most of that, but
> now it seems that when i click the command button to auto save it to my
> docs and close the sheet, when i open a new sheet it comes up as the
> same number.  Should there be some sort of loop code in there?
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
rondebruin (3790)
7/8/2004 8:27:19 PM
this is the basic run down.  i open up my invoice retail sheet, ente
the customers order and then click on a command button whic
automatically saves that sheet with that customers info into m
documents, and saves the file as the contents of cell K5 (this is wher
the auto generated sequential number should be).  As soon as it get
saved it is printed and the sheet closes.  Then i open the invoic
retail sheet again and start from scratch.  The problem is i have se
up a code upon workbook open such as:

Range("K5").Value = Range("K5").Value + 1
Numberformat = 0000

The dilemma i'm facing is everything else works but i want to obviousl
save each invoice under a new sequential number to be stored in mydocs
When i open the new sheet after saving, it doesn't jump to the nex
sequential number.  Can anybody help me?

tahnks alo

--
Message posted from http://www.ExcelForum.com

0
7/8/2004 11:31:53 PM
Hi chief

You must save the "invoice retail sheet" ( I think you mean workbook)
before you close it

Or are you using a sheet template?

-- 
Regards Ron de Bruin
http://www.rondebruin.nl


"chief >" <<chief.193mf3@excelforum-nospam.com> wrote in message news:chief.193mf3@excelforum-nospam.com...
> this is the basic run down.  i open up my invoice retail sheet, enter
> the customers order and then click on a command button which
> automatically saves that sheet with that customers info into my
> documents, and saves the file as the contents of cell K5 (this is where
> the auto generated sequential number should be).  As soon as it gets
> saved it is printed and the sheet closes.  Then i open the invoice
> retail sheet again and start from scratch.  The problem is i have set
> up a code upon workbook open such as:
>
> Range("K5").Value = Range("K5").Value + 1
> Numberformat = 0000
>
> The dilemma i'm facing is everything else works but i want to obviously
> save each invoice under a new sequential number to be stored in mydocs.
> When i open the new sheet after saving, it doesn't jump to the next
> sequential number.  Can anybody help me?
>
> tahnks alot
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
rondebruin (3790)
7/9/2004 11:39:41 AM
Reply:

Similar Artilces:

Excel 97 Autofilter
Is there a maximum number of lines that can be displayed using AutoFilter? In a list of >8000 items only 600 or so are displayed. How can I get around this? An AutoFilter dropdown list will only show 1000 entries. If your column has more than 1000 unique items, they can be filtered, but they can't all be displayed in the dropdown list. To AutoFilter for an item that doesn't appear in the dropdown list, you can choose Custom from the drop-down list, and type the criteria. Also, there are a couple of workarounds here: http://www.contextures.com/xlautofilter02.html Al...

Exporting CRM Opportunity List to Office Excel 2007
We have CRM 3.0 Server. Our users are using CRM Web Client to access their leads, opportunities, quotes and orders. Using the Export toolbar button in CRM, users who have Office 2003 installed on their PCs are able to export opportunity lists to Excell perfectly all right, however users who have Office 2007 installed are not able to do that! Is this a limitation in CRM 3.0 ? Is there a workaround (If any)? Any possible risks when using or applying this workaround (If any)? "luai7" <luai7@discussions.microsoft.com> wrote in message news:6CADFDC5-8439-4B5F-955A-D6DBF...

How can I do this in Excel
I know only enough about Excel to get me in trouble so please keep your answers as basic as possible. I have a entry like 01/04/2006 10 23 42 48 54 03 x4 when I paste this into a worksheet I would like it to appear like this col A col B col C col D col E col F col G col H 01/04/2006 10 23 42 48 54 03 x4 Can this be done? I remember seeing a feature where you could put lines to sperate date into different columns Thanks ...

Why can't I convert quatro pro .wb3 file in Excel #2
I have trid everything suggested in Excel and on-line but can't convert my *.wb3 file in Excel 2003. Downloaded the Office converter file and ran it, still nothing appropriate in add-ins. What am I doing wrong? Thanks, Tom ...

How do I change the color for cell selection in excel?
I just started using Excel 2007. In the older versions when I select a row, column, or specific group of cells, Excel would highlight the group in a certain color. In Excel 2007, it simply surrounds the selected group of cells with a thick black line. I've tried going into Excel Options under Advanced but couldn't find how to do this. Can you assist me in changing this feature? On Dec 31 2007, 6:52 pm, dminliberty <dminlibe...@discussions.microsoft.com> wrote: > I just started using Excel 2007. In the older versions when I select a row, > column, or specific grou...

Purchase Order/Invoice ledger
What I would like to do is to keep track of everything that we purchase over 12 months. I have already partly designed the worksheet with 5 fields, the last two being Purchase Order cost and Invoice cost. I have totalled both columns. I would like to have a running total which would export onto the next sheet and below that the current months total which would be added to the prevoius months total. you may try a pivot table of the data set. Data>pivot table and charts or subtotals also under Data menu. Either of these should give you what you want. Pat "Speaker" wrote:...

Excel Formula #3
Trying to write a formula that would say this: If there is an "x" in column F2 then add 1 to the number in column I2. In other words column I2 starts with a "0" and if there is a "x" placed in F2, then I want I2 to read "1". This is a win loss chart so there would be "x's" in other columns but I want it to keep adding "1" to I2. ** Posted via: http://www.ozgrid.com Excel Templates, Training, Add-ins & Business Software Galore! Free Excel Forum http://www.ozgrid.com/forum *** A bit more clarity before suggesting a worksheet_...

Money SB 2k6 custom invoice problem
I upgraded Money 2k5 SB to Money 2k6 SB yesterday - and I've already found a problem. :-( My custom invoice was carried over, and it displays correctly - except for my logo in the upper-left corner. I get a broken image link, instead of my logo. I've pointed it to the correct logo, and even copied the logo (a .GIF file) into the Invoice graphics folder. When I choose it, it shows up correctly in Invoice Designer; when I close Invoice Designer and save the custom invoice, the graphic does NOT show up, and only the broken image link does. Anyone? Arrrggghhhh! Also, he line ...

Automating Excel from Access
From Access I'm trying to set the vertical alignment for all cells of an Excelfile to 'Top': Dim XLApp As Excel.Application Dim XLBook As Excel.Workbook Dim XLSheet As Excel.Worksheet Set XLApp = New Excel.Application Set XLBook = XLApp.Workbooks.Open("c:\Test.xls") Set XLSheet = XLBook.Worksheets(1) XLSheet.Activate XLSheet.Cells.Select With Selection .VerticalAlignment = xlTop End With XLBook.SaveAs ("c:\Test2.xls") Set XLSheet = Nothing XLBook.Close Set XLBook = Nothing XLApp.Quit Set XLApp = Nothing but I get the message objetvariable or blockvaria...

Using english and non-English Excel commands simultaneously in a non-English installation
Hi community, I like this forum very much - I got many valuable insights from it. In the past I had an English Excel installation at the office available, recently my employer moved to the German version and deinstalled the english one. Now, when i use a english command like =VLOOKUP(A1,'Sheet 2'! A1:H200,8,FALSE) adopting the country settings for "," with ";" Excel comes up with #NAME? since I used the english term, not the german one - but sometimes the german equivalent is not at hand as fast as needed ;-) However if a open a english coined workbook of a US/UK c...

change cell shading when a number differs from the previous number
I am generally familiar with Excel, but need some help on this particular problem. Is there a way to apply a format where: whenever the number in a column differs from the one previous to it, a cell shading color change is applied. For example, you have a column of numbers in Excel: 2 2 2 5 (cell shading color change applied) 5 5 5 2 (cell shading color change applied) 2 2 3 (cell shading color change applied) etc.... It doesn't matter what color is used, just so that it is applied whenever there is a change from the previous number. There has got to be an easy answer to this.... ...

change field number automatically when printing
We have a form in Excel, it is only one page, but we need a number on the right top corner of the sheet that changes automatically evey time we print the form Is there any way to do this Thank you You could use a Workbook BeforePrint macro that do that, assume the number is in cell G1 Range("G1").Value = Range("G1").Value + 1 like this Private Sub Workbook_BeforePrint(Cancel As Boolean) Range("G1").Value = Range("G1").Value + 1 End Sub -- Regards, Peo Sjoblom "sponder" <anonymous@discussions.microsoft.com> wrote in message news...

Excel 2007
Hi, I wanted to post a fix to a common problem I've seen. There are many posts on the internet concerning this but I've only seen the true fix once. Here's the problem, someone installs Office 2007 and then when they try to double click on an Excel file, it opens Excel but the Excel file itself does not open. So, you are left looking at the Excel 2007 screen, but it is blank with no workbooks open (not even the default one that usually opens). Here is the official Microsoft solution: http://support.microsoft.com/kb/924074 - however, this only fixes about 20% of the case...

Creating forms in Access or Excel that link to Visio Documents
Does anybody have any experience with creating forms in access or excel that are integrated with Visio? How would I go about doing this? Access by definition can store 'objects', which can include visio drawings. If prompted, don't select display icon a.) create table add oledb field add text field for name b.) open table select oledb field and right click => insert new microsoft visio edit the new drawing and close it c.) create a form based on new table drag the oledb field onto the form d.) open the form what are you attempting to do? al &quo...

Microsoft 2007
I am trying to share a workbook - Toolbar, Review/Share Workbook - Each time, I get a message, the file is no longer shared and will not let me save. Any thoughts? -- Patty I think another person of your shared workbook might have removed you from the sharing. Otherwise the file has been moved to some other location. Save the current file in your local system and try to open the Original File from its actual location. -- Remember to Click Yes, if this post helps! -------------------- (Ms-Exl-Learner) -------------------- "Patty" wrote: > I am tryi...

bin location export into excell
I run a report and then export it into excell. In the report all the bin #'s are correct, but in excell a protion of them are shown as dates rather than 00-00-00. Why do only some do this? The items in question were built at various times. Sandy It's being interpreted as a date by MS Excel due to the format of the bin location field. See my reply to your other post. Tom -- Stop fishing for e-mail "Sandy" wrote: > I run a report and then export it into excell. In the report all the bin #'s > are correct, but in excell a protion of them are shown as dates...

Can i use a formula in the excel chart wizard?
I am creating a coursework in which i have to analyse a lot of data in the spreadsheet. it is already cluttered with columns and i dont want to add extra columns to add to the clutter. i was wondering whether there is a way to plot absolute values of a column without changing the actual values in the column or creating a new column using ABS() function. Thanx You can't use formulas in the reference boxes in the chart wizard. You can only use range references or defined names. - Jon ------- Jon Peltier Peltier Technical Services, Inc. http://peltiertech.com/ On 3...

How can I conditionally format in excel for >3 conditions
There are a possible 5 different conditions which I wish to use to determine the colour of a given cell. Each condition should result in a different colour. Using the "wizard" I get a maximum of 3 colours. Is there a slick way around this limitation? What's the criteria for all your scenarios? I mean, chances are, using a =if() with an or() or and and() will do just fine, 1 condition. Danny On Mon, 11 Jul 2005 05:21:03 -0700, Jimbo <Jimbo@discussions.microsoft.com> wrote: > There are a possible 5 different conditions which I wish to use to > de...

Excel : insert new data series via mouse click on graphic.
Dear Developers, I work as researcher in a sector that uses spatial data. i always return back to excel, honouring Turing principle, after "travelling" to more sophisticated Sw or ambients (like matlab, and others). One of the few thing I find annoyng in excel is that one cannot input data via graphic with a click of mouse on the cartesian space, function very useful in spatial data. Sincerely ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button ...

Row numbers #2
I want to label and print row numbers for each row, however, I do not wish to print column headings...can I disable the printing of the column headings? Is there possibly a function to run in a cell to provide it a number or something else? ----------------- Scott You can only disable printing column *and* row headings. <File> <PageSetup> <Sheet> tab, Make sure "column & row headings" is *unchecked*. Then, you can number your first column to mirror the row numbers, and that will give the impression of displaying and printing the row headers. You could e...

Help please
I have an Excel 2003 spreadsheet that I keep golf scores and with some formulas I maintain my golf handicap. I would like to have an interface, form, etc that will allow me to do the following: 1) Enter data for a golf round (name, date, course, rating1, rating2, score) 2) Display data for a golfer 3) Perform functions on the data in the spreadsheet (modify/delete data, compute handicaps, etc) I'm not sure if there is something within Excel (VBA?) that will allow this or if there is some shareware or freeware that is specifically designed for this but any help would be gre...

using excel windows 98
i have a table of 100's of figures that are all in text, ie: 12 34 45 56 67 78 56 78 23 78 34 67 23 89 45 78 87 21 each number in it's own cell, but i have loads of them how can i get a decimal point in front of all of them, so they all show like this ..12 .34 .45 .56 .67 .78 ..56 .78 .23 .78 .34 .67 ..23 .89 .45 .78 .87 .21 i have looked in the cells settings and can not find a way myself, i have been told to check a box to default the decimal point, but this is when you have an empty sheet, not when you have already entered the figures. any ideas. thanks Jason One way .. ...

Accidental Saved over Excel File
I was clicking on a help topic and realized as it was loading the help screen, that I needed to click on the other topic. I clicked several times and then somehow it clicked something in the background in the worksheet, my worksheet is now blank, and all the data is gone. Is there ANYWAY at all to get the workbook again in the condition it was before the accidental last save??? Sorry to Say, but AFAIK there is no way to recover your files except of course if you have made a back up copy. "Tom" <wilsontb@bellsouth.net> wrote in message news:00b001c38fb8$69c6da60$a00128...

Transfer HTML emails to computer automaticly?
How do you make Outlook automaticly move the "Inbox on MSN" e-mails into the regular Inbox, with the rest of my e-mails? You can't - you must move them manually. Rules do not work with MSN accounts. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. All unsolicited mail sent to my personal account will be deleted without reading. After furious head scratching, Dyan asked: | How do you make Outlook automaticly move the "Inbox on MSN" e-mails | into the regular Inbox, with the rest of my e-mails? ...

PDF or Image file transfer to Excel
Is there any possible way to pull the information off an image onto Excel? For example, Blue Company receives a remittance advice each month from Red Customer. This remittance advice is received in PDF format and is hundreds of pages. Is there anyway to take this remittance advice in PDF format and translate it to Excel? That way all the invoices on this remittance advice do not have to be manually keyed? Any help would be great!!! Thanks!!! Ryan You can convert the pdf to an html file, and then open it up in Excel. Of course you'll lose formatting but you can reconstruct it ...