Issue on if function > "45"

[jptaz]

Hello,

I have this code to enable and change the back color to white of a combo box if the value any of 3 other textboxes equals or is higher to 45. Otherwise, the combo box stays disable and gray.

The problem is that if I enter the following numbers (5, 6, 7, 8, 9), the combox box is being enabled. Any other numbers below 45, it stays disabled...

Do you have any idea why? How do I fix this?

Private Sub Condition_1()
    If Controls.Item("Reg549").Value >= "45" Or Controls.Item("Reg550").Value >= "45" Or Controls.Item("Reg551").Value >= "45" Then
        Controls.Item("Reg552").Enabled = True
        Controls.Item("Reg552").BackColor = "&H80000005"
    Else
        Controls.Item("Reg552").Enabled = False
        Controls.Item("Reg552").BackColor = "&H80000004"
    End If
End Sub

Private Sub Reg549_AfterUpdate()
    Call Condition_1
End Sub
Private Sub Reg550_AfterUpdate()
    Call Condition_1
End Sub
Private Sub Reg551_AfterUpdate()
    Call Condition_1
End Sub

thank you

JP

 

[Oaktree]

Remove the quotes. "45" is a text value. 45 is a number.

 

[jptaz]

Remove the quotes. "45" is a text value. 45 is a number.

Thanks, I've tried that, but now once I enter any number (< or > 45) or letter it enables my combobox and It won't disable if the value is erased...

I understand the idea however but can't think of why...

jp

 

[Joe4]

Did you remember to to change the "45" to 45 for ALL three references to 45?

If you try this code and you enter a value, let us know exactly what each message box returns:

Private Sub Condition_1()
    If Controls.Item("Reg549").Value >= 45 Or Controls.Item("Reg550").Value >= 45 Or Controls.Item("Reg551").Value >= 45 Then
        MsgBox "Condition_1 Met"
        Controls.Item("Reg552").Enabled = True
        Controls.Item("Reg552").BackColor = "&H80000005"
    Else
        MsgBox "Condition_1 Not Met"
        Controls.Item("Reg552").Enabled = False
        Controls.Item("Reg552").BackColor = "&H80000004"
    End If
End Sub

Private Sub Reg549_AfterUpdate()
    MsgBox Controls.Item("Reg549").Value & ", Is numeric=" & IsNumeric(Controls.Item("Reg549").Value)
    Call Condition_1
End Sub

Private Sub Reg550_AfterUpdate()
    MsgBox Controls.Item("Reg550").Value & ", Is numeric=" & IsNumeric(Controls.Item("Reg550").Value)
    Call Condition_1
End Sub

Private Sub Reg551_AfterUpdate()
    MsgBox Controls.Item("Reg551").Value & ", Is numeric=" & IsNumeric(Controls.Item("Reg551").Value)
    Call Condition_1
End Sub

 

[jptaz]

Did you remember to to change the "45" to 45 for ALL three references to 45?

If you try this code and you enter a value, let us know exactly what each message box returns:

Private Sub Condition_1()
    If Controls.Item("Reg549").Value >= 45 Or Controls.Item("Reg550").Value >= 45 Or Controls.Item("Reg551").Value >= 45 Then
        MsgBox "Condition_1 Met"
        Controls.Item("Reg552").Enabled = True
        Controls.Item("Reg552").BackColor = "&H80000005"
    Else
        MsgBox "Condition_1 Not Met"
        Controls.Item("Reg552").Enabled = False
        Controls.Item("Reg552").BackColor = "&H80000004"
    End If
End Sub

Private Sub Reg549_AfterUpdate()
    MsgBox Controls.Item("Reg549").Value & ", Is numeric=" & IsNumeric(Controls.Item("Reg549").Value)
    Call Condition_1
End Sub

Private Sub Reg550_AfterUpdate()
    MsgBox Controls.Item("Reg550").Value & ", Is numeric=" & IsNumeric(Controls.Item("Reg550").Value)
    Call Condition_1
End Sub

Private Sub Reg551_AfterUpdate()
    MsgBox Controls.Item("Reg551").Value & ", Is numeric=" & IsNumeric(Controls.Item("Reg551").Value)
    Call Condition_1
End Sub

Hello Joe4,

Yes I removed quotes from every "45"

When I try your code and enter a number in each textboxes I get for the first 2 textboxes "is numeric = true" then "Condition_1 Met". For the third textbox (any order) I get "is numeric = true" then "Condition_1 Not Met".
However, if the value of any textbox is over 45, no mather the other numbers I don't get the "condition_1 not met" msg...

If I enter a letter I get "is numeric = false" then "Condition_1 Met". I don't have "Condition_1 Not Met"

Thanks for your time.

JP

 

[Joe4]

When I try your code and enter a number in each textboxes I get for the first 2 textboxes "is numeric = true" then "Condition_1 Met". For the third textbox (any order) I get "is numeric = true" then "Condition_1 Not Met".
However, if the value of any textbox is over 45, no mather the other numbers I don't get the "condition_1 not met" msg...

Those Messages Boxes should be returning a number before the "Is Numeric..." part. If they do not, then that is your problem. It is not pulling any value.
It may recognize not your controls, the way you are tryring to refer to them.
Are you putting Controls directly on the Excel file, or are you using a User Form?

 

[NoSparks]

try this

Private Sub Condition_1()
    
    x = VBA.IIf(Controls.Item("Reg549") = "", 0, Controls.Item("Reg549"))
    y = VBA.IIf(Controls.Item("Reg550") = "", 0, Controls.Item("Reg550"))
    z = VBA.IIf(Controls.Item("Reg551") = "", 0, Controls.Item("Reg551"))
    
    If x >= 45 Or y >= 45 Or z >= 45 Then
        Controls.Item("Reg552").Enabled = True
        Controls.Item("Reg552").BackColor = "&H80000005"
    Else
        Controls.Item("Reg552").Enabled = False
        Controls.Item("Reg552").BackColor = "&H80000004"
    End If
End Sub

Private Sub Reg549_AfterUpdate()
    Call Condition_1
End Sub

Private Sub Reg550_AfterUpdate()
    Call Condition_1
End Sub

Private Sub Reg551_AfterUpdate()
    Call Condition_1
End Sub

 

[NoSparks]

I don't know how VBA deals with letter characters typed into the textboxes but apparently considers them more than 45.
You can use the _BeforeUpdate of a text box to check that it is numeric.
Cancel = True will prevent moving out of the textbox.

 

[jptaz]

try this

Private Sub Condition_1()
   
    x = VBA.IIf(Controls.Item("Reg549") = "", 0, Controls.Item("Reg549"))
    y = VBA.IIf(Controls.Item("Reg550") = "", 0, Controls.Item("Reg550"))
    z = VBA.IIf(Controls.Item("Reg551") = "", 0, Controls.Item("Reg551"))
   
    If x >= 45 Or y >= 45 Or z >= 45 Then
        Controls.Item("Reg552").Enabled = True
        Controls.Item("Reg552").BackColor = "&H80000005"
    Else
        Controls.Item("Reg552").Enabled = False
        Controls.Item("Reg552").BackColor = "&H80000004"
    End If
End Sub

Private Sub Reg549_AfterUpdate()
    Call Condition_1
End Sub

Private Sub Reg550_AfterUpdate()
    Call Condition_1
End Sub

Private Sub Reg551_AfterUpdate()
    Call Condition_1
End Sub

Hey thank you very much!! Your solution works perfectly. Do you mind explaining what you did? As for the letters I might just force numbers in those textboxes.

Thanks again

JP

 

[jptaz]

Those Messages Boxes should be returning a number before the "Is Numeric..." part. If they do not, then that is your problem. It is not pulling any value.
It may recognize your controls, the way you are tryring to refer to them.
Are you putting Controls directly on the Excel file, or are you using a User Form?

Thanks again for your time. Nosparks solution worked. They were in a userform. Don't know what the problem was..

JP