Userform textbox background color and percent

[jh51745]

I am trying to get the text box to change background color when the percent is < 90.0% and > 110.0% and to change back when the value is in between. However it only does red in the current version below. When I change the "90.0" to "0.9" and so forth it will only work for number entered 0.9-1.1. I have tried many things but know I must be missing something.

Private Sub Grade_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Grade.value = Format(Grade.value / 100, "0.0%")

 Grade.value = Grade
   
 If Grade < "90.0" Then
       Grade.BackColor = &HFF&
       
    ElseIf Grade > "110.0" Then
        Grade.BackColor = &HFF&
     
    ElseIf Grade >= "90.0" And Grade <= "110.0" Then
         Grade.BackColor = CoreNumber.BackColor
 
    End If
End Sub

[Kyle123]

Try this

Private Sub Grade_Exit(ByVal Cancel As MSForms.ReturnBoolean)

Dim GrdNo as long
GrdNo = val(Grade.Value)

Grade.value = Format(Grade.value / 100, "0.0%")

   
 If GrdNo < 90 Then
       Grade.BackColor = &HFF&
       
    ElseIf GrdNo > 110 Then
        Grade.BackColor = &HFF&
     
    ElseIf GrdNo >= 90 And GrdNo <= 110 Then
         Grade.BackColor = CoreNumber.BackColor
 
    End If
End Sub

[jh51745]

It works with the correct range except it doesn't keep the format correctly it changes 100.0 to just 100 without it being 100.0%

[Kyle123]

oops, typo, try that

[jh51745]

That works except for numbers that round to 90 such as 89.9 or 110. That should be turned red but is not. It needs to be 90.0 and 110.0 but doesn't work.

[romperstomper]

You could use:

GrdNo = Application.Round(val(Grade.Value), 1)

[AkaTrouble]

could try >= 89.5 instead of >=90



EDIT

also change < 90 to < 89.5

[jh51745]

This doesn't work either

Private Sub Grade_Exit(ByVal Cancel As MSForms.ReturnBoolean)

Dim GrdNo as long
GrdNo = Application.Round(val(Grade.Value), 1)

Grade.value = Format(Grade.value / 100, "0.0%")

   
 If GrdNo <= 89.9 Then
       Grade.BackColor = &HFF&
       
    ElseIf GrdNo >= 110.1 Then
        Grade.BackColor = &HFF&
     
    ElseIf GrdNo >= 90 And GrdNo <= 110 Then
         Grade.BackColor = CoreNumber.BackColor
 
    End If
End Sub

[jh51745]

Didn't work either. The number 89.9 should turn red where as 90.0 should not change.

[Kyle123]

Being thick

Dim GrdNo as Double

[romperstomper]

Ha - I didn't even see that line!

[jh51745]

Changing long to double works, working code below

Private Sub Grade_Exit(ByVal Cancel As MSForms.ReturnBoolean)

Dim GrdNo as Double
GrdNo = Application.Round(val(Grade.Value), 1)

Grade.value = Format(Grade.value / 100, "0.0%")

   
 If GrdNo <90 Then
       Grade.BackColor = &HFF&
       
    ElseIf GrdNo >110 Then
        Grade.BackColor = &HFF&
     
    ElseIf GrdNo >= 90 And GrdNo <= 110 Then
         Grade.BackColor = CoreNumber.BackColor
 
    End If
End Sub

[jh51745]

If I don't change anything in the cell after being in it I get a run-time error 13 type mismatch and highlights. I have a my original code that works for non % when I exit out of the box without changing anything. it doesn't matter if it is at exit or after update. Any ideas?

Grade.value = Format(Grade.value / 100, "0.0%")

[romperstomper]

Try:

Private Sub Grade_Exit(ByVal Cancel As MSForms.ReturnBoolean)

    Dim GrdNo                 As Double
    GrdNo = Application.Round(CDbl(Replace(Grade.Value, "%", "")), 1)

    Grade.Value = Format(GrdNo / 100, "0.0%")


    If GrdNo < 90 Then
        Grade.BackColor = &HFF&

    ElseIf GrdNo > 110 Then
        Grade.BackColor = &HFF&

    ElseIf GrdNo >= 90 And GrdNo <= 110 Then
        Grade.BackColor = CoreNumber.BackColor

    End If
End Sub

[jh51745]

Same result

[romperstomper]

What do you want to happen if there was a value in there and you delete it? What should the backcolor be?

[mikerickson]

What is a typical entry in the text box?
What is shown if this is inserted before the If statement?

MsgBox GrdNo

[jh51745]

What is a typical entry in the text box?
What is shown if this is inserted before the If statement?

MsgBox GrdNo

I get 100 in the message box after typing in 100.0. After closing the box it displays 100.0% in the textbox like it should

[jh51745]

The color should be

Grade.BackColor = CoreNumber.BackColor

If I enter a number and go to the next box and then return to the this box, but then move onto another box without changing anything or deleting and exiting the box I get the error.

It basically doesn't like it when there are no changes made while being in the box and exiting the box

[romperstomper]

Try this - should cover most things:

Private Sub Grade_Exit(ByVal Cancel As MSForms.ReturnBoolean)

    Dim GrdNo                 As Double
    Dim strText               As String

    If Len(Grade.Value) <> 0 Then
    
        strText = Replace$(Grade.Value, "%", "")
        
        If IsNumeric(strText) Then
        
            GrdNo = Application.Round(CDbl(strText), 1)
            Grade.Value = Format(GrdNo / 100, "0.0%")

            If GrdNo < 90 Then
                Grade.BackColor = &HFF&

            ElseIf GrdNo > 110 Then
                Grade.BackColor = &HFF&

            ElseIf GrdNo >= 90 And GrdNo <= 110 Then
                Grade.BackColor = CoreNumber.BackColor
            End If
        Else
            MsgBox "No text please"
            Cancel = True

        End If
        
    Else
        Grade.BackColor = CoreNumber.BackColor
    End If
End Sub

[jh51745]

Try this - should cover most things:

Private Sub Grade_Exit(ByVal Cancel As MSForms.ReturnBoolean)

    Dim GrdNo                 As Double
    Dim strText               As String

    If Len(Grade.Value) <> 0 Then
    
        strText = Replace$(Grade.Value, "%", "")
        
        If IsNumeric(strText) Then
        
            GrdNo = Application.Round(CDbl(strText), 1)
            Grade.Value = Format(GrdNo / 100, "0.0%")

            If GrdNo < 90 Then
                Grade.BackColor = &HFF&

            ElseIf GrdNo > 110 Then
                Grade.BackColor = &HFF&

            ElseIf GrdNo >= 90 And GrdNo <= 110 Then
                Grade.BackColor = CoreNumber.BackColor
            End If
        Else
            MsgBox "No text please"
            Cancel = True

        End If
        
    Else
        Grade.BackColor = CoreNumber.BackColor
    End If
End Sub

This works, I added the line of code wrong the first time I tried it

[romperstomper]

Can you post a workbook. That works fine for me and handles blank cells, text and numbers or percentages.