Auto Number increase

[MatthewHughes]

Hello all.

i have searched this forum and found things very close, But none of them work for my self.

what i need help with is im trying to get a number to auto increase each time the jobsheet is printed,

it will also be accessed by more than one computer/user so also needs no write protection (i dont know how it keeps activating?)

i would upload the file but cant seem to find where to do it.

im using office pro plus 2013 if that makes any difference?

Thank you in advance.

 

[Youngdand]

What number? a value of a cell? a number in the filename?

More details would probably help, even if you cannot upload, you could put a small example in your question.

Regards,

Dan.

 

[MatthewHughes]

Hello,

yep your right, was trying to type while at work.

The number in question is in Cell H4 but merged with cell I4 it is currently at number 19

Cheers Matt

 

[lrobbo314]

Go to the code for 'ThisWorkbook' and add the following code for the 'BeforePrint' event.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Range("H4").Value = Range("H4").Value + 1
End Sub

 

[MatthewHughes]

Thank you so much sir..

next question is how do i make it so 'user2' another networked computer can click save on the document to keep the numbers going up?

and the moment i get the message of read only on the other computer?

it is also an older version of office 2010?

Matt

 

[Youngdand]

I was halfway through a reply pre empting this question.

Shared workbooks, are never ideal, as one will always be locked or can be overwritten. You would better using a template, and possible having a log file somewhere else to store the last document number issued. you could trigger the document on open to check the log file for the last number and set that in the document, and then recorded back to the log file and close the log file. more like a database.

Regards,

Dan.

 

[MatthewHughes]

Dan,

i can build you a pc out of most components, i can understand some parts of game code.

but i think you just spoke witch craft.

basically i have hit my limit with excel.

if you can point me in the correct direction so i can learn this or give me big pointers i would be forever grateful.

Matt

 

[Youngdand]

Hi Matt,

I am a little busy this morning, but will try to put something together later, if no on else has.

Essentially. If you save your main document as a blank macro enabled template, you would then add in a small "on open" macro which would open another file, check the for the latest number, insert this in to the new file, then increase the value in the log file and save and close this straight away. this means that people will never have the same file open at the same time, and the log file will only be open for a second or so, meaning the chances of a lock will be small though not impossible.

As i say, if i can get to it later i will, but if someone else can step in, then so be it.

Regards,

Dan.

 

[MatthewHughes]

Dan,

thank you very much, just so i understand as well, upon each open the number will increase?

is it also possible that the number will increase up each print? i ask becuase when customers drop of a machine for a service sometimes you have to book in a few items at a time?

Cheer Matt

 

[Youngdand]

Hi Matt,

the below will still work on print, and not on open as requested.

You will need to create a file somewhere called logfile.xlsx and just enter the latest number you have used in cell a1 and save then close.
copy the below macro to the ThisWorkbook section of the VBA editor for the workbook you will be using, and change "c:\test\logfile.xlsx" to the path of your logfile

then save your file (or copy of to test)

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim TW As Workbook
Dim lf As Workbook
Set TW = ThisWorkbook
Set lf = Workbooks.Open("c:\test\logfile.xlsx")
With TW
ActiveSheet.Range("H4").Value = lf.Sheets(1).Range("a1").Value + 1
End With
With lf
Application.ScreenUpdating = False
.Sheets(1).Cells(1, 1).Value = .Sheets(1).Cells(1, 1).Value + 1
lf.Save
lf.Close
Application.ScreenUpdating = True
End With
End Sub

each time you print, it will update the value of H4 in the logfile, so the next time it is printed you will get the next available number.

Like i said, this is not infallible, but the logfile should only be open for a couple of seconds while you print, so the likelyhood of the file being locked is small.


Regards,

Dan.