Excel 2007 & 2010 Static Random Unique Alphanumeric string across multiple sheets

[svignan]

Hi,

I am new to VBA and wanted to make an excel file. I need to create an excel file with 15 sheets and each sheet will have 6 columns;

1. Member ID (Mandatory)
2. First Name (Mandatory)
3. Last Name (Mandatory)
4. Gender (Mandatory)
5. Date of Birth (MM/DD/YY) format (Mandatory)
6. Member Code

All fields will be blank initially. Once a user enters/selects a value in the Member ID, First Name, Last Name, Gender & Date of Birth column, a Unique, Random, 15 character alphanumeric string (only upper case alphabets, no lower case alphabets) should be generated in the Member Code column.

The value should not change even when data for next row is entered. After saving the excel workbook and re-opening it, it should retain all the unique random 15 character strings generated.

I found a good function that can create a 15 character string as required, but I am unable to make it unique within the sheet & across the sheets in a workbook.

The function is taken from this site
http://www.ozgrid.com/forum/showthread.php?t=19314

I modified the function slightly to suit my needs and changed it to this

Please Login or Register  to view this content.

I can throw in some conditional statements to not run this script if the previous columns are empty, but I am unable to make it unique.

Kindly help me out here.

Thanks in anticipation !!!
Vignan

[abousetta]

Hi,

A very minor change that will check to make sure that the generated code is unique prior to printing it to the worksheet.

Please Login or Register  to view this content.

Additionally you should be calling this from a sub and leaving only the values or the code could change if someone accidentally clicks inside the cell.

Hope this helps.

abousetta