I found an old post that I was able to modify, but I still have a couple
questions. Here is a copy/paste of what I posted to the old post from January:
-----------------------------------------------------------------------------

I too was looking for a way to create passwords and came across this post. I
am not trying to look across different worksheets, which makes it easier. I
have the person's last name in Column C and the Passwords will go in Column
D. I have a header row, so my data starts on Row 2. It works nicely except I
have a few questions,

1) The current code takes the last value from Column C and places it in
Column D, Row 2. This is not correct. It should take the value from Column C,
Row 2 and concatenate it as it does with the rest of the cells. How do I make
it do that?

2) Is there a way to scramble "C2"? For example, if a person's last name is
"Smith", it generates the following password: "&pSmith!V". I would rather it
scramble the "Smith" part of the password. So it might read for example,
"&phSimt!V". Is this possible?

3) As far as scrambling goes, is it possible to just create a random
character password not based on another cell value? If so, that would be the
best solution.

Here's the code I modified from the earlier post:
----------------------------------------------------------------

Sub Password()
Dim Cell As Range

For Each Cell In Range(Range("c2"), Range("c2").End(xlDown))
Cell.Offset(0, 1).Value = MakeNewPassword(Cell.Value)
Next

End Sub

Private Function MakeNewPassword(UserName As String) As String
Dim pass1 As String

'Using C1 to hold the UserName
With Sheets("Phase_1")
.Range("d2").Value = UserName
pass1 = .Range("d2")
MakeNewPassword = "&p" & pass1 & "!V"

End With

End Function
------------------------------------------------------------
Thanks for any help!


"mikeolson" wrote:

I just ran it again and the PASSWORD IN F13 is not the correct password for
A13, it is for the last company listed in column A


"NickHK" wrote:

Mike,
From your description, I still have the feeling that you are only generating
a password for the first company. e.g.
Sheet2!A1=Pass1(Sheet1!A13) "B10"
Sheet2!A2=Pass2(Sheet1!A13) "D20"

If Sheet2 A1 & A2 use formulas connected to Sheet1 A13, then they cannot
process any other companies located in Sheet1 A14+.
You need to update Sheet2 to point to A14 onwards, otherwise there is no
connection between your company list and generating a Password for each.

However, as you have a worksheet method of password generation, why not just
fill down a column on Sheet1 with these formulas instead and forget the VBA.
Seems that worksheet function INDIRECT may be useful to you here.

Unless I'm missing something, which is quite likely...

NickHK

"mikeolson" wrote in message
...
That didn't work either.

1) Sheet 1, A13 has: "company"
2) Sheet 2, A1 turns the "com" into a code let's say B10 (as in cell), in
that cell is a value (I have a large grid with random numbers in it)
3) Sheet 2, A2 turns "pany" into a code let's say D20 (as in cell), in
that
cell is a value
4) My macro looks up the value in cell B10 & it's 1234 and looks up the
value in cell D20 and it's 5678, concatenates PASS+1234+5678 =
PASS12345678
and places it in Sheet 2, A3 and Sheet 1, F13
5) Since each client will get a new code in Sheet 2 A1 & A2 to get from
the
grid, A3 will have a new PASS********
6) So, as I understand it, for each customer, Sheet 2, cells A1, A2, A3
are
unique, A1 & A2 contain formulas, A3 is a value placed from the macro, as
it
looks for the 2nd customer name, it needs to place their password in A3
and
F14, the 3rd customer would need to be in A3 (as they all will) and F15
and
so on
This is where I am stuck. It looks up Sheet 1 A13, calculates the values
in
Sheet 2 A1 & A2, macro looks up the grid for the corrsponding values in A1
&
A2 and gives me the final password in A3 and I have it placed again in
Sheet
1 on the same row as that customer (F13), I cannot get it to repeat this
process on A14&F14 until it sees a blank line in column A, which means
there's no customer there, I then do not need a password. I also have a
column N with a yes or no in it for e-mailing the password, this works,
if
the yes or no should be the verification to create a password or not that
would work also. Thank you for your time, this one's driving me crazy! :)

Mike

"NickHK" wrote:

Mike,
The looping code works fine; it's you MakeNewPassword routine that is
wrong.
I see no connection between the CompanyName and actually making the
password. Surely you need to set some cell value = CompanyName to
actually
change something.

You only need to return the password from the function, not reset this
hardcoded cell each. So delete this line:
Sheets("Sheet1").Range("F13") = "PASS" & MakeNewPassword

NickHK

"mikeolson" wrote in message
...
It works for a single password only. It found my client in A13,
retrieved
the password from my 2nd sheet based on the name of the client
scrambled
and
placed the unique password in F13. It did not go to A14 and get their
password and post it in F14. Sheet 2 cell A1 & A2 scrambles & "codes"
their
company name and A3 puts them together to create the password. Here
is
what
I used:
'
Sub Button1_Click()
Dim Cell As Range

For Each Cell In Range(Range("a13"), Range("a13").End(xlDown))
Cell.Offset(0, 6).Value = MakeNewPassword(Cell.Value)
Next

End Sub

Private Function MakeNewPassword(CompanyName As String) As String
Dim pass1 As String
Dim pass2 As String

pass1 = Sheets("Sheet2").Range("A1")
pass2 = Sheets("Sheet2").Range("A2")
MakeNewPassword = Sheets("Sheet2").Range(pass1) &
Sheets("Sheet2").Range(pass2)
Sheets("Sheet2").Range("A3") = "PASS" & MakeNewPassword
Sheets("Sheet1").Range("F13") = "PASS" & MakeNewPassword

End Function
'

So when it's all done it would assign as many unique passwords in
column F
starting at row 13 as there are unique company names in column A
starting
at
row 13 until it finds an empty cell in column A, it stops and does not
place
a password in column F corresponding to the blank cell in A

Thank you for your help on this!

Mike



"NickHK" wrote:

Mike,
Something like this ?

Private Sub CommandButton2_Click()
Dim Cell As Range

For Each Cell In Range(Range("a13"), Range("a13").End(xlDown))
Cell.Offset(0, 6).Value = MakeNewPassword(Cell.Value)
Next

End Sub

Private Function MakeNewPassword(CompanyName As String) As String
'However you generate the PW
MakeNewPassword = StrReverse(CompanyName)
End Function

NickHK

"mikeolson" wrote in message
...
I have a company client list in column A rows 13:62. Column F is
where
I
put
their new password, Column H is the contact name, column I is
their
e-mail
address. I have a macro that looks at the name in A13, calculates
a
unique
password and places it in F13. I want the macro to move on to A14
and
put
their password in F14 and so on until it sees an empty cell in
column
A.

I failed to mention what I'm looking for in a password. The rules would be:
1) 8 characters/numbers mixed
2) Upper and Lower case characters
3) Include at least 1 number
4) Special characters 1-0 are okay.

Thanks for any help,

Sharon

"Sharon" wrote:

I found an old post that I was able to modify, but I still have a couple
questions. Here is a copy/paste of what I posted to the old post from January:
-----------------------------------------------------------------------------

I too was looking for a way to create passwords and came across this post. I
am not trying to look across different worksheets, which makes it easier. I
have the person's last name in Column C and the Passwords will go in Column
D. I have a header row, so my data starts on Row 2. It works nicely except I
have a few questions,

1) The current code takes the last value from Column C and places it in
Column D, Row 2. This is not correct. It should take the value from Column C,
Row 2 and concatenate it as it does with the rest of the cells. How do I make
it do that?

2) Is there a way to scramble "C2"? For example, if a person's last name is
"Smith", it generates the following password: "&pSmith!V". I would rather it
scramble the "Smith" part of the password. So it might read for example,
"&phSimt!V". Is this possible?

3) As far as scrambling goes, is it possible to just create a random
character password not based on another cell value? If so, that would be the
best solution.

Here's the code I modified from the earlier post:
----------------------------------------------------------------

Sub Password()
Dim Cell As Range

For Each Cell In Range(Range("c2"), Range("c2").End(xlDown))
Cell.Offset(0, 1).Value = MakeNewPassword(Cell.Value)
Next

End Sub

Private Function MakeNewPassword(UserName As String) As String
Dim pass1 As String

'Using C1 to hold the UserName
With Sheets("Phase_1")
.Range("d2").Value = UserName
pass1 = .Range("d2")
MakeNewPassword = "&p" & pass1 & "!V"

End With

End Function
------------------------------------------------------------
Thanks for any help!


"mikeolson" wrote:

I just ran it again and the PASSWORD IN F13 is not the correct password for
A13, it is for the last company listed in column A


"NickHK" wrote:

Mike,
From your description, I still have the feeling that you are only generating
a password for the first company. e.g.
Sheet2!A1=Pass1(Sheet1!A13) "B10"
Sheet2!A2=Pass2(Sheet1!A13) "D20"

If Sheet2 A1 & A2 use formulas connected to Sheet1 A13, then they cannot
process any other companies located in Sheet1 A14+.
You need to update Sheet2 to point to A14 onwards, otherwise there is no
connection between your company list and generating a Password for each.

However, as you have a worksheet method of password generation, why not just
fill down a column on Sheet1 with these formulas instead and forget the VBA.
Seems that worksheet function INDIRECT may be useful to you here.

Unless I'm missing something, which is quite likely...

NickHK

"mikeolson" wrote in message
...
That didn't work either.

1) Sheet 1, A13 has: "company"
2) Sheet 2, A1 turns the "com" into a code let's say B10 (as in cell), in
that cell is a value (I have a large grid with random numbers in it)
3) Sheet 2, A2 turns "pany" into a code let's say D20 (as in cell), in
that
cell is a value
4) My macro looks up the value in cell B10 & it's 1234 and looks up the
value in cell D20 and it's 5678, concatenates PASS+1234+5678 =
PASS12345678
and places it in Sheet 2, A3 and Sheet 1, F13
5) Since each client will get a new code in Sheet 2 A1 & A2 to get from
the
grid, A3 will have a new PASS********
6) So, as I understand it, for each customer, Sheet 2, cells A1, A2, A3
are
unique, A1 & A2 contain formulas, A3 is a value placed from the macro, as
it
looks for the 2nd customer name, it needs to place their password in A3
and
F14, the 3rd customer would need to be in A3 (as they all will) and F15
and
so on
This is where I am stuck. It looks up Sheet 1 A13, calculates the values
in
Sheet 2 A1 & A2, macro looks up the grid for the corrsponding values in A1
&
A2 and gives me the final password in A3 and I have it placed again in
Sheet
1 on the same row as that customer (F13), I cannot get it to repeat this
process on A14&F14 until it sees a blank line in column A, which means
there's no customer there, I then do not need a password. I also have a
column N with a yes or no in it for e-mailing the password, this works,
if
the yes or no should be the verification to create a password or not that
would work also. Thank you for your time, this one's driving me crazy! :)

Mike

"NickHK" wrote:

Mike,
The looping code works fine; it's you MakeNewPassword routine that is
wrong.
I see no connection between the CompanyName and actually making the
password. Surely you need to set some cell value = CompanyName to
actually
change something.

You only need to return the password from the function, not reset this
hardcoded cell each. So delete this line:
Sheets("Sheet1").Range("F13") = "PASS" & MakeNewPassword

NickHK

"mikeolson" wrote in message
...
It works for a single password only. It found my client in A13,
retrieved
the password from my 2nd sheet based on the name of the client
scrambled
and
placed the unique password in F13. It did not go to A14 and get their
password and post it in F14. Sheet 2 cell A1 & A2 scrambles & "codes"
their
company name and A3 puts them together to create the password. Here
is
what
I used:
'
Sub Button1_Click()
Dim Cell As Range

For Each Cell In Range(Range("a13"), Range("a13").End(xlDown))
Cell.Offset(0, 6).Value = MakeNewPassword(Cell.Value)
Next

End Sub

Private Function MakeNewPassword(CompanyName As String) As String
Dim pass1 As String
Dim pass2 As String

pass1 = Sheets("Sheet2").Range("A1")
pass2 = Sheets("Sheet2").Range("A2")
MakeNewPassword = Sheets("Sheet2").Range(pass1) &
Sheets("Sheet2").Range(pass2)
Sheets("Sheet2").Range("A3") = "PASS" & MakeNewPassword
Sheets("Sheet1").Range("F13") = "PASS" & MakeNewPassword

End Function
'

So when it's all done it would assign as many unique passwords in
column F
starting at row 13 as there are unique company names in column A
starting
at
row 13 until it finds an empty cell in column A, it stops and does not
place
a password in column F corresponding to the blank cell in A

Thank you for your help on this!

Mike



"NickHK" wrote:

Mike,
Something like this ?

Private Sub CommandButton2_Click()
Dim Cell As Range

For Each Cell In Range(Range("a13"), Range("a13").End(xlDown))
Cell.Offset(0, 6).Value = MakeNewPassword(Cell.Value)
Next

End Sub

Private Function MakeNewPassword(CompanyName As String) As String
'However you generate the PW
MakeNewPassword = StrReverse(CompanyName)
End Function

NickHK

"mikeolson" wrote in message
...
I have a company client list in column A rows 13:62. Column F is
where
I
put
their new password, Column H is the contact name, column I is
their
e-mail
address. I have a macro that looks at the name in A13, calculates
a
unique
password and places it in F13. I want the macro to move on to A14
and
put
their password in F14 and so on until it sees an empty cell in
column
A.

I failed to mention what I'm looking for in a password. The rules would be:
1) 8 characters/numbers mixed
2) Upper and Lower case characters
3) Include at least 1 number
4) Special characters 1-0 are okay.

Thanks for any help,

Sharon

"Sharon" wrote:

I found an old post that I was able to modify, but I still have a couple
questions. Here is a copy/paste of what I posted to the old post from January:
-----------------------------------------------------------------------------

I too was looking for a way to create passwords and came across this post. I
am not trying to look across different worksheets, which makes it easier. I
have the person's last name in Column C and the Passwords will go in Column
D. I have a header row, so my data starts on Row 2. It works nicely except I
have a few questions,

1) The current code takes the last value from Column C and places it in
Column D, Row 2. This is not correct. It should take the value from Column C,
Row 2 and concatenate it as it does with the rest of the cells. How do I make
it do that?

2) Is there a way to scramble "C2"? For example, if a person's last name is
"Smith", it generates the following password: "&pSmith!V". I would rather it
scramble the "Smith" part of the password. So it might read for example,
"&phSimt!V". Is this possible?

3) As far as scrambling goes, is it possible to just create a random
character password not based on another cell value? If so, that would be the
best solution.

Here's the code I modified from the earlier post:
----------------------------------------------------------------

Sub Password()
Dim Cell As Range

For Each Cell In Range(Range("c2"), Range("c2").End(xlDown))
Cell.Offset(0, 1).Value = MakeNewPassword(Cell.Value)
Next

End Sub

Private Function MakeNewPassword(UserName As String) As String
Dim pass1 As String

'Using C1 to hold the UserName
With Sheets("Phase_1")
.Range("d2").Value = UserName
pass1 = .Range("d2")
MakeNewPassword = "&p" & pass1 & "!V"

End With

End Function
------------------------------------------------------------
Thanks for any help!


"mikeolson" wrote:

I just ran it again and the PASSWORD IN F13 is not the correct password for
A13, it is for the last company listed in column A


"NickHK" wrote:

Mike,
From your description, I still have the feeling that you are only generating
a password for the first company. e.g.
Sheet2!A1=Pass1(Sheet1!A13) "B10"
Sheet2!A2=Pass2(Sheet1!A13) "D20"

If Sheet2 A1 & A2 use formulas connected to Sheet1 A13, then they cannot
process any other companies located in Sheet1 A14+.
You need to update Sheet2 to point to A14 onwards, otherwise there is no
connection between your company list and generating a Password for each.

However, as you have a worksheet method of password generation, why not just
fill down a column on Sheet1 with these formulas instead and forget the VBA.
Seems that worksheet function INDIRECT may be useful to you here.

Unless I'm missing something, which is quite likely...

NickHK

"mikeolson" wrote in message
...
That didn't work either.

1) Sheet 1, A13 has: "company"
2) Sheet 2, A1 turns the "com" into a code let's say B10 (as in cell), in
that cell is a value (I have a large grid with random numbers in it)
3) Sheet 2, A2 turns "pany" into a code let's say D20 (as in cell), in
that
cell is a value
4) My macro looks up the value in cell B10 & it's 1234 and looks up the
value in cell D20 and it's 5678, concatenates PASS+1234+5678 =
PASS12345678
and places it in Sheet 2, A3 and Sheet 1, F13
5) Since each client will get a new code in Sheet 2 A1 & A2 to get from
the
grid, A3 will have a new PASS********
6) So, as I understand it, for each customer, Sheet 2, cells A1, A2, A3
are
unique, A1 & A2 contain formulas, A3 is a value placed from the macro, as
it
looks for the 2nd customer name, it needs to place their password in A3
and
F14, the 3rd customer would need to be in A3 (as they all will) and F15
and
so on
This is where I am stuck. It looks up Sheet 1 A13, calculates the values
in
Sheet 2 A1 & A2, macro looks up the grid for the corrsponding values in A1
&
A2 and gives me the final password in A3 and I have it placed again in
Sheet
1 on the same row as that customer (F13), I cannot get it to repeat this
process on A14&F14 until it sees a blank line in column A, which means
there's no customer there, I then do not need a password. I also have a
column N with a yes or no in it for e-mailing the password, this works,
if
the yes or no should be the verification to create a password or not that
would work also. Thank you for your time, this one's driving me crazy! :)

Mike

"NickHK" wrote:

Mike,
The looping code works fine; it's you MakeNewPassword routine that is
wrong.
I see no connection between the CompanyName and actually making the
password. Surely you need to set some cell value = CompanyName to
actually
change something.

You only need to return the password from the function, not reset this
hardcoded cell each. So delete this line:
Sheets("Sheet1").Range("F13") = "PASS" & MakeNewPassword

NickHK

"mikeolson" wrote in message
...
It works for a single password only. It found my client in A13,
retrieved
the password from my 2nd sheet based on the name of the client
scrambled
and
placed the unique password in F13. It did not go to A14 and get their
password and post it in F14. Sheet 2 cell A1 & A2 scrambles & "codes"
their
company name and A3 puts them together to create the password. Here
is
what
I used:
'
Sub Button1_Click()
Dim Cell As Range

For Each Cell In Range(Range("a13"), Range("a13").End(xlDown))
Cell.Offset(0, 6).Value = MakeNewPassword(Cell.Value)
Next

End Sub

Private Function MakeNewPassword(CompanyName As String) As String
Dim pass1 As String
Dim pass2 As String

pass1 = Sheets("Sheet2").Range("A1")
pass2 = Sheets("Sheet2").Range("A2")
MakeNewPassword = Sheets("Sheet2").Range(pass1) &
Sheets("Sheet2").Range(pass2)
Sheets("Sheet2").Range("A3") = "PASS" & MakeNewPassword
Sheets("Sheet1").Range("F13") = "PASS" & MakeNewPassword

End Function
'

So when it's all done it would assign as many unique passwords in
column F
starting at row 13 as there are unique company names in column A
starting
at
row 13 until it finds an empty cell in column A, it stops and does not
place
a password in column F corresponding to the blank cell in A

Thank you for your help on this!

Mike



"NickHK" wrote:

Mike,
Something like this ?

Private Sub CommandButton2_Click()
Dim Cell As Range

For Each Cell In Range(Range("a13"), Range("a13").End(xlDown))
Cell.Offset(0, 6).Value = MakeNewPassword(Cell.Value)
Next

End Sub

Private Function MakeNewPassword(CompanyName As String) As String
'However you generate the PW
MakeNewPassword = StrReverse(CompanyName)
End Function

NickHK

"mikeolson" wrote in message
...
I have a company client list in column A rows 13:62. Column F is
where
I
put
their new password, Column H is the contact name, column I is
their
e-mail
address. I have a macro that looks at the name in A13, calculates
a
unique
password and places it in F13. I want the macro to move on to A14
and
put
their password in F14 and so on until it sees an empty cell in
column
A.