No problem LMD -
First, try switching worksheets in the same workbook (don't close the
workbook). If your invoice is on Sheet1, select the Sheet2 tab and then
switch back to Sheet1. That should activate the code I sent if you have it
in the correct location.
If it still does not increment, then the code is in the wrong place. Here's
how to make sure.
1. Start the
VB editor (Tools, Macro, Visual Basic Editor).
2. The left pane of the
VB screen is the Project Explorer and should show a
list of projects, objects, etc.
3. Look for VBAProject(YourFileName.xls) and double-click the sheetname of
your invoice sheet under the "Microsoft Excel Objects" folder.
4. Paste the code I sent in the blank work area window (the main window that
takes up most of the space in the
VB Editor.
5. Then File, Close and Return to MS Excel (or press Alt+Q).
6. Then switch between worksheet tabs and see if you invoice number will
increment.
----------
Let me know how it goes.
Jay
"LUVMEEDOO" wrote:
THANK YOU SO MUCH FOR HELPING ME. I DID COPY AND PASTE THE FORMULA INTO THE
OBJECT, SHEET 1 (WHICH IS WHAT MY SPREAD SHEET IS ON) AND I STILL COULDN'T
GET IT TO WORK. I DON'T THINK I'M CUT OUT FOR USING VISUAL BASIC. I'M NOT
SURE WHAT I'M DOING WRONG, SO I DON'T KNOW IF ANYONE WILL BE ABLE TO HELP ME
WITHOUT LOOKING AT MY SCREEN AND TELLING ME WHAT I HAVE TO DO EXACTLY TO GET
THIS CORRECT.
The only thing i can think to do is tell you exactly what i did. I opened
my work sheet, typed in my number in 15600 in my cell. Went into tools and
macros and choose to create a new macro. Then i double clicked on sheet 1
object, pasted your formula and changed the cell reference number to my
number. Then i went to file and clicked on "close and return to microsoft
excel", which brought me back to my work sheet. Finally, I selected to run
my new macro, (nothing happened) then i saved my worksheet, closed out, then
opened it again to see if the number had increased...it hadn't.
Sorry i'm slow at this, i'm not sure if you can help me but i appreciate all
the help you've given. Thanks.
"Jay" wrote:
Punch 15061 into your invoice number cell.
Then, copy the code below to the invoice sheet object in the Visual Basic
Editor (change the cell reference in the code from "B1" to whatever cell
holds your invoice number).
Private Sub Worksheet_Activate()
Range("B1") = Range("B1") + 1
End Sub
This'll do the trick, but you may find that you'll want the printing event
(or some other event) to trigger the increment. Let me know if this works or
if you want to pursue other options.
--
Jay
"LUVMEEDOO" wrote:
WELL THE FIRST INVOICE NUMBER WOULD BE 15601. AND THEN I WOULD LIKE TO BE
ABLE TO FILL OUT THAT INVOICE, PRINT IT AND CLOSE OUT OF THE WORK SHEET.
THEN WHEN I OPEN THAT WORK SHEET AGAIN THE NUMBER HAS AUTOMACTICALLY CHANGED
TO 15602. IF YOU COULD HELP ME THAT WOULD BE SO GREAT.
"Jay" wrote:
Hi LMD-
What do you want the numbers to look like ? Sequential numbers (1, 2, 3,
...), 4 digit numbers (0001, 0002, 0003, ...), combination numbers (98-10001,
98-10002, 98-10003, ...), or other ?
--
Jay
"LUVMEEDOO" wrote:
I have been trying to have my electronic invoices automatically number
themselves each time the invoice/worksheet is opened. Does anyone have a
formula i could use? I have already gone to all the regular links ie:
http://www.mcgimpsey.com/excel/udfs/sequentialnums.html and
http://www.mvps.org/dmcritchie/excel/getstarted.htm and
http://www.cpearson.com/excel/events.htm .
Trying to teach yourself this online is extremely difficult. Can anyone help?