BeforePrint Sub auto referencing

[CordoK83 via OfficeKB.com]

Hi!

I'm new to programming Macros and I'm slowly learning by reading posts on
"how to"s for different things I've needed to do. I haven't found a way to do
the following, and was wondering if anyone could help:

I have an excel form that needs to have an sequential numbering that changes
everytime the worksheet is printed and/or saved.

This worksheet is used to log in the references and amount of invoices being
received from vendor X, then it's printed and one copy stays with the
invoices we received and another is given to the vendor as a "receipt" for
him to turn in when picking up the "check". What I need is for each "receipt"
to have a different number (can't repeat itself) --- but I need for both
copies to have the same number --- thus this would run in order to increase
the reference number after printing a worksheet twice.

In addition, I also need to be able to keep a log of each reference number
assigned, and some information from each "receipt".

Is there anyway that I'd be able to set this up.

Please let me know!

Thank you!

 

[john]

have a look at this site:

http://www.mcgimpsey.com/excel/udfs/sequentialnums.html

 

[CordoK83 via OfficeKB.com]

Hi,

Thank you, but I've tried applying this to my form, but it doesn't really
work because I need it to change reference numbers after I print out 2 copies
of the form. Is there any other way to do this? Any other macro?

Thanks for the help.

have a look at this site:

http://www.mcgimpsey.com/excel/udfs/sequentialnums.html

Hi!

[quoted text clipped - 21 lines]

Thank you!

 

[john]

sorry for belated reply.

Following code increments your invoice sheet after two copies are printed &
makes a record of invoice to log worksheet. You will need to add code to
update any textbox on your form that displays invoice number.

Sub PrintInvoice()
Dim invno As Range
Dim InvNumber As Long
Dim InvWS As Worksheet
Dim LogWs As Worksheet
Dim Supplier As String

Set InvWS = Worksheets("Invoice")
Set LogWs = Worksheets("Log")
Set invno = InvWS.Range("A1")

Supplier = InvWS.Range("A2").Value '<<change as required

With invno

.NumberFormat = "@"

.HorizontalAlignment = xlRight

If IsEmpty(.Value) Then

InvNumber = 1


.Value = Format(InvNumber, "0000")

Else

InvNumber = .Value


End If


InvWS.PrintOut Copies:=2, Collate:=True


.Value = Format(InvNumber + 1, "0000")

End With

With LogWs

lr = .Cells(.Rows.Count, 1).End(xlUp).Row + 1

With .Range("A" & lr)

.NumberFormat = "@"
.HorizontalAlignment = xlRight
.Value = Format(InvNumber, "0000")

End With

.Range("B" & lr).Value = Supplier
.Range("C" & lr).Value = Format(Date, "dd/mm/yyyy")

End With

End Sub


--
jb

Hi,

Thank you, but I've tried applying this to my form, but it doesn't really
work because I need it to change reference numbers after I print out 2 copies
of the form. Is there any other way to do this? Any other macro?

Thanks for the help.

have a look at this site:

http://www.mcgimpsey.com/excel/udfs/sequentialnums.html

Hi!

[quoted text clipped - 21 lines]

Thank you!

 

[CordoK83 via OfficeKB.com]

Thank you! Just one more question, as I still haven't been able to make this
work.

Would I just "copy & paste" this code onto the "This Workbook" section on
Visual Basic of my file? ---I've Adjusted the names I actually have on my
file for the InvWS, Log, and my invoice number cell reference (as well as the
suppliers).

I greatly appreciate your help with this!.

sorry for belated reply.

Following code increments your invoice sheet after two copies are printed &
makes a record of invoice to log worksheet. You will need to add code to
update any textbox on your form that displays invoice number.

Sub PrintInvoice()
Dim invno As Range
Dim InvNumber As Long
Dim InvWS As Worksheet
Dim LogWs As Worksheet
Dim Supplier As String

Set InvWS = Worksheets("Invoice")
Set LogWs = Worksheets("Log")
Set invno = InvWS.Range("A1")

Supplier = InvWS.Range("A2").Value '<<change as required

With invno

.NumberFormat = "@"

.HorizontalAlignment = xlRight

If IsEmpty(.Value) Then

InvNumber = 1

.Value = Format(InvNumber, "0000")

Else

InvNumber = .Value

End If

InvWS.PrintOut Copies:=2, Collate:=True

.Value = Format(InvNumber + 1, "0000")

End With

With LogWs

lr = .Cells(.Rows.Count, 1).End(xlUp).Row + 1

With .Range("A" & lr)

.NumberFormat = "@"
.HorizontalAlignment = xlRight
.Value = Format(InvNumber, "0000")

End With

.Range("B" & lr).Value = Supplier
.Range("C" & lr).Value = Format(Date, "dd/mm/yyyy")

End With

End Sub

Hi,

[quoted text clipped - 12 lines]

 

[john]

Place it behind your userform and call it from the commandbutton_click event

if you are still having problems mail me directly nospamdt @ btinternet . com

(close spaces)
--
jb

Thank you! Just one more question, as I still haven't been able to make this
work.

Would I just "copy & paste" this code onto the "This Workbook" section on
Visual Basic of my file? ---I've Adjusted the names I actually have on my
file for the InvWS, Log, and my invoice number cell reference (as well as the
suppliers).

I greatly appreciate your help with this!.

sorry for belated reply.

Following code increments your invoice sheet after two copies are printed &
makes a record of invoice to log worksheet. You will need to add code to
update any textbox on your form that displays invoice number.

Sub PrintInvoice()
Dim invno As Range
Dim InvNumber As Long
Dim InvWS As Worksheet
Dim LogWs As Worksheet
Dim Supplier As String

Set InvWS = Worksheets("Invoice")
Set LogWs = Worksheets("Log")
Set invno = InvWS.Range("A1")

Supplier = InvWS.Range("A2").Value '<<change as required

With invno

.NumberFormat = "@"

.HorizontalAlignment = xlRight

If IsEmpty(.Value) Then

InvNumber = 1

.Value = Format(InvNumber, "0000")

Else

InvNumber = .Value

End If

InvWS.PrintOut Copies:=2, Collate:=True

.Value = Format(InvNumber + 1, "0000")

End With

With LogWs

lr = .Cells(.Rows.Count, 1).End(xlUp).Row + 1

With .Range("A" & lr)

.NumberFormat = "@"
.HorizontalAlignment = xlRight
.Value = Format(InvNumber, "0000")

End With

.Range("B" & lr).Value = Supplier
.Range("C" & lr).Value = Format(Date, "dd/mm/yyyy")

End With

End Sub

Hi,

[quoted text clipped - 12 lines]

Thank you!