VBA: Diference between 2 TextBox user Form

DECOVIOTI

New Member
Joined
Dec 11, 2020
Messages
22
Office Version
  1. 2019
Platform
  1. Windows
Hello guys

I am developing a form in VBA that has "Text 1" and "Text 2" which are trying to subtract between them and show the result in Text3. I am currently using a macro below which is working.
My problem is that when I'm trying to create a validation, in which, for example, if the user types "Text 1" = 1000 and not "Text 2" = 2000, I would like the vba to display a message to the user such as "Text1" cannot be less than "Text2".and a reset Text1 and Text 2 and Text3.
Could you please help me?

VBA Code:
Sub SubtrairTXT()

'
On Error Resume Next

If Text1.Value <> "" And Text2.Value <> "" Then
    If Text1.Value >= 0 And Text2.Value >= 0 Then
        Text3.Value = Text1.Value - Text2.Value
        Text3.Value = Format(Text3.Value, "$#,##0.00;-$#,##0.00")
    Else
        Cancel = True
    End If
Else
    Cancel = True
End If

End Sub
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
7,628
Office Version
  1. 2019
Platform
  1. Windows
Hi
try following

Place code in your forms code page

VBA Code:
Sub SubtrairTXT()
    Dim Txt1        As Double, Txt2 As Double
    Dim ValidEntry  As Boolean
    
    Txt1 = Val(Me.Text1.Value)
    Txt2 = Val(Me.Text2.Value)
    
    ValidEntry = Txt1 > Txt2
    
    Me.Text1.BackColor = IIf(ValidEntry, vbWindowBackground, vbRed)
    If ValidEntry Then
        Me.Text3.Value = Format(Txt1 - Txt2, "$#,##0.00;-$#,##0.00")
    Else
        MsgBox "Text1 cannot be less than Text2", 48, "Entry Error"
        Me.Text1.SetFocus
    End If
    
End Sub

I assume you are calling the code from Text1 & Text2 change events?

VBA Code:
Private Sub Text1_Change()
    SubtrairTXT
End Sub

Private Sub Text2_Change()
    SubtrairTXT
End Sub

Dave
 

DECOVIOTI

New Member
Joined
Dec 11, 2020
Messages
22
Office Version
  1. 2019
Platform
  1. Windows
Hi
try following

Place code in your forms code page

VBA Code:
Sub SubtrairTXT()
    Dim Txt1        As Double, Txt2 As Double
    Dim ValidEntry  As Boolean
   
    Txt1 = Val(Me.Text1.Value)
    Txt2 = Val(Me.Text2.Value)
   
    ValidEntry = Txt1 > Txt2
   
    Me.Text1.BackColor = IIf(ValidEntry, vbWindowBackground, vbRed)
    If ValidEntry Then
        Me.Text3.Value = Format(Txt1 - Txt2, "$#,##0.00;-$#,##0.00")
    Else
        MsgBox "Text1 cannot be less than Text2", 48, "Entry Error"
        Me.Text1.SetFocus
    End If
   
End Sub

I assume you are calling the code from Text1 & Text2 change events?

VBA Code:
Private Sub Text1_Change()
    SubtrairTXT
End Sub

Private Sub Text2_Change()
    SubtrairTXT
End Sub

Dave
Hello Dave
Thanks for the replaying
I put this vba code in a module and then I am calling via

Private Sub Text1_Change()
Call Module3.SubtrairTXT
End Sub

Private Sub Text2_Change()
Call Module3.SubtrairTXT
End Sub
Is it going to work if I put in a module? I have a Sub
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
7,628
Office Version
  1. 2019
Platform
  1. Windows
If you want to place in standard you then replace with this updated code

VBA Code:
Sub SubtrairTXT(ByVal Form As Object)
    Dim Txt1        As Double, Txt2 As Double
    Dim ValidEntry  As Boolean
    
    Txt1 = Val(Form.Text1.Value)
    Txt2 = Val(Form.Text2.Value)
    
    ValidEntry = Txt1 > Txt2
    
    Form.Text1.BackColor = IIf(ValidEntry, vbWindowBackground, vbRed)
    If ValidEntry Then
        Form.Text3.Value = Format(Txt1 - Txt2, "$#,##0.00;-$#,##0.00")
    Else
        MsgBox "Text1 cannot be less than Text2", 48, "Entry Error"
        Form.Text1.SetFocus
    End If
    
End Sub

and to call from change event codes

VBA Code:
Private Sub Text1_Change()
    SubtrairTXT Me
End Sub

Private Sub Text2_Change()
    SubtrairTXT Me
End Sub

Dave
 

Forum statistics

Threads
1,175,819
Messages
5,899,659
Members
434,794
Latest member
head

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
Top