Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
BeforePrint Sub auto referencing
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! -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200908/1 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
BeforePrint Sub auto referencing
have a look at this site:
http://www.mcgimpsey.com/excel/udfs/sequentialnums.html -- jb "CordoK83 via OfficeKB.com" wrote: 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! -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200908/1 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
BeforePrint Sub auto referencing
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. john wrote: have a look at this site: http://www.mcgimpsey.com/excel/udfs/sequentialnums.html Hi! [quoted text clipped - 21 lines] Thank you! -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200908/1 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
BeforePrint Sub auto referencing
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 "CordoK83 via OfficeKB.com" wrote: 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. john wrote: have a look at this site: http://www.mcgimpsey.com/excel/udfs/sequentialnums.html Hi! [quoted text clipped - 21 lines] Thank you! -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200908/1 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
BeforePrint Sub auto referencing
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!. john wrote: 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! -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200908/1 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
BeforePrint Sub auto referencing
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 "CordoK83 via OfficeKB.com" wrote: 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!. john wrote: 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! -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200908/1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
referencing another sheet and using auto fill | New Users to Excel | |||
BeforePrint | Excel Programming | |||
Data referencing and auto fill across worksheets in one workbook.. | Excel Worksheet Functions | |||
BeforePrint help | Excel Programming | |||
ADO 2.7 & ADO 2.8 beforeprint | Excel Programming |