conditional value in a textbox in Excel

I have placed an Active X textbox on my macro-enabled Excel worksheet.  I want to add code to the control.

How do I get the textbox to display the following text:   "Test of code was successful."

if the value in cell A1 minus the value in cell B1 is greater than 0?

So, for example if the value in cell A1 is 2 and the value in B1 is 1, the message should display.  Otherwise, the textbox should remain empty/blank.

Thank you
dbfromnewjerseyAsked:
Who is Participating?
 
Martin LissConnect With a Mentor Older than dirtCommented:
This is better than my last one. It checks to make sure that neither cell is empty.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim lngLastRow As Long

lngLastRow = Range("A1048576").End(xlUp).Row
If Not Intersect(Target, Range("A1:B" & lngLastRow)) Is Nothing Then
    If Cells(Target.Row, "A") <> Empty And Cells(Target.Row, "B") <> Empty Then
        If Cells(Target.Row, "A") > Cells(Target.Row, "B") Then
            ActiveSheet.OLEObjects("Textbox1").Object.Text = "Test of code was successful."
        Else
            ActiveSheet.OLEObjects("Textbox1").Object.Text = ""
        End If
    End If
End If

End Sub

Open in new window

0
 
NorieVBA ExpertCommented:
Are values being entered manually into A1 and B1?
0
 
Martin LissOlder than dirtCommented:
ActiveSheet.OLEObjects("Textbox1").Object.Text = "Test of code was successful."

Open in new window

0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Ryan ChongConnect With a Mentor Commented:
try something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" Or Target.Address = "$B$1" Then
        If IsNumeric(Cells(1, "A")) And IsNumeric(Cells(1, "B")) Then
            If Cells(1, "A") - Cells(1, "B") > 0 Then
                TextBox1.Text = "Test of code was successful."
            Else
                TextBox1.Text = ""
            End If
        Else
            TextBox1.Text = ""
        End If
    End If
End Sub

Open in new window

29075013.xlsm
0
 
Martin LissOlder than dirtCommented:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim lngRows As Long

lngRows = ActiveSheet.UsedRange.Rows.Count
If Not Intersect(Target, Range("A1:B" & lngRows)) Is Nothing Then
    If Cells(Target.Row, "A") > Cells(Target.Row, "B") Then
        ActiveSheet.OLEObjects("Textbox1").Object.Text = "Test of code was successful."
    Else
        ActiveSheet.OLEObjects("Textbox1").Object.Text = ""
    End If
End If

End Sub

Open in new window

0
 
NorieVBA ExpertCommented:
Are the values in the cells derived from formulas?
0
 
Martin LissOlder than dirtCommented:
I’m glad I was able to help.

If you expand the “Full Biography” section of my profile you’ll find links to some articles I’ve written that may interest you.

Marty - Microsoft MVP 2009 to 2017
              Experts Exchange MVE 2015
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2017
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.