Backgrounf formatting in Text Control

[Sir Babydum GBE]

Hi

in TxtCtrl1 when a person starts entering text I want the background to turn red (RGB 255, 0, 0) until a 13th digit is entered - when it reverts to white (it's default colour). If they typ 14 or more character, it goes red again.

How do I accomplish this please?

Thanks

BD

[anandbohra]

Private Sub TxtCtrl1_Change()
If Len(TxtCtrl1.Text) = 13 Then
TxtCtrl1.BackColor = vbRed
Else
TxtCtrl1.BackColor = vbWhite
End If
End Sub

[Sir Babydum GBE]

Private Sub TxtCtrl1_Change()
If Len(TxtCtrl1.Text) <> 13 Then
TxtCtrl1.BackColor = vbRed
Else
TxtCtrl1.BackColor = vbWhite
End If
End Sub

Perfect! Thanks

[rory]

Not really addressing your question, but if I were a user, I would find that really irritating!

[Sir Babydum GBE]

Not really addressing your question, but if I were a user, I would find that really irritating!

True, the problem is that it's not rare for our agents to mistype reference numbers (it's usually a missed or duplicated keystroke). And if we don't get these ref numbers right - our customers won't get what they've been promised - and this with the ones who are already not happy.

In an ideal world I would have like to have built a form that had minimal rules - they complete what they want in what order they choose - but our last form had too many occasions where essential information was missing or incorrect.

It's a difficult thing to create a system that pleases your users but is bullet-proof enough to give you what you need.

having said that, if anyone has examples of userforms in Excel that look nice and are foolproof yet simple, feel free to sheare, cos this one's turned out to be a nightmare!

I think I need to go back to my old job as international peacekeeper and troubleshooter.

[mdmackillop]

Check the length and validity before you move on.
BTW, If you have a list of valid references, why not put them in a Combobox, which will autofill as well.

[vba]Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
Select Case Len(TextBox1)
Case Is < 13
MsgBox "too few letters"
Cancel = True
Case Is > 13
MsgBox "too many letters"
Cancel = True
Case Else
Set c = Columns(1).Find(TextBox1, lookat:=xlWhole)
If Not c Is Nothing Then
MsgBox "Item OK"
Else
MsgBox "Value not found."
Cancel = True
End If
End Select
End Sub

Private Sub CommandButton1_Click()
MsgBox TextBox1
End Sub

[/vba]

[Sir Babydum GBE]

Thanks all, that's great. MD, unfortuately I can't validate these numbers as there are millions of them (literally). So this isn't foolproof validation - but most typos seem to be missing or extra digits - so this should help reduce the error count.

BD

[mdmackillop]

Can you return the account name or whatever from a database? That would give you a check before entering wrong information.

[Sir Babydum GBE]

Can you return the account name or whatever from a database? That would give you a check before entering wrong information.

We don't have customer databases as far as i know, except for the SAP billing system we use. I don't believe I'd get authority to link to that.

[mdmackillop]

I foresee a lot of error checking going on. 13 digits correct every time? I doubt it!

[Sir Babydum GBE]

I foresee a lot of error checking going on. 13 digits correct every time? I doubt it!

It's cross-checked against customer names and addresses, so unless agents get everything wrong, we should be ok - but it's just that the fewer the mistakes, the less digging we need to do.