VBA Code to apply a value to a cell based on the comparative values of 2 other cells

[Denny57]

I will be adding hundreds of records to a data table via a user form and have a need for each new record to apply a certain value to a specfic column based on the values in two other cells / text boxes in the record to be added.

Requirement
If Value TextBox1 = Value TextBox2 then Value TextBox3 = A
If Value TextBox1 > Value TextBox2 then Value TextBox3 = B
If Value TextBox1 < Value TextBox2 then Value TextBox3 = C

I understand that I cannot apply a formula directly into TextBox3 in the User Form so I am looking for some VBA code that can be used to apply this calculated value to Column E for each new record, either by updating the User Form (perhaps by using AfterUpdate or some similar VBA command) or as part of the AddRecord Command.

I could apply the formula to each cell in Column E in advance but I do think this is the best option.

Thanks in advance

 

[breynolds0431]

Hi. You could use some textbox exit events on the textbox1 and textbox2. Try the below....

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

With Me
    If Not .TextBox1.Value = vbNullString And Not .TextBox2.Value = vbNullString Then
        If .TextBox1.Value = .TextBox2.Value Then
            .TextBox3.Value = "A"
        ElseIf .TextBox1.Value > .TextBox2.Value Then
            .TextBox3.Value = "B"
        ElseIf .TextBox1.Value < .TextBox2.Value Then
            .TextBox3.Value = "C"
        End If
    Else
        .TextBox3.Value = ""
    End If
End With
End Sub

Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
With Me
    If Not .TextBox1.Value = vbNullString And Not .TextBox2.Value = vbNullString Then
        If .TextBox1.Value = .TextBox2.Value Then
            .TextBox3.Value = "A"
        ElseIf .TextBox1.Value > .TextBox2.Value Then
            .TextBox3.Value = "B"
        ElseIf .TextBox1.Value < .TextBox2.Value Then
            .TextBox3.Value = "C"
        End If
    Else
        .TextBox3.Value = ""
    End If
End With
End Sub

 

[Denny57]

Hi. You could use some textbox exit events on the textbox1 and textbox2. Try the below....

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

With Me
    If Not .TextBox1.Value = vbNullString And Not .TextBox2.Value = vbNullString Then
        If .TextBox1.Value = .TextBox2.Value Then
            .TextBox3.Value = "A"
        ElseIf .TextBox1.Value > .TextBox2.Value Then
            .TextBox3.Value = "B"
        ElseIf .TextBox1.Value < .TextBox2.Value Then
            .TextBox3.Value = "C"
        End If
    Else
        .TextBox3.Value = ""
    End If
End With
End Sub

Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
With Me
    If Not .TextBox1.Value = vbNullString And Not .TextBox2.Value = vbNullString Then
        If .TextBox1.Value = .TextBox2.Value Then
            .TextBox3.Value = "A"
        ElseIf .TextBox1.Value > .TextBox2.Value Then
            .TextBox3.Value = "B"
        ElseIf .TextBox1.Value < .TextBox2.Value Then
            .TextBox3.Value = "C"
        End If
    Else
        .TextBox3.Value = ""
    End If
End With
End Sub

Thank You for this solution

 

[Denny57]

Hi. You could use some textbox exit events on the textbox1 and textbox2. Try the below....

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

With Me
    If Not .TextBox1.Value = vbNullString And Not .TextBox2.Value = vbNullString Then
        If .TextBox1.Value = .TextBox2.Value Then
            .TextBox3.Value = "A"
        ElseIf .TextBox1.Value > .TextBox2.Value Then
            .TextBox3.Value = "B"
        ElseIf .TextBox1.Value < .TextBox2.Value Then
            .TextBox3.Value = "C"
        End If
    Else
        .TextBox3.Value = ""
    End If
End With
End Sub

Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
With Me
    If Not .TextBox1.Value = vbNullString And Not .TextBox2.Value = vbNullString Then
        If .TextBox1.Value = .TextBox2.Value Then
            .TextBox3.Value = "A"
        ElseIf .TextBox1.Value > .TextBox2.Value Then
            .TextBox3.Value = "B"
        ElseIf .TextBox1.Value < .TextBox2.Value Then
            .TextBox3.Value = "C"
        End If
    Else
        .TextBox3.Value = ""
    End If
End With
End Sub

I now have another complication following on from the above solution, which works fine.

The values of TextBox1 and TextBox2 are numbers and when these are sent to the worksheet they are treasted as text so I need to have these converted into numeric format in the WS. Previously I have used the following code to convert a textbox value to numeric, but as the Sub already exists I have tried to include the numeric formatting in to this sub-routine, but when I try this I receive a "Type Mismatch Error 13" message.

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Me.TextBox1 = CDbl(Format(Me.TextBox1, "#,##0"))
End Sub

Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Me.TextBox2 = CDbl(Format(Me.TextBox2, "#,##0"))
End Sub

I am looking to retain the existing solution but to adapt the code so that the values of TextBox1 & TextBox2 are populated into the Ws in a numeric format.

Hoping you can assist

 

[breynolds0431]

I don't think you'd need to convert to a double or update the formatting. You should be able to just use .Value to transfer the textbox amount to a worksheet as a value. If you leave that part off, then the amount would be transferred to the worksheet as text. See if the below example helps.

Sheets("Sheet1").Range("A1") = Me.TextBox1.Value

For the sheet cells, you'd also want to check to ensure the formatting of that cell is a number, which you could do programmatically as well. Something like the below...

With Sheets("Sheet1").Range("A1") .Value = Me.TextBox1.Value .NumberFormat = "#,##0" End With