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

Denny57

Board Regular
Joined
Nov 23, 2015
Messages
235
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Hi. You could use some textbox exit events on the textbox1 and textbox2. Try the below....

VBA Code:
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
 
Upvote 0
Solution
Hi. You could use some textbox exit events on the textbox1 and textbox2. Try the below....

VBA Code:
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
 
Upvote 0
Hi. You could use some textbox exit events on the textbox1 and textbox2. Try the below....

VBA Code:
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,919
Messages
6,175,368
Members
452,638
Latest member
Oluwabukunmi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top