Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Creating username and passwords
I need help creating all lower case usernames made up of the first letter of the customers first name and the first 7 letters of their last name. I also need help creating passwords which will be made up of a customers first and last initial, followed by a random 6 digit number. the first character in the password will be lower case and the second character will be upper case. Full Name Last Name First Name Username Password Boxer, Barbara Boxer Barbara Cantwell, Maria Cantwell Maria Craig, Larry Craig Larry Crapo, Michael Crapo Michael Ensign, John Ensign John Feinstein, Dianne Feinstein Dianne Murray, Patty Murray Patty Reid, Harry Reid Harry Smith, Gordon Smith Gordon Wyden, Ron Wyden Ron I need help creating functions for these tasks -- shannyshanhan ------------------------------------------------------------------------ shannyshanhan's Profile: http://www.excelforum.com/member.php...o&userid=37173 View this thread: http://www.excelforum.com/showthread...hreadid=568903 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Creating username and passwords
=LEFT(D1,1)&LEFT(C1,7)
and =LEFT(C1,1)&LEFT(D1,1)&RANDBETWEEN(100000,999999) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "shannyshanhan" wrote in message news:shannyshanhan.2c5vke_1154939710.0501@excelfor um-nospam.com... I need help creating all lower case usernames made up of the first letter of the customers first name and the first 7 letters of their last name. I also need help creating passwords which will be made up of a customers first and last initial, followed by a random 6 digit number. the first character in the password will be lower case and the second character will be upper case. Full Name Last Name First Name Username Password Boxer, Barbara Boxer Barbara Cantwell, Maria Cantwell Maria Craig, Larry Craig Larry Crapo, Michael Crapo Michael Ensign, John Ensign John Feinstein, Dianne Feinstein Dianne Murray, Patty Murray Patty Reid, Harry Reid Harry Smith, Gordon Smith Gordon Wyden, Ron Wyden Ron I need help creating functions for these tasks -- shannyshanhan ------------------------------------------------------------------------ shannyshanhan's Profile: http://www.excelforum.com/member.php...o&userid=37173 View this thread: http://www.excelforum.com/showthread...hreadid=568903 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Creating username and passwords
shannyshanhan wrote: I need help creating all lower case usernames made up of the first letter of the customers first name and the first 7 letters of their last name. I also need help creating passwords which will be made up of a customers first and last initial, followed by a random 6 digit number. the first character in the password will be lower case and the second character will be upper case. Full Name Last Name First Name Username Password Boxer, Barbara Boxer Barbara Cantwell, Maria Cantwell Maria Craig, Larry Craig Larry Crapo, Michael Crapo Michael Ensign, John Ensign John Feinstein, Dianne Feinstein Dianne Murray, Patty Murray Patty Reid, Harry Reid Harry Smith, Gordon Smith Gordon Wyden, Ron Wyden Ron I need help creating functions for these tasks -- Hi shannyshanhan As the Random function is volatile, whenever you enter a new user all previous users' passwords will change and you'll lose your control. So copy the following code into your worksheet (Right-click the sheet tab View Code and paste): Private Sub Worksheet_Change(ByVal Target As Range) Dim RndNmbr As String With Target Randomize If .Column = 3 Then .Offset(0, 1) = LCase(Left(.Offset(0, -1), 1)) & LCase(Left(.Offset(0, -2), 7)) RndNmbr = Int((999999 - 1 + 1) * Rnd + 1) Select Case Len(RndNmbr) Case 1: RndNmbr = "00000" & RndNmbr Case 2: RndNmbr = "0000" & RndNmbr Case 3: RndNmbr = "000" & RndNmbr Case 4: RndNmbr = "00" & RndNmbr Case 5: RndNmbr = "0" & RndNmbr End Select .Offset(0, 2) = LCase(Left(.Offset(0, -1), 1)) & UCase(Left(.Offset(0, -2), 1)) & RndNmbr End If End With End Sub This is based on your layout and will fire when the First Name is entered in column C. Regards Steve |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Creating username and passwords
Scoops wrote: But why waste processing time? Paste this instead (D'oh!): Private Sub Worksheet_Change(ByVal Target As Range) Dim RndNmbr As String With Target Randomize If .Column = 4 Then .Offset(0, 1) = LCase(Left(.Offset(0, -1), 1)) & LCase(Left(.Offset(0, -2), 7)) RndNmbr = Int((999999 - 100000 + 1) * Rnd + 1) .Offset(0, 2) = LCase(Left(.Offset(0, -1), 1)) & UCase(Left(.Offset(0, -2), 1))&RndNmbr End If End With End Sub Regards Steve |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is there a template for username and password storage for Excel? | Excel Discussion (Misc queries) |