Change color of textbox based on length of string in text box

Tennisguuy

Well-known Member
Joined
Oct 17, 2007
Messages
550
Office Version
  1. 2016
Platform
  1. Windows
I have a worksheet that I would like to program the textbox if the value in cell F32 is greater than or equal to 1,000 and there is no text typed in the textbox meaning its blank and the string value would be 0 I want it to turn red. However if F32 is greater than greater than or equal to 1,000 and the user has entered text in the text box and the string length is great than 25 I want it to turn clear or white. I found some code but not sure how to modify it to fit this situation.

I did the following and this worked perfectly but just don't know how to modify the code for the above. This codes changes the textbox based on the value in the text box.

It said to insert a textbox by clicking the developer tab>insert>text box (activeX control), then draw the text box. Then right click on the textbox and select viewcode and paste the following code.

Private Sub TextBox1_Change()
On Error Resume Next
Select Case TextBox1.Value
Case 1 To 10:
TextBox1.BackColor = vbRed
Case 11 To 20:
TextBox1.BackColor = vbGreen
Case Else:
TextBox1.BackColor = vbYellow
End Select
End Sub

I did all of these steps and it work perfectly but I don't know how to change to code to modify my situation above.
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Scott T

Well-known Member
Joined
Dec 14, 2016
Messages
2,615
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Re: How to change color of textbox based on length of string in text box

Try
Code:
Private Sub TextBox1_Change()
On Error Resume Next
stringlen = Len(TextBox1)
Select Case stringlen
Case 1 To 10:
TextBox1.BackColor = vbRed
Case 11 To 20:
TextBox1.BackColor = vbGreen
Case Else:
TextBox1.BackColor = vbYellow
End Select
End Sub
 
Last edited:

Tennisguuy

Well-known Member
Joined
Oct 17, 2007
Messages
550
Office Version
  1. 2016
Platform
  1. Windows
Re: How to change color of textbox based on length of string in text box

Scott thanks that kind of worked but doesn't incorporate the value in cell F32. I input just your code and change the line Case 1 to 10: with Case 0 to 10: and that didn't work. However if I started typing in the text box and the stringlen was less than 10 it worked and turned red and if more than 10 it did change color to white. I also changed your code for Case 11 to 20: to vbwhite
 

Scott T

Well-known Member
Joined
Dec 14, 2016
Messages
2,615
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Re: How to change color of textbox based on length of string in text box

Try


Code:
Private Sub TextBox1_Change()
On Error Resume Next
stringlen = Len(TextBox1)
If stringlen = 0 And Range("F32") >= 100 Then stringlen = 1
Select Case stringlen
Case 1 To 10:
TextBox1.BackColor = vbRed
Case 11 To 20:
TextBox1.BackColor = vbGreen
Case Else:
TextBox1.BackColor = vbYellow
End Select
End Sub

Note since this is a texbox change event the color will not change until the text box is changed. So if F32 is under 100 and the textbox empty and the color is yellow and you change F32 to over or equal to 100, since the textbox did not change the code will not run and the box will not change colors. So that it will change you may want to also have the worksheet change event code


Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$F$32" Then
stringlen = Len(TextBox1)

If stringlen = 0 And Range("F32") >= 100 Then stringlen = 1
    Select Case stringlen
    Case 1 To 10:
    TextBox1.BackColor = vbRed
    Case 11 To 20:
    TextBox1.BackColor = vbGreen
    Case Else:
    TextBox1.BackColor = vbYellow
    End Select
End If
End Sub
 

Tennisguuy

Well-known Member
Joined
Oct 17, 2007
Messages
550
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Re: How to change color of textbox based on length of string in text box

I pasted the code above into the spreadsheet and when the textbox is empty the color is not yellow it's white. After I enter text it changes immediately to red then once it reach string count greater than 10 it does change to green. I am not very good with VBA code so I am not sure if I am doing everything correctly. What do you mean by you many want to have the worksheet change even code.
 

Scott T

Well-known Member
Joined
Dec 14, 2016
Messages
2,615
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Re: How to change color of textbox based on length of string in text box

The Textbox change event only runs when the textbox changes. When you first past the code in it has not run since the textbox has not changed. This is why it changes when you start to enter text. With the text box change event and the worksheet change event after the textbox is changed or F32 is changed the color should be correct.
 

Tennisguuy

Well-known Member
Joined
Oct 17, 2007
Messages
550
Office Version
  1. 2016
Platform
  1. Windows
Re: How to change color of textbox based on length of string in text box

Ok thanks for all of your help Scott. I really really appreciated it.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,789
Messages
5,524,885
Members
409,609
Latest member
Channingz

This Week's Hot Topics

Top