Creating username and passwords

[shannyshanhan]

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

[Bob Phillips]

=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" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>

[Scoops]

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

[Scoops]

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