Automatically calculate random number as invoice number

 Blocked Profile -
Hello there!

I am fairly new to macros and am trying to create an excel document that automatically insert a new randomly generated number (as the invoice number) each time the document is opened. I then want the document to automatically save with the assigned invoice number as the file name.

I have used the macro for my random number generator:

Sub Increment_invoice()

Dim num As Integer
Range("B1").Value = Int((99999999 - 10000000 + 1) * Rnd + 10000000)
num = Range("B1").Value
num = num + 1
Range("B1").Value = num

End Sub

This seems to be working, I just can't get past this, any help would be greatly appreciated.


5 replies

Ok, so random number generation is probably the hardest thing to try to accomplish by code. I mean A REAL RANDOM NUMBER!

So let us talk about how to generate one, or let us talk about how one may be generated. Here are some different ways programmers may generate a random number. This is not an all exclusive list, and may be more like a charter, as it will grow as more input is given.

Let us start:
1. Get the second of the minute and multiply it by the minute, then take that and multiply it by the hour.
2. Take the Second of the minute and multiply it by the X/y access of the mouse. this works if button isn't always in the same location on the screen.
3. if it is for order ticket generation, just serialize the juilien date with hour, and minute. So Julien date is the day of the year. The hour and minute will always increase with each minute. example of this would be 2 digit year, 3 digit day, 2 digit hour, 2 digit minute for something like - 140711624. In this example, you would be able to track what year, what day, what minute it was made. It would give you a good idea of who also, based on that sequence!

You could always link to a sheet that is a counter. Get that number, add one to it, and close it. This is what I do, but I make a connection to "master couter" as a Database query, so that I can have mutliple machines never generate the same ticket.

Let us know what you think! ALWAYS use your imagination!

Have fun!

"If you can't soar with the eagles, then don't fly with the flock!" - Oliver Sykes; Bring Me The Horizon
Thanks for your response ac3mark. My number generator is working fine, when I attach it to a shape. I just can't get it to generate a new number, each time that I open the work book.
Any ideas?

OK, lets try this then:

Let us know if this helps?

As far as save as file name auto fill type behavior, no. I would hope that you would appreciate your machine not thinking this much for you!

I hope that helped!

"If you can't soar with the eagles, then don't fly with the flock!" - Oliver Sykes; Bring Me The Horizon
Ok, so that works fabulously, THANKS! I have saved the workbook as a macro enabled template. I have a couple of queries:
1. Can I save it as a normal work book?; and
2. After they save as a normal excel spreadsheet, I would like the automatic date/time update to stop? So the ticket number stays the same?


Alright good to hear! You are so welcome.

1. Yes save it as a normal workbook, as there are no macros!
2. Ok, this is a tough one initially, as one would NOT want to be able to programatically "LOCK" a cell, or sheet. So , let us look into if we can....

...hang tight...let us see!

Will you mark this solved and let us open a new query for the other question? Sound Good?

Use your imagination. Have Fun!

"If you can't soar with the eagles, then don't fly with the flock!" - Oliver Sykes; Bring Me The Horizon
All sorted. Thanks for your help:)
Blocked Profile
SO what was your fix, as I am the curious full circle type of guy!