Conditional Format a TextBox

[Denny57]

I would appreciate some assistance applying condtional formatting to a textbox that contains the result of this formula

If TextBox1.Value = "No" Then
        TextBox2.Value = "Not Active"
    Else
       TextBox2 = Range("T8").Text

Range("T8") [From another active WS] also contains a formula that will return a variable amount (If TRUE) or "£0.00" (If FALSE).
Values in TextBox2 will be as TEXT

I am trying to apply conditional formatting to TextBox2 when the returned value is the variable amount (i.e. the value is not either "Not Active" or "£0.00")
I have tried the following code, but this applies the formatting to any value, I assume because the actual content of TextBox2 is a formula and not a text value

Private Sub TextBox2_Change()
If TextBox2 = "Not Active" Or Not "£0.00" Then
TextBox2.BackColor = RGB(0, 255, 0)
End If
End Sub

I have also tried using TextBox2.Text and TextBox2.Value
I have even tried adding the folowing but without success.

If TextBox2.= "Not Active" Then TextBox2.BackColor = RGB(0, 0, 0)
If TextBox2 = "£0.00" Then TextBox2.BackColor = RGB(0, 0, 0)

Thanks in advance

 

[mmhill]

set Textbox2 = sheet2.Shapes(1)
Textbox2.Textframe2.TextRange.Text = "Stuff"
Debug.Print Textbox2.Textframe2.TextRange.Text

 

[Denny57]

Thank you for taking the time to try to resolve my enquiry.
I have to be honest and admit that I am a relative beginner when it comes to VBA but I always try to understand the logic behind expressions, their aims the syntax required.

Unfortunately, I am totally confused by this "solution" as I cannot even start to understand what this is trying to achieve, where is should be placed and what confilcts this might have, given that I have multiple sheets in the workbook. I also need to apply this to a significant number of TextBoxes so this cannot be a generic solution.

 

[dmt32]

I have multiple sheets in the workbook. I also need to apply this to a significant number of TextBoxes so this cannot be a generic solution.

Hi,
I am assuming that you have ActiveX Textboxes in your worksheets & your CF code would be same for all?
If so, you can create a common code that each textbox can call

see if following update to your code will do what you want

Place in a STANDARD module

Sub CF(ByVal objTextBox As Object)
    Dim Valid   As XlRgbColor, NotValid As XlRgbColor
    Dim m       As Variant
  
   'Valid = the value is not either "Not Active" or "£0.00"
    Valid = rgbLime
    NotValid = rgbWhite
  
    With objTextBox
        m = Application.Match(.Value, Array("Not Active", "£0.00"), 0)
        .BackColor = IIf(Len(.Value) > 0 And IsError(m), Valid, NotValid)
    End With
End Sub

For each of your worksheet TextBox_Change events, you pass the TextBox object to the code

Private Sub TextBox2_Change()
    CF Me.TextBox2
End Sub

Hope I have understood your requirement correctly but amend code as required

Hope Helpful

Dave

 

[mmhill]

Sub CF(ByVal objTextBox As Object)
    Dim Valid   As XlRgbColor, NotValid As XlRgbColor
    Dim m       As Variant
 
   'Valid = the value is not either "Not Active" or "£0.00"
    Valid = rgbLime
    NotValid = rgbWhite

I did not know about XlRgbColor enumeration. Thank you for that.

 

[Denny57]

Hi,
I am assuming that you have ActiveX Textboxes in your worksheets & your CF code would be same for all?
If so, you can create a common code that each textbox can call

see if following update to your code will do what you want

Place in a STANDARD module

Sub CF(ByVal objTextBox As Object)
    Dim Valid   As XlRgbColor, NotValid As XlRgbColor
    Dim m       As Variant
 
   'Valid = the value is not either "Not Active" or "£0.00"
    Valid = rgbLime
    NotValid = rgbWhite
 
    With objTextBox
        m = Application.Match(.Value, Array("Not Active", "£0.00"), 0)
        .BackColor = IIf(Len(.Value) > 0 And IsError(m), Valid, NotValid)
    End With
End Sub

For each of your worksheet TextBox_Change events, you pass the TextBox object to the code

Private Sub TextBox2_Change()
    CF Me.TextBox2
End Sub

Hope I have understood your requirement correctly but amend code as required

Hope Helpful

Dave

Thank You.. This works perfectly

 

[dmt32]

Thank You.. This works perfectly

Most welcome & appreciate your feedback

Dave

 

[dmt32]

I did not know about XlRgbColor enumeration. Thank you for that.

Welcome - always worth a rummage in the object Browser to see what gems MS have included.

Dave