PDA

View Full Version : Solved: Returning a Value from and "If Then Else"



tccmdr
06-18-2007, 06:28 PM
Can anyone explain to me why this will not work:banghead:


Private Sub TextBox46_Change()
If Me.C1.Value >= Me.To1.Value Then
If Me.C2.Value >= Me.To2.Value Then
If Me.C3.Value >= Me.To3Value Then
If Me.C4.Value >= Me.To4.Value Then
If Me.C5.Value >= Me.To5.Value Then
If Me.C6.Value >= Me.To6.Value Then
If Me.C7.Value >= Me.To7.Value Then
If Me.C8.Value >= Me.To8.Value Then
If Me.C9.Value >= Me.To9.Value Then
Me.TextBox46.Value = "Compliant"

Else: Me.TextBox46.Value = "Non Compliant"

End If
End Sub


The TextBox is remaining blank:dunno

Any input would be appreciated: pray2:

gnod
06-18-2007, 06:55 PM
you need an If and End If combination.. in your code you only have 1 end if..

tccmdr
06-18-2007, 06:58 PM
This doesn't work either:doh:


Private Sub TextBox46_Change()
If Me.C1.Value >= Me.To1.Value Then
If Me.C2.Value >= Me.To2.Value Then
If Me.C3.Value >= Me.To3.Value Then
If Me.C4.Value >= Me.To4.Value Then
If Me.C5.Value >= Me.To5.Value Then
If Me.C6.Value >= Me.To6.Value Then
If Me.C7.Value >= Me.To7.Value Then
If Me.C8.Value >= Me.To8.Value Then
If Me.C9.Value >= Me.To9.Value Then
Me.TextBox46.Value = "Compliant"

Else: Me.TextBox46.Value = "Non Compliant"

End If
End If
End If
End If
End If
End If
End If
End If
End If
End Sub

rbrhodes
06-18-2007, 07:36 PM
Hi tccmdr,

You're on the right track... However, only your final If will ever return "Non Compliant".

Rather than put an Else in every If I'd just put one at the end to cover them all. This would require an 'Exit Sub' line when "compliant" was the result. See below.

The other thing is that the Sub is recursive. The event that fires the sub is Textbox46 change. Textbox 46 changes, the sub runs.

When the sub runs it can change TextBox46 to either "Compliant" or "Non Compliant" therfore firing itself again. This will not harm anything as when it gets thru all of the If's again it doesn't change TextBox46 so it finishes the second run and continues from where it left off to complete the first.

Waste of cycles tho. I'd set a public boolean and check it on entry to the sub. When it fires the 2nd time it checks the boolean and exits itself, returning immediately to the first instance.

One more: I'd use a "with" statement to avoid repeated calls to "Me".

Cheers,

dr

With recursion




Option Explicit

Private Sub TextBox46_Change()
If Me.C1.Value >= Me.To1.Value Then
If Me.C2.Value >= Me.To2.Value Then
If Me.C3.Value >= Me.To3.Value Then
If Me.C4.Value >= Me.To4.Value Then
If Me.C5.Value >= Me.To5.Value Then
If Me.C6.Value >= Me.To6.Value Then
If Me.C7.Value >= Me.To7.Value Then
If Me.C8.Value >= Me.To8.Value Then
If Me.C9.Value >= Me.To9.Value Then
'put value and exit here
Me.TextBox46.Value = "Compliant"
Exit Sub
End If
End If
End If
End If
End If
End If
End If
End If
End If
'didn't hit 'exit sub' line so must be Non Compliant
Me.TextBox46.Value = "Non Compliant"
End Sub



Without recursion


Option Explicit
Public T46 As Boolean
Private Sub TextBox46_Change()

If T46 = True Then
T46 = False
Exit Sub
End If

With Me
If .C1.Value >= .To1.Value Then
If .C2.Value >= .To2.Value Then
If .C3.Value >= .To3.Value Then
If .C4.Value >= .To4.Value Then
If .C5.Value >= .To5.Value Then
If .C6.Value >= .To6.Value Then
If .C7.Value >= .To7.Value Then
If .C8.Value >= .To8.Value Then
If .C9.Value >= .To9.Value Then
T46 = True
.TextBox46.Value = "Compliant"
Exit Sub
End If
End If
End If
End If
End If
End If
End If
End If
End If
'didn't hit 'exit sub' line so must be Non Compliant
.TextBox46.Value = "Non Compliant"
End With
End Sub

rbrhodes
06-18-2007, 07:36 PM
Of course you could always just put an Else after every if...

Cheers,

dr

tccmdr
06-18-2007, 07:58 PM
Thanks DR....sort of works :think:


I have to keep trying to type something in the textbox for the "Change" event to occur; and
The code only seems to recognise the first numeral in the percentage e.g 20% is recognised as greater than 100%:doh:
:dunno

rbrhodes
06-18-2007, 08:17 PM
tccmdr,

The Change event fires as soon as you do anything! Perhaps you need to add a commandbutton and move the code from the Textbox_Change to the Button_Click.

Then you type into the textbox and when you're done, click the button and the code runs. That would also kill the recursive action.

Cheers,

dr

tccmdr
06-18-2007, 08:43 PM
DR...

Attached the code to the Label_Click() event......works fine, however,



The code only seems to recognise the first numeral in the percentage e.g 20% is recognised as greater than 100%:doh:



:dunno

tccmdr
06-19-2007, 04:22 PM
Still cannot work out % issue.

Its as if the zeros are not recognised:think:

geekgirlau
06-19-2007, 05:30 PM
No, it's that Excel is treating the values as text; if you were sorting them alpha-numerically, 2 comes after 1.

There's a couple of different ways to handle this:

You could strip the % sign out of the values when comparing them, and convert the text box value to numeric before running the comparison;
When the user enters data, you could strip the % sign out at the time, so if they type "20%" you change it to "20". You could have a label with a % sign immediately after the text box to indicate that the number is a percentage. You would still need to convert the value to a numeric before running your comparison.Your comparison needs to become something like

If CSng(Replace(Me.C1.Value,"%","")) >= CSng(Replace(Me.To1.Value,"%","")) Then

tccmdr
06-19-2007, 05:48 PM
:clap: Eureka!!:clap:

Thanks GG........

A quick question........does excel identify numerals as values until a symbol is used e.g a $ sign or % sign:think:

I'm assuming yes, given the result above:whistle:

geekgirlau
06-19-2007, 07:56 PM
Actually I think you'll find that even without the % sign, Excel assumes everything in a text box is text.

tccmdr
06-19-2007, 08:40 PM
Ahh...

So removing the % and using CSng identifies the text as a value
e.g "100%" = 100

Thanks GG:hi: