LENDING A FORMULA FOR INVOICES
 

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?

LENDING A FORMULA FOR INVOICES
 

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?

LENDING A FORMULA FOR INVOICES
 

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?

LENDING A FORMULA FOR INVOICES
 

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?

LENDING A FORMULA FOR INVOICES
 

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?

LENDING A FORMULA FOR INVOICES
 

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?

LENDING A FORMULA FOR INVOICES
 

Oh my gosh it actually worked. THANK YOU. Okay so now that this worked is
there an easy way to make it change P.O. numbers after printing like you
mentioned before? Don't worry about it if it's too difficult to understand.

"Jay" wrote:

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?

LENDING A FORMULA FOR INVOICES
 

If we're lucky, the following will work in your situation. It increments
P.O. BEFORE printing. Maybe it will suit your application, maybe not. Can't
hurt to try it because it's easy. It gets a tad more involved if this
doesn't work for you.

In the VB Editor, find the "ThisWorkbook" object in the project explorer
pane, open it with a double-click, and paste the following code into the work
area window:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Worksheets("Sheet3").Range("B2") = Worksheets("Sheet3").Range("B2") + 1
End Sub

Try it and let me know what's next.

--
Jay


"LUVMEEDOO" wrote:

Oh my gosh it actually worked. THANK YOU. Okay so now that this worked is
there an easy way to make it change P.O. numbers after printing like you
mentioned before? Don't worry about it if it's too difficult to understand.

"Jay" wrote:

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?

LENDING A FORMULA FOR INVOICES
 

I PASTED THE CODE WHERE YOU TOLD ME, CHANGED THE RANGE NUMBER, GOT BACK TO
THE EXCEL WORK SHEET AND PRINTED...THE NUMBER DIDN'T CHANGE. I HOPE IT DID
IT CORRECTLY. I'M NOT SURE WHAT I WOULD HAVE MISSED.

"Jay" wrote:

If we're lucky, the following will work in your situation. It increments
P.O. BEFORE printing. Maybe it will suit your application, maybe not. Can't
hurt to try it because it's easy. It gets a tad more involved if this
doesn't work for you.

In the VB Editor, find the "ThisWorkbook" object in the project explorer
pane, open it with a double-click, and paste the following code into the work
area window:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Worksheets("Sheet3").Range("B2") = Worksheets("Sheet3").Range("B2") + 1
End Sub

Try it and let me know what's next.

--
Jay


"LUVMEEDOO" wrote:

Oh my gosh it actually worked. THANK YOU. Okay so now that this worked is
there an easy way to make it change P.O. numbers after printing like you
mentioned before? Don't worry about it if it's too difficult to understand.

"Jay" wrote:

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?

LENDING A FORMULA FOR INVOICES
 

Did you change the worksheet name, too ?

Change "Sheet3" in the code I sent to "YourInvoiceSheetName" (change it in
both places).
--
Jay


"LUVMEEDOO" wrote:

I PASTED THE CODE WHERE YOU TOLD ME, CHANGED THE RANGE NUMBER, GOT BACK TO
THE EXCEL WORK SHEET AND PRINTED...THE NUMBER DIDN'T CHANGE. I HOPE IT DID
IT CORRECTLY. I'M NOT SURE WHAT I WOULD HAVE MISSED.

"Jay" wrote:

If we're lucky, the following will work in your situation. It increments
P.O. BEFORE printing. Maybe it will suit your application, maybe not. Can't
hurt to try it because it's easy. It gets a tad more involved if this
doesn't work for you.

In the VB Editor, find the "ThisWorkbook" object in the project explorer
pane, open it with a double-click, and paste the following code into the work
area window:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Worksheets("Sheet3").Range("B2") = Worksheets("Sheet3").Range("B2") + 1
End Sub

Try it and let me know what's next.

--
Jay


"LUVMEEDOO" wrote:

Oh my gosh it actually worked. THANK YOU. Okay so now that this worked is
there an easy way to make it change P.O. numbers after printing like you
mentioned before? Don't worry about it if it's too difficult to understand.

"Jay" wrote:

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?

LENDING A FORMULA FOR INVOICES
 

THAT DID IT! Thank you so much for everything, i really appreciate it. I
have asked a couple questions on this discussion board and you are by far the
most helpful person i have come across. This is going to save the little
company i work for time and money. Thank you again.

"Jay" wrote:

Did you change the worksheet name, too ?

Change "Sheet3" in the code I sent to "YourInvoiceSheetName" (change it in
both places).
--
Jay


"LUVMEEDOO" wrote:

I PASTED THE CODE WHERE YOU TOLD ME, CHANGED THE RANGE NUMBER, GOT BACK TO
THE EXCEL WORK SHEET AND PRINTED...THE NUMBER DIDN'T CHANGE. I HOPE IT DID
IT CORRECTLY. I'M NOT SURE WHAT I WOULD HAVE MISSED.

"Jay" wrote:

If we're lucky, the following will work in your situation. It increments
P.O. BEFORE printing. Maybe it will suit your application, maybe not. Can't
hurt to try it because it's easy. It gets a tad more involved if this
doesn't work for you.

In the VB Editor, find the "ThisWorkbook" object in the project explorer
pane, open it with a double-click, and paste the following code into the work
area window:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Worksheets("Sheet3").Range("B2") = Worksheets("Sheet3").Range("B2") + 1
End Sub

Try it and let me know what's next.

--
Jay


"LUVMEEDOO" wrote:

Oh my gosh it actually worked. THANK YOU. Okay so now that this worked is
there an easy way to make it change P.O. numbers after printing like you
mentioned before? Don't worry about it if it's too difficult to understand.

"Jay" wrote:

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?

LENDING A FORMULA FOR INVOICES
 

I too followed these directions and it worked great. I do have another
question. Each time the invoice number changes is it possible to save that
invoice automatically? I would like the ability to type in an invoice number
and pull up that invoice. Hopefully this makes sense.

"LUVMEEDOO" wrote:

THAT DID IT! Thank you so much for everything, i really appreciate it. I
have asked a couple questions on this discussion board and you are by far the
most helpful person i have come across. This is going to save the little
company i work for time and money. Thank you again.

"Jay" wrote:

Did you change the worksheet name, too ?

Change "Sheet3" in the code I sent to "YourInvoiceSheetName" (change it in
both places).
--
Jay


"LUVMEEDOO" wrote:

I PASTED THE CODE WHERE YOU TOLD ME, CHANGED THE RANGE NUMBER, GOT BACK TO
THE EXCEL WORK SHEET AND PRINTED...THE NUMBER DIDN'T CHANGE. I HOPE IT DID
IT CORRECTLY. I'M NOT SURE WHAT I WOULD HAVE MISSED.

"Jay" wrote:

If we're lucky, the following will work in your situation. It increments
P.O. BEFORE printing. Maybe it will suit your application, maybe not. Can't
hurt to try it because it's easy. It gets a tad more involved if this
doesn't work for you.

In the VB Editor, find the "ThisWorkbook" object in the project explorer
pane, open it with a double-click, and paste the following code into the work
area window:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Worksheets("Sheet3").Range("B2") = Worksheets("Sheet3").Range("B2") + 1
End Sub

Try it and let me know what's next.

--
Jay


"LUVMEEDOO" wrote:

Oh my gosh it actually worked. THANK YOU. Okay so now that this worked is
there an easy way to make it change P.O. numbers after printing like you
mentioned before? Don't worry about it if it's too difficult to understand.

"Jay" wrote:

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?