Hello all,

here is my 1st issue:

I've created a Name Range and use it in a validation data List of a
cell (consider it as A2). In A2 cell I've also placed an IF statement
like this one IF(A1="BLABLA";"SPEAKING";""), now my problem is that
when A1 < BLABLA then a message appears "The value you entered is not
valid. A user has restricted values that can be entered into this
cell." for A2 cell. The validation I created for this cell has been
marked to ignore blanks but as it seems it doesn't work right.

What I have to do for this?

2nd one:

How to encrypt or restrict by viewing my vba code I wrote when I want
to distribute my file?

Thanks in advance

Hi

issue 1: maybe have your validation be a custom validation with formula

=Or(A2 = "SPEAKING","")
The problem is that an empty string is not a blank.

issue 2: in the VBA IDE go toTools - VBA Project Properties -
Protection and password-protect your project. Evidently, it is not the
strongest encryption in the world, but is adequate for most purposes.

Hope that helps

-John Coleman

x-rays wrote:
Hello all,

here is my 1st issue:

I've created a Name Range and use it in a validation data List of a
cell (consider it as A2). In A2 cell I've also placed an IF statement
like this one IF(A1="BLABLA";"SPEAKING";""), now my problem is that
when A1 < BLABLA then a message appears "The value you entered is not
valid. A user has restricted values that can be entered into this
cell." for A2 cell. The validation I created for this cell has been
marked to ignore blanks but as it seems it doesn't work right.

What I have to do for this?

2nd one:

How to encrypt or restrict by viewing my vba code I wrote when I want
to distribute my file?

Thanks in advance

Slight correction:

I meant =Or(A2 = "SPEAKING", A2 = "")

Sorry for any confusion

John Coleman wrote:
Hi

issue 1: maybe have your validation be a custom validation with formula

=Or(A2 = "SPEAKING","")
The problem is that an empty string is not a blank.

issue 2: in the VBA IDE go toTools - VBA Project Properties -
Protection and password-protect your project. Evidently, it is not the
strongest encryption in the world, but is adequate for most purposes.

Hope that helps

-John Coleman

x-rays wrote:
Hello all,

here is my 1st issue:

I've created a Name Range and use it in a validation data List of a
cell (consider it as A2). In A2 cell I've also placed an IF statement
like this one IF(A1="BLABLA";"SPEAKING";""), now my problem is that
when A1 < BLABLA then a message appears "The value you entered is not
valid. A user has restricted values that can be entered into this
cell." for A2 cell. The validation I created for this cell has been
marked to ignore blanks but as it seems it doesn't work right.

What I have to do for this?

2nd one:

How to encrypt or restrict by viewing my vba code I wrote when I want
to distribute my file?

Thanks in advance

Hi John and thank for your reply,

The solution for the 1st issue doesn't work, perhaps I didn't explain
my problem correct, let me try again:

I got 2 cells which both have validation, In 1st cell I choose values
from a List (Name Range), In 2nd cell I choose values from a list bur,
I use the INDIRECT(A1) function as a source to fill my list depending
on what I chose in 1st cell. To do this I created 3 Name Ranges: Basic
List, Specific1, Specific2

Basic List: Specific, Specific1, Specific2
Specific1: AAA, BBB
Specific2: CCC, DDD

Now with the INDIRECT in 2nd cell what I get, when choose Specific1
from the 1st the 2nd fills with AAA and BBB if I choose Specific2 for
the 1st the 2nd fills with CCC and DDD values.

But In 2nd cell I wrote an IF statement to accomplish a suggested value
for it depending the value in 1st cell: =IF(A1="Specific1";"AAA";"").
Don't if I can do it with a better way this one, please enlighten me
if...

So If the 1st cell has the value of Specific1 then there is no problem,
but when I choose something else I "block my self" through my
validation although I marked to ignore blanks. As you said this "" is
not equal to blank, what can I do...?

Did I miss something on your suggestion?

Thank you very much for your response.

John Coleman wrote:
Slight correction:

I meant =Or(A2 = "SPEAKING", A2 = "")

Sorry for any confusion

John Coleman wrote:
Hi

issue 1: maybe have your validation be a custom validation with formula

=Or(A2 = "SPEAKING","")
The problem is that an empty string is not a blank.

issue 2: in the VBA IDE go toTools - VBA Project Properties -
Protection and password-protect your project. Evidently, it is not the
strongest encryption in the world, but is adequate for most purposes.

Hope that helps

-John Coleman

x-rays wrote:
Hello all,

here is my 1st issue:

I've created a Name Range and use it in a validation data List of a
cell (consider it as A2). In A2 cell I've also placed an IF statement
like this one IF(A1="BLABLA";"SPEAKING";""), now my problem is that
when A1 < BLABLA then a message appears "The value you entered is not
valid. A user has restricted values that can be entered into this
cell." for A2 cell. The validation I created for this cell has been
marked to ignore blanks but as it seems it doesn't work right.

What I have to do for this?

2nd one:

How to encrypt or restrict by viewing my vba code I wrote when I want
to distribute my file?

Thanks in advance

Orthography (Corrections)

I choose values from a list bur - I choose values from a list but
Don't if I can do it with a better way this one - Don't know if I can
do it with a better way this one


x-rays wrote:
Hi John and thank for your reply,

The solution for the 1st issue doesn't work, perhaps I didn't explain
my problem correct, let me try again:

I got 2 cells which both have validation, In 1st cell I choose values
from a List (Name Range), In 2nd cell I choose values from a list bur,
I use the INDIRECT(A1) function as a source to fill my list depending
on what I chose in 1st cell. To do this I created 3 Name Ranges: Basic
List, Specific1, Specific2

Basic List: Specific, Specific1, Specific2
Specific1: AAA, BBB
Specific2: CCC, DDD

Now with the INDIRECT in 2nd cell what I get, when choose Specific1
from the 1st the 2nd fills with AAA and BBB if I choose Specific2 for
the 1st the 2nd fills with CCC and DDD values.

But In 2nd cell I wrote an IF statement to accomplish a suggested value
for it depending the value in 1st cell: =IF(A1="Specific1";"AAA";"").
Don't if I can do it with a better way this one, please enlighten me
if...

So If the 1st cell has the value of Specific1 then there is no problem,
but when I choose something else I "block my self" through my
validation although I marked to ignore blanks. As you said this "" is
not equal to blank, what can I do...?

Did I miss something on your suggestion?

Thank you very much for your response.

John Coleman wrote:
Slight correction:

I meant =Or(A2 = "SPEAKING", A2 = "")

Sorry for any confusion

John Coleman wrote:
Hi

issue 1: maybe have your validation be a custom validation with formula

=Or(A2 = "SPEAKING","")
The problem is that an empty string is not a blank.

issue 2: in the VBA IDE go toTools - VBA Project Properties -
Protection and password-protect your project. Evidently, it is not the
strongest encryption in the world, but is adequate for most purposes.

Hope that helps

-John Coleman

x-rays wrote:
Hello all,

here is my 1st issue:

I've created a Name Range and use it in a validation data List of a
cell (consider it as A2). In A2 cell I've also placed an IF statement
like this one IF(A1="BLABLA";"SPEAKING";""), now my problem is that
when A1 < BLABLA then a message appears "The value you entered is not
valid. A user has restricted values that can be entered into this
cell." for A2 cell. The validation I created for this cell has been
marked to ignore blanks but as it seems it doesn't work right.

What I have to do for this?

2nd one:

How to encrypt or restrict by viewing my vba code I wrote when I want
to distribute my file?

Thanks in advance

Hi,

Maybe the validation formula could involve lookup functions - keep the
Or with A2 = "" still one of the clauses, but the other clause could
use a combination of lookup fuctions and information functions:
something like

=Or(Not(IsErr(Match,A2,Indirect(A1),0)),A2 = "")

for the validation formula.

You could also consider *forcing* the user to select from the list by
locking the cell and letting an event-handler attached to the list
populate the cell.

Just a couple of ideas

-John Coleman

x-rays wrote:
Hi John and thank for your reply,

The solution for the 1st issue doesn't work, perhaps I didn't explain
my problem correct, let me try again:

I got 2 cells which both have validation, In 1st cell I choose values
from a List (Name Range), In 2nd cell I choose values from a list bur,
I use the INDIRECT(A1) function as a source to fill my list depending
on what I chose in 1st cell. To do this I created 3 Name Ranges: Basic
List, Specific1, Specific2

Basic List: Specific, Specific1, Specific2
Specific1: AAA, BBB
Specific2: CCC, DDD

Now with the INDIRECT in 2nd cell what I get, when choose Specific1
from the 1st the 2nd fills with AAA and BBB if I choose Specific2 for
the 1st the 2nd fills with CCC and DDD values.

But In 2nd cell I wrote an IF statement to accomplish a suggested value
for it depending the value in 1st cell: =IF(A1="Specific1";"AAA";"").
Don't if I can do it with a better way this one, please enlighten me
if...

So If the 1st cell has the value of Specific1 then there is no problem,
but when I choose something else I "block my self" through my
validation although I marked to ignore blanks. As you said this "" is
not equal to blank, what can I do...?

Did I miss something on your suggestion?

Thank you very much for your response.

John Coleman wrote:
Slight correction:

I meant =Or(A2 = "SPEAKING", A2 = "")

Sorry for any confusion

John Coleman wrote:
Hi

issue 1: maybe have your validation be a custom validation with formula

=Or(A2 = "SPEAKING","")
The problem is that an empty string is not a blank.

issue 2: in the VBA IDE go toTools - VBA Project Properties -
Protection and password-protect your project. Evidently, it is not the
strongest encryption in the world, but is adequate for most purposes.

Hope that helps

-John Coleman

x-rays wrote:
Hello all,

here is my 1st issue:

I've created a Name Range and use it in a validation data List of a
cell (consider it as A2). In A2 cell I've also placed an IF statement
like this one IF(A1="BLABLA";"SPEAKING";""), now my problem is that
when A1 < BLABLA then a message appears "The value you entered is not
valid. A user has restricted values that can be entered into this
cell." for A2 cell. The validation I created for this cell has been
marked to ignore blanks but as it seems it doesn't work right.

What I have to do for this?

2nd one:

How to encrypt or restrict by viewing my vba code I wrote when I want
to distribute my file?

Thanks in advance

Another typo:

=Or(Not(IsErr(Match(A2,Indirect(A1),0)),A2 = "")

John Coleman wrote:
Hi,

Maybe the validation formula could involve lookup functions - keep the
Or with A2 = "" still one of the clauses, but the other clause could
use a combination of lookup fuctions and information functions:
something like

=Or(Not(IsErr(Match,A2,Indirect(A1),0)),A2 = "")

for the validation formula.

You could also consider *forcing* the user to select from the list by
locking the cell and letting an event-handler attached to the list
populate the cell.

Just a couple of ideas

-John Coleman

x-rays wrote:
Hi John and thank for your reply,

The solution for the 1st issue doesn't work, perhaps I didn't explain
my problem correct, let me try again:

I got 2 cells which both have validation, In 1st cell I choose values
from a List (Name Range), In 2nd cell I choose values from a list bur,
I use the INDIRECT(A1) function as a source to fill my list depending
on what I chose in 1st cell. To do this I created 3 Name Ranges: Basic
List, Specific1, Specific2

Basic List: Specific, Specific1, Specific2
Specific1: AAA, BBB
Specific2: CCC, DDD

Now with the INDIRECT in 2nd cell what I get, when choose Specific1
from the 1st the 2nd fills with AAA and BBB if I choose Specific2 for
the 1st the 2nd fills with CCC and DDD values.

But In 2nd cell I wrote an IF statement to accomplish a suggested value
for it depending the value in 1st cell: =IF(A1="Specific1";"AAA";"").
Don't if I can do it with a better way this one, please enlighten me
if...

So If the 1st cell has the value of Specific1 then there is no problem,
but when I choose something else I "block my self" through my
validation although I marked to ignore blanks. As you said this "" is
not equal to blank, what can I do...?

Did I miss something on your suggestion?

Thank you very much for your response.

John Coleman wrote:
Slight correction:

I meant =Or(A2 = "SPEAKING", A2 = "")

Sorry for any confusion

John Coleman wrote:
Hi

issue 1: maybe have your validation be a custom validation with formula

=Or(A2 = "SPEAKING","")
The problem is that an empty string is not a blank.

issue 2: in the VBA IDE go toTools - VBA Project Properties -
Protection and password-protect your project. Evidently, it is not the
strongest encryption in the world, but is adequate for most purposes.

Hope that helps

-John Coleman

x-rays wrote:
Hello all,

here is my 1st issue:

I've created a Name Range and use it in a validation data List of a
cell (consider it as A2). In A2 cell I've also placed an IF statement
like this one IF(A1="BLABLA";"SPEAKING";""), now my problem is that
when A1 < BLABLA then a message appears "The value you entered is not
valid. A user has restricted values that can be entered into this
cell." for A2 cell. The validation I created for this cell has been
marked to ignore blanks but as it seems it doesn't work right.

What I have to do for this?

2nd one:

How to encrypt or restrict by viewing my vba code I wrote when I want
to distribute my file?

Thanks in advance