Creating a unique form number upon Workbook Open

**lancekeith** · 03-15-2013, 12:03 PM

Hello,

I'm attempting to construct a form that will be used a few times each day. Each time the form is opened I want it to include a unique number in cell R3. This number should be MMDDYY-XX where XX is a random two digit number.

I'm a basic VBA user and have come up with the following:

Please Login or Register  to view this content.

This seems to work, except that each time I open the file it has the same two digit random number. Can somebody please explain to me why it isn't generating a new random number each time?

Thanks in advance
Lance

**HaHoBe** · 03-15-2013, 12:08 PM

Hi, Lance,

maybe use Randomize:

Please Login or Register  to view this content.

I´d go with a counter instead of a random number which should be formatted as 2 digits if wanted.

Ciao,
Holger

**lancekeith** · 03-15-2013, 12:24 PM

Holger,
The randomize worked, thanks.

I like the idea of a counter, but unsure how to go about it. What would that look like so that the counter would reset daily?

Thanks

**HaHoBe** · 03-15-2013, 01:12 PM

Hi, lancekeith,

maybe like this:

Please Login or Register  to view this content.

To make sure that it works in each every new year as well I would change the datestamp from mmddyy to yymmdd.

Ciao,
Holger

**lancekeith** · 03-15-2013, 01:38 PM

Holger,

I tried your code, like this

Please Login or Register  to view this content.

I believe I understand what it is supposed to do, and it makes sense, but it doesn't seem to work.
Lance

**HaHoBe** · 03-15-2013, 01:52 PM

Hi, Lance,

... but it doesn't seem to work.

Do you get a run-time error or is the string not put into the proper range (the code refers to the active sheet as no worksheet name is listed)? Maybe have a look at the attached sample workbook.

Ciao,
Holger