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

Tennisguuy

Well-known Member
Joined
Oct 17, 2007
Messages
564
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.
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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:
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

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
Back
Top