RANDBETWEEN

bbworld

New Member
Joined
Jul 29, 2019
Messages
10
Hello,

I have built a user form I would like users to enter their details into.

This includes'

Date
Employee ID
Name
Email

I have a field called ID Gen where I am using for RANDBETWEEN formula to generate a 'unique' alphanumeric number. I say 'unique' as I have read that sometimes using the fomula, duplicates can happen.

Formula

=CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(65,90))&RANDBETWEEN(10,999999)

This produces a alphanumeric value such as "UI533792"

When in excel running it as a formula this works just fine. When I am trying to get this to work in the user form it fails to run. (exhaustive Google for VBA has not yielded any results either :( )

Finally, I am looking for some assistance with copying all the data from the screenshot via a transpose paste into a hidden worksheet. For the employee ID and email address, I would like to have these validated in the user form via the hidden worksheet and an error returned if a user with the employee ID or email address exist.

If this is all good then create the ID Gen and copy this over to the hidden worksheet and clear the content on the user form ready for the next input.

Cheers
Dan
 

Attachments

  • Rand1.PNG
    Rand1.PNG
    8.9 KB · Views: 6

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

GraH

Well-known Member
Joined
Mar 22, 2020
Messages
707
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
To make something unique you can also use a simple increment (with/without date/Timestamp combo) and store the last generated ID in a field. Can't help with the rest.
 

bbworld

New Member
Joined
Jul 29, 2019
Messages
10
@Fluff

I’m looking to understand more about generating a hex identifier with 8 characters that is unique and would enter into a field in a form recently debugged - thanks again @Fluff.


This is the final working form that just needs the identifier.

Look forward to learning more about variables.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,567
Messages
5,625,550
Members
416,116
Latest member
Joemamasuka

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top