random change contents of Excel cell?

April 22, 2009 at 16:51:19
Specs: Windows 2000

Is it possible to change a string of letters and numbers in a column in excel 2000?
I have two columns, one with a list of names, the next has their associated passwords, consisting of a random mix of letters and digits. Up to now, when ever I need to change the passwords (regularly) I've done it manually. Wondering if I can use a macro or something.

e.g.

Name Password
JohnT xtw35t6
MaryL upt7r43

Change to:
Name Password
JohnT 6t9e33L
MaryL r55eew9

Thanks.


See More: random change contents of Excel cell?

Report •


#1
Report •

#2
April 22, 2009 at 18:40:08

This code will place a new 7 character string in the selected cell.

While you will typically get a mixture of uppercase letters, lower case letters and numbers, the strings that are created are nothing more than 7 randomly selected characters from the list. You could end up with the perfect 7 character password or you could end up with actual words or 7 of the same letter or number - remember, it's random.

It would be very rare not to get a mixture since we have 62 characters to choose from each time through the loop, but each character is selected individually, so there is no guarantee of a strong password.

I did double up on the numbers since there are 26 each ucase and lcase letters, so I figured I'd increase the odds that they'll be a decent number of numbers selected.

If you have password rules that need to be adhered to, then we would need to know them and the code could get significantly more complicated.

Sub newPassword()
Randomize
'List of characters to use for passwords
 newPasswordChars = _
   "0123456789ABCDEFGHIJKLNOPQRSTUVWXYZ0123456789abcedefghijklmnopqrstuvwxyz"
'Length of Password
  Pass_Length = 7
'Build New Password by selecting 7 ramdom characters from list
   For bldPass = 1 To Pass_Length
'Randomly choose a character
    nxtPassChar = Int((Len(newPasswordChars) * Rnd) + 1)
'Append it to the end of the New Password
     NewPass = NewPass & Mid(newPasswordChars, nxtPassChar, 1)
   Next
Selection = NewPass
End Sub


Report •

#3
April 23, 2009 at 06:45:40

I beefed up the code a little to make the passwords stronger.

If the new password doesn't contain at least 2 uppercase letters, 2 lowercase letters and 2 numbers, the code will loop back and build a new one until the three criteria are met.

Of course, we still haven't dealt with the fairly common rule of not recycling passwords for some given amount of time. That could be done, but you would need to store all the old passwords for a given user and then check the new password - and date - against the list. It could be done, maybe by including a VLOOKUP in the code.

Anyway, here's the "stronger" code...

Sub newPassword()
Randomize
'List of characters to use for passwords
 newPasswordChars = _
   "0123456789ABCDEFGHIJKLNOPQRSTUVWXYZ0123456789abcedefghijklmnopqrstuvwxyz"
'Length of Password
  Pass_Length = 7
'Reset check variables
ruleFailed:
 NewPass = ""
 numChk = 0
 ucChk = 0
 lcChk = 0
'Build New Password by selecting 7 ramdom characters from list
   For bldPass = 1 To Pass_Length
'Randomly choose a character
     nxtPassChar = Int((Len(newPasswordChars) * Rnd) + 1)
'Append it to the end of the New Password
     NewPass = NewPass & Mid(newPasswordChars, nxtPassChar, 1)
   Next
'Check NewPass for 2 numbers, 2 UCase letters and 2 LCase letters
   For chkPass = 1 To Pass_Length
    If IsNumeric(Mid(NewPass, chkPass, 1)) Then
        numChk = numChk + 1
        GoTo gotNum
    End If
    If UCase(Mid(NewPass, chkPass, 1)) = Mid(NewPass, chkPass, 1) Then _
        ucChk = ucChk + 1
    If LCase(Mid(NewPass, chkPass, 1)) = Mid(NewPass, chkPass, 1) Then _
        lcChk = lcChk + 1
gotNum:
    Next
'Build new password if any rule fails
    If numChk < 2 Or ucChk < 2 Or lcChk < 2 Then GoTo ruleFailed
Selection = NewPass
End Sub


Report •

Related Solutions

#4
April 24, 2009 at 05:20:32

Great guys! Thanks for the help.

Report •

#5
April 24, 2009 at 07:17:18

One more point to be aware of related to the solution found at the tushar-mehta site.

Assuming that calculation is set to automatic, any formula based solution is going to fire each time the worksheet is changed in any manner, thus changing every password.

You'd probably have to generate the passwords in another sheet and copy the value - not the formula - to your password sheet.


Report •


Ask Question