Change Option button value 1

[Queryman]

I have four option button on a form and when checked they have a value of "TRUE" and "FALSE" when they don't. I need the TRUE and FALSE value to convert to LOYAL and NOTLOYAL. The code I wrote is not working, any suggestions will be highly appreciated.

If OptPreLoyal.Value = True And OptPostLoyal.Value = True Then TxtLoyal.Value = "BLOCK"
If OptPreLoyal.Value = True And OptPostLoyal.Value = True Then TxtPreLoyal.Value = "LOYAL" And TxtPostLoyal.Value = "NOT LOYAL"
If OptPreNotLoyal.Value = True And OptPostNotLoyal.Value = True Then TxtLoyal.Value = "BLOCK"
If OptPreNotLoyal.Value = True And OptPostNotLoyal.Value = True Then TxtPreNotLoyal.Value = "NOT LOYAL" And TxtPostNotLoyal.Value = "NOT LOYAL"
If OptPreLoyal.Value = True And OptPostNotLoyal.Value = True Then TxtLoyal.Value = "DEFECTOR"
If OptPreLoyal.Value = True And OptPostNotLoyal.Value = True Then TxtPreLoyal.Value = "LOYAL" And TxtPostNotLoyal.Value = "NOT LOYAL"
If OptPreNotLoyal.Value = True And OptPostLoyal.Value = True Then TxtLoyal.Value = "CONVERSION"
If OptPreNotLoyal.Value = True And OptPostLoyal.Value = True Then TxtPreNotLoyal.Value = "NOT LOYAL" And TxtPostLoyal.Value = "NOT LOYAL"

The TxtLoyal value works fine, it's the TxtPreLoyal, TxtPostLoyal, TxtPreNotLoyal and TxtPostNotLoyal I am having problems with



Michael

 

[fumei]

1. Please describe exactly what the problem is.

2. You must have skipped some things in your post, as there are no End If statements. Perhaps post more and we can see what may be wrong.

3. I suspect that the logic could be done better.

If OptPreLoyal.Value = True And OptPostLoyal.Value = True Then TxtLoyal.Value = "BLOCK"
If OptPreLoyal.Value = True And OptPostLoyal.Value = True Then TxtPreLoyal.Value = "LOYAL" And TxtPostLoyal.Value = "NOT LOYAL"

should be:

If OptPreLoyal.Value = True And OptPostLoyal.Value = True Then
   TxtLoyal.Value = "BLOCK"
   TxtPreLoyal.Value = "LOYAL"
   TxtPostLoyal.Value = "NOT LOYAL"
End If

as the two If statements are testing the same conditions - if both Options are TRUE. Why repeat the test?

For the textboxes, you may want to use .Text rather than .Value.

4. All the logic appears to be checking for two conditions to be TRUE, but what if one is False?

Could you describe what you want to happen a bit better?

Gerry

 

[Queryman]

Thanks that worked!



Michael

 

[Zathras]

fumei, the End If statement is not needed when the code is all in one line:
[tt]
If xxx Then yyy
[/tt]
is the same as
[tt]
If xxx Then
yyy
End If
[/tt]
Queryman, if you are truly using option buttons, then as fumei has indicated, the logic is overly complex since, for example, you will never have both OptPreLoyal and OptPostLoyal true at the same time. (Unless they are in different groups, which you did not indicate.)

Are you really using Option Buttons, or are you using Check Boxes?

 

[Queryman]

Zathras,
Yes thay are in different groups, I do have a follow up question though:

now have the option button values captured properly, now I need a complicated IF statement to change a value in a cell based on these option button values.
There are three cells involved:
I2, J2 & K2
I2 contains the values "LOYAL" or "NOT LOYAL"
J2 contains the values "LOYAL" or "NOT LOYAL"

I need to have K2 display the following:
if I2 & J2 are LOYAL then K2 should display "BLOCK"
if I2 & J2 are LOYAL then K2 should display "DEFECTOR"
if I2 & J2 are LOYAL then K2 should display "LOYAL"
if I2 & J2 are LOYAL then K2 should display "CONVERSION"
if I2 has values & J2 is BLANK then K2 should display "" and maybe have conditional formatting to display the cell in RED
and vice versa if I2 is blank and J2 has values then K2 should display "" and maybe have conditional formatting to display the cell in RED




Michael

 

[Zathras]

You have indicated 4 different results for the same conditions.
[tt]
if I2 & J2 are LOYAL then
[/tt]
It would be best to start a new thread, perhaps in the Microsoft Office forum (forum68) since you are asking for a non-VBA solution.

But you could consider just putting the value you want in K2 at the same time you are putting values in I2 and J2.

 

[Queryman]

Thanks, I found the solution
=IF(AND(I2="LOYAL",J2="LOYAL"),"BLOCK","")&IF(AND(I2="LOYAL",J2="NOT LOYAL"),"DEFECTOR","")&IF(AND(I2="NOT LOYAL",J2="NOT LOYAL"),"BLOCK","")&IF(AND(I2="NOT LOYAL",J2="LOYAL"),"CONVERSION","")&IF(AND(I2="",J2="NOT LOYAL"),"PLEASE FILL IN","")&IF(AND(I2="LOYAL",J2=""),"PLEASE FILL IN","")&IF(AND(I2="",J2="LOYAL"),"PLEASE FILL IN","")&IF(AND(I2="NOT LOYAL",J2=""),"PLEASE FILL IN","")&IF(AND(I2="",J2=""),"","")



Michael

 

[Zathras]

Ok, as long as you're happy. But what if both I2 and J2 are blank? (The formula displays blank.)

This formula gives the same results as yours, with the excption of displaying "PLEASE FILL IN" when both I2 and J2 are blank:
[tt]
=IF(OR(I2="",J2=""),"PLEASE FILL IN",IF(I2=J2,"BLOCK",IF(I2="LOYAL","DEFECTOR","CONVERSION")))
[/tt]
A bit simpler, don't you think?

 

[Queryman]

Thanks, Zathras



Michael