Can you change the forecolor of various portions of text within a userform textbox?

[rizzo93]

My users must follow a certain format when entering a project summary so that all summaries look consistent from a formatting perspective.

For example, I'm looking for "3/4/19", NOT "03/04/19" or "3/4/2019". Another example is, "Procurement is required" NOT "Procurement must be done". Again, just making things consistent across all user input.

Using formulas within a worksheet, I'm calculating where the problem spots are. I then provide a summary of what needs to be corrected. But I am thinking of taking it one step further by colorizing the problem errors within a textbox to make them more prominent (my team is very visual and doesn't like to read).

So if my textbox starts out with forecolor "vbwhite" and backcolor "vbgrey", I have a checkbox in the userform that will turn all of the text vbgreen and the backcolor to vbblack. But I just want it to make things like the wrongly formatted "03/04/19" to be vbgreen while changing the rest of the text to something dimmer. And if there's another problem spot, that would also be vbgreen.

Ultimately, I'd like one subroutine to go through and find the problem areas, and then call another subroutine to make just those areas a different color.

Can this be done without needing a degree in rocket science?

 

[Norie]

You can't apply different formatting to different sections of a textbox on a userform.

 

[shg]

You could put it in a cell and apply character-level formatting there.

 

[DanteAmor]

An idea.
You could separately label the numbers that are wrong, for example:

The code while writing in the textbox:

Private Sub TextBox1_Change()
    ds = Split(TextBox1, "/")
    If UBound(ds) = 2 Then
        Label1.ForeColor = vbGreen
        Label1.Caption = ds(0)
        Label2.ForeColor = vbGreen
        Label2.Caption = ds(1)
        Label3.ForeColor = vbGreen
        Label3.Caption = ds(2)
        If Left(ds(0), 1) = "0" Then
            Label1.ForeColor = vbRed
            Label1.Caption = ds(0)
        End If
        If Left(ds(1), 1) = "0" Then
            Label2.ForeColor = vbRed
            Label2.Caption = ds(1)
        End If
        If Len(ds(2)) > 2 Then
            Label3.ForeColor = vbRed
            Label3.Caption = ds(2)
        End If
    End If
End Sub


Private Sub TextBox2_Change()
    ds = Split(TextBox2, "/")
    If UBound(ds) = 2 Then
        Label4.ForeColor = vbGreen
        Label4.Caption = ds(0)
        Label5.ForeColor = vbGreen
        Label5.Caption = ds(1)
        Label6.ForeColor = vbGreen
        Label6.Caption = ds(2)
        If Left(ds(0), 1) = "0" Then
            Label4.ForeColor = vbRed
            Label4.Caption = ds(0)
        End If
        If Left(ds(1), 1) = "0" Then
            Label5.ForeColor = vbRed
            Label5.Caption = ds(1)
        End If
        If Len(ds(2)) > 2 Then
            Label6.ForeColor = vbRed
            Label6.Caption = ds(2)
        End If
    End If
End Sub

 

[Jon Peltier]

Are the problems with entries in the userform or with entries in worksheet cells?

 

[Fazza]

if your formulas can identify the problems, or you can specify/articulate the details via rules/logic, then that means some program code could be written to do the fix-ups

hard to know how involved that would be, but it is hopefully not a big deal. so the users can enter the data just like they do now & the code would change it to what is acceptable

should be easier for everyone? a win-win

just needs some further explanation of the setup and details for people [edit: forum users] to advise (on exact details of the solution/code)

 

[rizzo93]

Thanks, Norie.

 

[rizzo93]

You could put it in a cell and apply character-level formatting there.

Hmm, I had not considered that, but I think I see your point. I'll keep that in mind. Thanks, shg.

 

[rizzo93]

An idea.
You could separately label the numbers that are wrong, for example:

The code while writing in the textbox:

Private Sub TextBox1_Change()
    ds = Split(TextBox1, "/")
    If UBound(ds) = 2 Then
        Label1.ForeColor = vbGreen
        Label1.Caption = ds(0)
        Label2.ForeColor = vbGreen
        Label2.Caption = ds(1)
        Label3.ForeColor = vbGreen
        Label3.Caption = ds(2)
        If Left(ds(0), 1) = "0" Then
            Label1.ForeColor = vbRed
            Label1.Caption = ds(0)
        End If
        If Left(ds(1), 1) = "0" Then
            Label2.ForeColor = vbRed
            Label2.Caption = ds(1)
        End If
        If Len(ds(2)) > 2 Then
            Label3.ForeColor = vbRed
            Label3.Caption = ds(2)
        End If
    End If
End Sub


Private Sub TextBox2_Change()
    ds = Split(TextBox2, "/")
    If UBound(ds) = 2 Then
        Label4.ForeColor = vbGreen
        Label4.Caption = ds(0)
        Label5.ForeColor = vbGreen
        Label5.Caption = ds(1)
        Label6.ForeColor = vbGreen
        Label6.Caption = ds(2)
        If Left(ds(0), 1) = "0" Then
            Label4.ForeColor = vbRed
            Label4.Caption = ds(0)
        End If
        If Left(ds(1), 1) = "0" Then
            Label5.ForeColor = vbRed
            Label5.Caption = ds(1)
        End If
        If Len(ds(2)) > 2 Then
            Label6.ForeColor = vbRed
            Label6.Caption = ds(2)
        End If
    End If
End Sub

Another interesting option, DanteAmor. Thanks!

Oh and please tell me how you got an image posted into this thread.

 

[rizzo93]

Are the problems with entries in the userform or with entries in worksheet cells?

The errors with the format are in the text blocks themselves, which are caused by the user typing them in before getting to me for processing -- not an Excel error.