asked on

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

Norie

Are values being entered manually into A1 and B1?

Martin Liss

ActiveSheet.OLEObjects("Textbox1").Object.Text = "Test of code was successful."

Open in new window

SOLUTION

Ryan Chong

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Martin Liss

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

ASKER CERTIFIED SOLUTION

Martin Liss

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Norie

Are the values in the cells derived from formulas?

Martin Liss

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