I have a workbook with several worksheets. I have tried to limit user
interaction and have some security processes built in. So what I have done
is made a lookup range called €œPassword€�. This range is made up of three
columns: Column E €œPassword€�, Column F €œName€� Column G is €œInitials€�. It
covers rows 17 thru 50. The way it works is a command button brings a
userform to add a name. When the name is added to Column F the formula
=IF(ISERROR(UPPER(LEFT(F17,1)&MID(F17,FIND(" ",F17)+1,1)&MID(F17,FIND("
",F17,1)+3,1))),"",UPPER(LEFT(F17,1)&MID(F17,FIND( "
",F17)+1,1)&MID(F17,FIND(" ",F17,1)+3,1))) in column G reads column F and
places the persons initials in column G. At the same time Column E with the
formula: =IF(ISTEXT(F17),M17,"") reads column F and if there is text it
places a 4 digit password number from column M. These numbers are generated
by a macro that places them in M17:M50 and can be changed or updated by
re-running the macro. Column €œM€� is now hidden. Now I have the platform to
use for the security issues. In other worksheets it is necessary for the
users to place their initials in the forms that I have built. Using the
formula: IF(ISERROR(VLOOKUP(D38,Password,3,0)),"",VLOOKUP(D 38,Password,3,0))
I should be able to enter the four number password associated with the
persons name and it should place their initials in the appropriate cell. The
problem is, it does not work! If I manually enter a number in the password
column it works, but with the random numbers generated by the macro it does
not work. I thought it might be the formula in column E that was giving me
grief, so I changed the range in the macro to place the number directly into
column E, then use conditional formatting to hide the ones not in use. That
did not work either. When I mean it does not work, when the password in
entered into the userform, which places it in the appropriate cell, nothing
happens. Like it is a wrong password for the name. Does this have something
to do with the range of random numbers generated by the macro? I would like
the functionality to update the passwords from time to time for security
reasons; otherwise I would just manually enter some in and be done. Any
ideas would be appreciated!!

Mike Rogers
PS: EVERYTHING I have put into the project I have learned from this forum,
THANKS everyone!!!!

Hi!

First thing that comes to my mind is that the macro generated "numbers" are
really TEXT strings?

The "tip-off" is that as you say, if you then manually enter a number , then
it works.

Biff

"Mike Rogers" wrote in message
...
I have a workbook with several worksheets. I have tried to limit user
interaction and have some security processes built in. So what I have
done
is made a lookup range called "Password". This range is made up of three
columns: Column E "Password", Column F "Name" Column G is "Initials". It
covers rows 17 thru 50. The way it works is a command button brings a
userform to add a name. When the name is added to Column F the formula
=IF(ISERROR(UPPER(LEFT(F17,1)&MID(F17,FIND(" ",F17)+1,1)&MID(F17,FIND("
",F17,1)+3,1))),"",UPPER(LEFT(F17,1)&MID(F17,FIND( "
",F17)+1,1)&MID(F17,FIND(" ",F17,1)+3,1))) in column G reads column F and
places the persons initials in column G. At the same time Column E with
the
formula: =IF(ISTEXT(F17),M17,"") reads column F and if there is text it
places a 4 digit password number from column M. These numbers are
generated
by a macro that places them in M17:M50 and can be changed or updated by
re-running the macro. Column "M" is now hidden. Now I have the platform
to
use for the security issues. In other worksheets it is necessary for the
users to place their initials in the forms that I have built. Using the
formula:
IF(ISERROR(VLOOKUP(D38,Password,3,0)),"",VLOOKUP(D 38,Password,3,0))
I should be able to enter the four number password associated with the
persons name and it should place their initials in the appropriate cell.
The
problem is, it does not work! If I manually enter a number in the
password
column it works, but with the random numbers generated by the macro it
does
not work. I thought it might be the formula in column E that was giving me
grief, so I changed the range in the macro to place the number directly
into
column E, then use conditional formatting to hide the ones not in use.
That
did not work either. When I mean it does not work, when the password in
entered into the userform, which places it in the appropriate cell,
nothing
happens. Like it is a wrong password for the name. Does this have
something
to do with the range of random numbers generated by the macro? I would
like
the functionality to update the passwords from time to time for security
reasons; otherwise I would just manually enter some in and be done. Any
ideas would be appreciated!!

Mike Rogers
PS: EVERYTHING I have put into the project I have learned from this forum,
THANKS everyone!!!!

Biff,

Thanks for the response... You were right on!!! I looked in the macro and
here is the formula that gives me the grief:
myCell.FormulaR1C1 = "=TEXT(ROUND(10000*RAND(),0),""0000"")" Now for the
next question. Can I just remove the word "TEXT" from the formula. Or what
do I need to change.

Thanks again
Mike Rogers




"Biff" wrote:

Hi!

First thing that comes to my mind is that the macro generated "numbers" are
really TEXT strings?

The "tip-off" is that as you say, if you then manually enter a number , then
it works.

Biff

"Mike Rogers" wrote in message
...
I have a workbook with several worksheets. I have tried to limit user
interaction and have some security processes built in. So what I have
done
is made a lookup range called "Password". This range is made up of three
columns: Column E "Password", Column F "Name" Column G is "Initials". It
covers rows 17 thru 50. The way it works is a command button brings a
userform to add a name. When the name is added to Column F the formula
=IF(ISERROR(UPPER(LEFT(F17,1)&MID(F17,FIND(" ",F17)+1,1)&MID(F17,FIND("
",F17,1)+3,1))),"",UPPER(LEFT(F17,1)&MID(F17,FIND( "
",F17)+1,1)&MID(F17,FIND(" ",F17,1)+3,1))) in column G reads column F and
places the persons initials in column G. At the same time Column E with
the
formula: =IF(ISTEXT(F17),M17,"") reads column F and if there is text it
places a 4 digit password number from column M. These numbers are
generated
by a macro that places them in M17:M50 and can be changed or updated by
re-running the macro. Column "M" is now hidden. Now I have the platform
to
use for the security issues. In other worksheets it is necessary for the
users to place their initials in the forms that I have built. Using the
formula:
IF(ISERROR(VLOOKUP(D38,Password,3,0)),"",VLOOKUP(D 38,Password,3,0))
I should be able to enter the four number password associated with the
persons name and it should place their initials in the appropriate cell.
The
problem is, it does not work! If I manually enter a number in the
password
column it works, but with the random numbers generated by the macro it
does
not work. I thought it might be the formula in column E that was giving me
grief, so I changed the range in the macro to place the number directly
into
column E, then use conditional formatting to hide the ones not in use.
That
did not work either. When I mean it does not work, when the password in
entered into the userform, which places it in the appropriate cell,
nothing
happens. Like it is a wrong password for the name. Does this have
something
to do with the range of random numbers generated by the macro? I would
like
the functionality to update the passwords from time to time for security
reasons; otherwise I would just manually enter some in and be done. Any
ideas would be appreciated!!

Mike Rogers
PS: EVERYTHING I have put into the project I have learned from this forum,
THANKS everyone!!!!

Removing the word "TEXT" does not work, no suprise to you though. I did get
it working, but I think there is a better way. What I did was multiply the
range by one and it works, but I would think there was a "cleaner" way of
addressing the problem.

Mike Rogers

"Mike Rogers" wrote:

Biff,

Thanks for the response... You were right on!!! I looked in the macro and
here is the formula that gives me the grief:
myCell.FormulaR1C1 = "=TEXT(ROUND(10000*RAND(),0),""0000"")" Now for the
next question. Can I just remove the word "TEXT" from the formula. Or what
do I need to change.

Thanks again
Mike Rogers




"Biff" wrote:

Hi!

First thing that comes to my mind is that the macro generated "numbers" are
really TEXT strings?

The "tip-off" is that as you say, if you then manually enter a number , then
it works.

Biff

"Mike Rogers" wrote in message
...
I have a workbook with several worksheets. I have tried to limit user
interaction and have some security processes built in. So what I have
done
is made a lookup range called "Password". This range is made up of three
columns: Column E "Password", Column F "Name" Column G is "Initials". It
covers rows 17 thru 50. The way it works is a command button brings a
userform to add a name. When the name is added to Column F the formula
=IF(ISERROR(UPPER(LEFT(F17,1)&MID(F17,FIND(" ",F17)+1,1)&MID(F17,FIND("
",F17,1)+3,1))),"",UPPER(LEFT(F17,1)&MID(F17,FIND( "
",F17)+1,1)&MID(F17,FIND(" ",F17,1)+3,1))) in column G reads column F and
places the persons initials in column G. At the same time Column E with
the
formula: =IF(ISTEXT(F17),M17,"") reads column F and if there is text it
places a 4 digit password number from column M. These numbers are
generated
by a macro that places them in M17:M50 and can be changed or updated by
re-running the macro. Column "M" is now hidden. Now I have the platform
to
use for the security issues. In other worksheets it is necessary for the
users to place their initials in the forms that I have built. Using the
formula:
IF(ISERROR(VLOOKUP(D38,Password,3,0)),"",VLOOKUP(D 38,Password,3,0))
I should be able to enter the four number password associated with the
persons name and it should place their initials in the appropriate cell.
The
problem is, it does not work! If I manually enter a number in the
password
column it works, but with the random numbers generated by the macro it
does
not work. I thought it might be the formula in column E that was giving me
grief, so I changed the range in the macro to place the number directly
into
column E, then use conditional formatting to hide the ones not in use.
That
did not work either. When I mean it does not work, when the password in
entered into the userform, which places it in the appropriate cell,
nothing
happens. Like it is a wrong password for the name. Does this have
something
to do with the range of random numbers generated by the macro? I would
like
the functionality to update the passwords from time to time for security
reasons; otherwise I would just manually enter some in and be done. Any
ideas would be appreciated!!

Mike Rogers
PS: EVERYTHING I have put into the project I have learned from this forum,
THANKS everyone!!!!

I would think there was a "cleaner" way of
addressing the problem

Actually, that's a pretty good way to do it.!

Or:

=--TEXT(ROUND(10000*RAND(),0),"0000")

Biff

"Mike Rogers" wrote in message
...
Removing the word "TEXT" does not work, no suprise to you though. I did
get
it working, but I think there is a better way. What I did was multiply
the
range by one and it works, but I would think there was a "cleaner" way of
addressing the problem.

Mike Rogers

"Mike Rogers" wrote:

Biff,

Thanks for the response... You were right on!!! I looked in the macro and
here is the formula that gives me the grief:
myCell.FormulaR1C1 = "=TEXT(ROUND(10000*RAND(),0),""0000"")" Now for the
next question. Can I just remove the word "TEXT" from the formula. Or
what
do I need to change.

Thanks again
Mike Rogers




"Biff" wrote:

Hi!

First thing that comes to my mind is that the macro generated "numbers"
are
really TEXT strings?

The "tip-off" is that as you say, if you then manually enter a number ,
then
it works.

Biff

"Mike Rogers" wrote in message
...
I have a workbook with several worksheets. I have tried to limit user
interaction and have some security processes built in. So what I
have
done
is made a lookup range called "Password". This range is made up of
three
columns: Column E "Password", Column F "Name" Column G is "Initials".
It
covers rows 17 thru 50. The way it works is a command button brings a
userform to add a name. When the name is added to Column F the
formula
=IF(ISERROR(UPPER(LEFT(F17,1)&MID(F17,FIND("
",F17)+1,1)&MID(F17,FIND("
",F17,1)+3,1))),"",UPPER(LEFT(F17,1)&MID(F17,FIND( "
",F17)+1,1)&MID(F17,FIND(" ",F17,1)+3,1))) in column G reads column F
and
places the persons initials in column G. At the same time Column E
with
the
formula: =IF(ISTEXT(F17),M17,"") reads column F and if there is text
it
places a 4 digit password number from column M. These numbers are
generated
by a macro that places them in M17:M50 and can be changed or updated
by
re-running the macro. Column "M" is now hidden. Now I have the
platform
to
use for the security issues. In other worksheets it is necessary for
the
users to place their initials in the forms that I have built. Using
the
formula:
IF(ISERROR(VLOOKUP(D38,Password,3,0)),"",VLOOKUP(D 38,Password,3,0))
I should be able to enter the four number password associated with
the
persons name and it should place their initials in the appropriate
cell.
The
problem is, it does not work! If I manually enter a number in the
password
column it works, but with the random numbers generated by the macro
it
does
not work. I thought it might be the formula in column E that was
giving me
grief, so I changed the range in the macro to place the number
directly
into
column E, then use conditional formatting to hide the ones not in
use.
That
did not work either. When I mean it does not work, when the password
in
entered into the userform, which places it in the appropriate cell,
nothing
happens. Like it is a wrong password for the name. Does this have
something
to do with the range of random numbers generated by the macro? I
would
like
the functionality to update the passwords from time to time for
security
reasons; otherwise I would just manually enter some in and be done.
Any
ideas would be appreciated!!

Mike Rogers
PS: EVERYTHING I have put into the project I have learned from this
forum,
THANKS everyone!!!!

Biff

Thanks for the help and thaks for being on this forum helping all of us
that are learning.

Mike Rogers

"Biff" wrote:

I would think there was a "cleaner" way of
addressing the problem

Actually, that's a pretty good way to do it.!

Or:

=--TEXT(ROUND(10000*RAND(),0),"0000")

Biff

"Mike Rogers" wrote in message
...
Removing the word "TEXT" does not work, no suprise to you though. I did
get
it working, but I think there is a better way. What I did was multiply
the
range by one and it works, but I would think there was a "cleaner" way of
addressing the problem.

Mike Rogers

"Mike Rogers" wrote:

Biff,

Thanks for the response... You were right on!!! I looked in the macro and
here is the formula that gives me the grief:
myCell.FormulaR1C1 = "=TEXT(ROUND(10000*RAND(),0),""0000"")" Now for the
next question. Can I just remove the word "TEXT" from the formula. Or
what
do I need to change.

Thanks again
Mike Rogers




"Biff" wrote:

Hi!

First thing that comes to my mind is that the macro generated "numbers"
are
really TEXT strings?

The "tip-off" is that as you say, if you then manually enter a number ,
then
it works.

Biff

"Mike Rogers" wrote in message
...
I have a workbook with several worksheets. I have tried to limit user
interaction and have some security processes built in. So what I
have
done
is made a lookup range called "Password". This range is made up of
three
columns: Column E "Password", Column F "Name" Column G is "Initials".
It
covers rows 17 thru 50. The way it works is a command button brings a
userform to add a name. When the name is added to Column F the
formula
=IF(ISERROR(UPPER(LEFT(F17,1)&MID(F17,FIND("
",F17)+1,1)&MID(F17,FIND("
",F17,1)+3,1))),"",UPPER(LEFT(F17,1)&MID(F17,FIND( "
",F17)+1,1)&MID(F17,FIND(" ",F17,1)+3,1))) in column G reads column F
and
places the persons initials in column G. At the same time Column E
with
the
formula: =IF(ISTEXT(F17),M17,"") reads column F and if there is text
it
places a 4 digit password number from column M. These numbers are
generated
by a macro that places them in M17:M50 and can be changed or updated
by
re-running the macro. Column "M" is now hidden. Now I have the
platform
to
use for the security issues. In other worksheets it is necessary for
the
users to place their initials in the forms that I have built. Using
the
formula:
IF(ISERROR(VLOOKUP(D38,Password,3,0)),"",VLOOKUP(D 38,Password,3,0))
I should be able to enter the four number password associated with
the
persons name and it should place their initials in the appropriate
cell.
The
problem is, it does not work! If I manually enter a number in the
password
column it works, but with the random numbers generated by the macro
it
does
not work. I thought it might be the formula in column E that was
giving me
grief, so I changed the range in the macro to place the number
directly
into
column E, then use conditional formatting to hide the ones not in
use.
That
did not work either. When I mean it does not work, when the password
in
entered into the userform, which places it in the appropriate cell,
nothing
happens. Like it is a wrong password for the name. Does this have
something
to do with the range of random numbers generated by the macro? I
would
like
the functionality to update the passwords from time to time for
security
reasons; otherwise I would just manually enter some in and be done.
Any
ideas would be appreciated!!

Mike Rogers
PS: EVERYTHING I have put into the project I have learned from this
forum,
THANKS everyone!!!!