Probably an easy answer but i am having trouble with it. (1 Viewer)

Steve2020 · Aug 15, 2017

I have a field on a form called FemaleBodyFatPercentage that receives the percentages, based on the percentages i want to highlight the corresponding label from a transparent border to a 2 point border to highlight the label and make sure the other labels stay transparent. I have a feeling i am doing this totally wrong and I have tried multiple ways of doing this but I have no problem admitting that I am just stumped.

If FemaleBodyFatPercentage.Value > 9.99 Then Me!FemaleDangerouslylowLabel.BorderStyle = 0

If FemaleBodyFatPercentage.Value < 10 Then Me!FemaleDangerouslylowLabel.BorderStyle = 1

If FemaleBodyFatPercentage.Value < 10 > "13.99" Then Me!FemaleEssentialFatLabel.BorderStyle = 0

If FemaleBodyFatPercentage.Value > 10 < 14 Then Me!FemaleEssentialFatLabel.BorderStyle = 1

If FemaleBodyFatPercentage.Value < 14 > 20.99 Then Me!FemaleAthletesLabel.BorderStyle = 0

If FemaleBodyFatPercentage.Value > 14 < "21" Then Me!FemaleAthletesLabel.BorderStyle = 1

If FemaleBodyFatPercentage.Value < 21 > 24.99 Then Me!FemaleFitnessLabel.BorderStyle = 0

If FemaleBodyFatPercentage.Value > 21 < 25 Then Me!FemaleFitnessLabel.BorderStyle = 1

If FemaleBodyFatPercentage.Value < 25 > 31.99 Then Me!FemaleAcceptableLabel.BorderStyle = 0

If FemaleBodyFatPercentage.Value > 25 < 32 Then Me!FemaleAcceptableLabel.BorderStyle = 1

If FemaleBodyFatPercentage.Value < 32 Then Me!FemaleObeseLabel.BorderStyle = 0

If FemaleBodyFatPercentage.Value > 31.99 Then Me!FemaleObeseLabel.BorderStyle = 1

nhorton79 · Aug 15, 2017

Bit of a newbie as well. Where are you putting this code and initially noticed that none of your IF statements have END IF closing statements.

Sent from my iPhone using Tapatalk

nhorton79 · Aug 15, 2017

Also, maybe a SELECT...CASE statement might work here?

Sent from my iPhone using Tapatalk

Steve2020 · Aug 15, 2017

I am putting the code on the after update on a text box. It works sort of it will change the border on them as you change the percentages but if a border was already made visible it will stay visible even when the percentages change from that labels range.

I hope i explained that correctly.

isladogs · Aug 15, 2017

OK - several things here:

1.After Update event should be fine

nhorton79 said:
...noticed that none of your IF statements have END IF closing statements.

2. Not needed as each statement is on one line

nhorton79 said:
Also, maybe a SELECT...CASE statement might work here?

3. Yes - MUCH better

4. Use Me. before EACH FemaleBodyFatPercentage and FemaleDangerouslylowLabel
.Value NOT needed as it's the default

e.g.

Code:

[CODE]If [COLOR="Red"]Me.[/COLOR]FemaleBodyFatPercentage> 9.99 Then [COLOR="red"]Me.[/COLOR]FemaleDangerouslylowLabel.BorderStyle = 0

5. However, the main problem is that your ranges overlap and are incorrectly written:
e.g. if FemaleBodyFatPercentage = 12 then it is
a) >9.99 (statement 1)
b) >10 (statement 3)

Conditions like this are wrong: > 10 < 14
It should be '>10 And <14' or possibly 'Between 10 And 14'

These are also wrong > "13.99", <"21" as numbers shouldn't have quote marks

=====================================

Go through each statement & fix your conditions
Preferably change to Select Case

Have 'fun'

ashleedawg · Aug 16, 2017

@Steve2020 -
If..Then..Else can be on one line (without an End If) if you're only giving it one statement to execute, like shown on MSDN.

Code:

If 1 = 2 Then Msgbox "Uh-Oh" Else Msgbox "Universe Intact"

Select..Case is a good idea as long as you only need to meet one set of criteria, so that wouldn't work in this case -- unless the overlapping criteria are a mistake, as @ridders pointed out. Also, some forget that Select..Case can be used with 'sets' of criteria, like the examples on MSDN.

Code:

Sub testCase()
    Debug.Print CheckItOut("hello")
    Debug.Print CheckItOut(7)
    Debug.Print CheckItOut(#11/5/1963#)
End Sub

Function CheckItOut(myThing As Variant)
    Select Case myThing
        Case "aaa" To "zzz"
            CheckItOut = "Probably a Word"
        Case 1, 2, 3, 4, 5, 6, 7, 8, 9
            CheckItOut = "Whole number 1-9"
        Case Else
            CheckItOut = "something else"
    End Select
End Function

Cronk · Aug 16, 2017

What happens if FemaleBodyFatPercentage = 9.999? Such a value is greater than 9.99 and less than 10

You could simply the code using IIf statements
eg

Code:

Me!FemaleDangerouslylowLabel.BorderStyle = iif(FemaleBodyFatPercentage < 10 ,1,0)

Steve2020 · Aug 16, 2017

Thank you for all the answers i am going to try to rework it in the morning--thank you all very much for the ideas and fixes.

Probably an easy answer but i am having trouble with it. (1 Viewer)

Steve2020

New member

nhorton79

Registered User.

nhorton79

Registered User.

Steve2020

New member

isladogs

MVP / VIP

ashleedawg

"Here for a good time"

Cronk

Registered User.

Steve2020

New member

Similar threads

Users who are viewing this thread