Conditional Formatting in VBA (1 Viewer)

LewisWA02 · Apr 5, 2018

I have a form where a user inputs a value. The conditional formatting evaluates that value and sets the back color of the text box to the corresponding color. This works ok so long as the number entered is less than 100. Once it's over 100 it seems to evaluate only the first two digits of the number.

"valkpi" is the value the user enters
"txtinvert" is a selection that the user selects when establishing the criteria to judge the number against. It reverses the upper and lower bound of the value.
"txtlow" & "txthigh" are the lower and upper bounds respectively

I just don't understand what I have to change in order to get the system to evaluate the input properly. Any help is greatly appreciated.

I've also included a few pictures to show what I mean.

Code:

valkpi = Me.txtResult

If Me.txtinvert = 0 Then

        If valkpi < Me.txtLow Then
                 Me.txtResult.BackColor = vbRed
        
            ElseIf valkpi >= Me.txtLow And valkpi < Me.txtHigh Then
                 Me.txtResult.BackColor = vbYellow
        
            ElseIf valkpi >= Me.txtHigh Then
                 Me.txtResult.BackColor = vbGreen
                 
        End If
        
ElseIf Me.txtinvert = -1 Then

    If valkpi > Me.txtLow Then
            Me.txtResult.BackColor = vbRed
            
        ElseIf valkpi > Me.txtLow And Me.txtResult < Me.txtHigh Then
            Me.txtResult.BackColor = vbYellow
            
        ElseIf valkpi <= Me.txtHigh Then
            Me.txtResult.BackColor = vbGreen
            
    End If
    
End If

MarkK · Apr 5, 2018

This code is a fragment. Can you show all the code?
Mark

LewisWA02 · Apr 5, 2018

Code:

Private Sub txtResult_AfterUpdate()

valkpi = Me.txtResult

If Me.txtinvert = 0 Then
   If valkpi < Me.txtLow Then
      Me.txtResult.BackColor = vbRed
   ElseIf valkpi >= Me.txtLow And valkpi < Me.txtHigh Then
      Me.txtResult.BackColor = vbYellow
   ElseIf valkpi >= Me.txtHigh Then
      Me.txtResult.BackColor = vbGreen
   End If
ElseIf Me.txtinvert = -1 Then
    If valkpi > Me.txtLow Then
            Me.txtResult.BackColor = vbRed
        ElseIf valkpi >= Me.txtLow And Me.txtResult < Me.txtHigh Then
            Me.txtResult.BackColor = vbYellow
        ElseIf valkpi <= Me.txtHigh Then
            Me.txtResult.BackColor = vbGreen
     End If
End If

End Sub

MarkK · Apr 5, 2018

This looks wrong to me....

Code:

ElseIf Me.txtinvert = -1 Then
    If [COLOR="Blue"]valkpi > Me.txtLow[/COLOR] Then
            Me.txtResult.BackColor = vbRed

To me if you are going to invert the comparison here, don't you need to test for the selection being greater than txtHigh? This expression, greater than txtLow, will, of course, very commonly be true. Expect a lot of vbRed with this code, right?
Is that the problem?
Mark

isladogs · Apr 5, 2018

Agree with mark.
Also what would happen if<=txtlow when invert=-1

Orthodox Dave · Apr 6, 2018

I agree with Mark and Colin, but to address your original question (numbers over 100), I think your "numbers" are being evaluated as text strings. For example, Access will evaluate "1000000" as less than "2".

Since you haven't declared valkpi as a number, Access has made it a Variant and in this case, because it is getting its value from a text field (Me.txtLow) it is assuming it is text.

In your sub there are no variable declarations at all, which means you are not using Option Explicit to force you to declare variables. See this thread:
https://www.access-programmers.co.uk/forums/showthread.php?t=294452&highlight=option+explicit

Not declaring variables will get you into lots of trouble.

So the first part of your code should be:-

Code:

Private Sub txtResult_AfterUpdate()

Dim valkpi as integer

valkpi = val(Me.txtResult)

If Me.txtinvert = 0 Then

Your code also has some redundancy in the If statements, as in:

Code:

If valkpi < Me.txtLow Then
      Me.txtResult.BackColor = vbRed
   ElseIf valkpi >= Me.txtLow And valkpi < Me.txtHigh Then

You could simplify the last line to:

Code:

If valkpi < Me.txtLow Then
      Me.txtResult.BackColor = vbRed
   ElseIf valkpi < Me.txtHigh Then

(because if valkpi is not < Me.txtLow, then of course valkpi is >= Me.txtLow)

Your code also contains ElseIf statements but no Else statements. That's fine if you are happy the background colour can stay as it is if the conditions are not met, but I suspect you are trying to cover all possible conditions. In this case, the last statement before the End If statement should be Else, not ElseIf.

Hope some of that helps.

LewisWA02 · Apr 6, 2018

First, let me thank Orthodox Dave for pointing me to the obvious. I use the variable valkpi in a few different instances of code so I had actually declared it as a global variable set to string....so in effect I created my own problem. Changing it lets the code now evaluate all instances as numbers and all is right with the world. Thanks for helping me see the forest for the trees. I changed it now everything is working properly.

As for the other questions, let me try to explain. The The invert selection reverses the values of the lower bound and the upper bound in order to account for situations where you would consider a lower number to be good versus a higher number. Take for example the number of times employees are injured over the course of a quarter. You want that number to be smaller than your lower bound. Therefore it has to be evaluated in reverse of the situation. Rather than use a different form for each possible situation, I just had VBA switch the values through code.

With regards to :

Code:

valkpi >= Me.txtLow And valkpi < Me.txtHigh

This is checking to see if the variable is between the two bounds. Dave, your statement "valkpi >= Me.txtLow And valkpi < Me.txtHigh" is correct, to a degree. If you are just wanting to see if it is higher than a value or lower than a value then that works fine. However, wanting to see that it is between the values requires the code to evaluate it as such.

Again, thank you for all the replies. You guys really made me think on this one.

LewisWA02 · Apr 6, 2018

Ridders,

To answer your question: "what would happen if<=txtlow when invert=-1", it would highlight the text box green as in its good because you did less than what could be a problem, versus the other scenario where it would be red to indicate you didn't do enough of what ever the KPI is.

isladogs · Apr 6, 2018

LewisWA02 said:
Ridders,

To answer your question: "what would happen if<=txtlow when invert=-1", it would highlight the text box green as in its good because you did less than what could be a problem, versus the other scenario where it would be red to indicate you didn't do enough of what ever the KPI is.

No it wouldn't ... unless you have set the default colour as green.
As you don't have an Else statement to cover 'all other values', it wouldn't be covered by the CF at all

Have you done full tests on all possible outcomes with this?

LewisWA02 · Apr 6, 2018

I don't know what to tell you then because it's doing just what I said it would. It was simply the declaration I had that was wrong. Thanks for your posts.

isladogs · Apr 7, 2018

Glad you got it working. My comments were based on the original code before you had made any changes.

Conditional Formatting in VBA (1 Viewer)

LewisWA02

Registered User.

Attachments

MarkK

bit cruncher

LewisWA02

Registered User.

MarkK

bit cruncher

isladogs

MVP / VIP

Orthodox Dave

Home Developer

LewisWA02

Registered User.

LewisWA02

Registered User.

isladogs

MVP / VIP

LewisWA02

Registered User.

isladogs

MVP / VIP

Similar threads

Users who are viewing this thread