Add a special format to a textbox to work while typing

If you open the userform in the attached workbook, you will see a textbox with the word "OPERATOR". This word is fixed and cannot be deleted as per the code which enables it. After that word, the textbox has visible space for 7 more characters. This means that the word "OPERATOR" and 7 characters can be visible in the box at the same time.

What I want is to have three numbers after the word, followed by a hyphen and then three further numbers. Therefore for example, I can have OPERATOR123-456 in the textbox. I want to have VBA that would format the box as the user types and automatically add the hyphen after the first three numbers perhaps. Finally, after the 6th number (i.e. the 7th character counting the hyphen) is entered, VBA code should prevent the user from entering any further characters in that box.

I am not looking for an Exit routine as I would like the box to be formatted as one types.

Any clue how to code this?

Originally Posted by 6StringJazzer

Here is one solution. Note that when you are processing raw user input there are tons of things the user can do wrong. I have caught most of them here, although the user will have problems if he has entered numbers past the hyphen and then tries to backspace over it.

This solution is spot on! I like the way you made it possible for someone to delete before the hyphen (instead of backspace) and start over the data entry if something needs changing. Good job on the solutions for possible scenarios too!

Thanks a lot!

You didn't ask me any questions but there is a coding technique here that is essential for things like this. As part of the validation, the code may change the content of the textbox. When that happens it will fire the TextBox1_Change event, causing a recursive call. This gets very sticky. So if the code is changing the textbox, it sets a flag to say "The code is making this change, not the user." So at the beginning of the Sub, you check the flag, and if it's code making the change then you just exit.

Originally Posted by 6StringJazzer

You didn't ask me any questions but there is a coding technique here that is essential for things like this. As part of the validation, the code may change the content of the textbox. When that happens it will fire the TextBox1_Change event, causing a recursive call. This gets very sticky. So if the code is changing the textbox, it sets a flag to say "The code is making this change, not the user." So at the beginning of the Sub, you check the flag, and if it's code making the change then you just exit.

What I should have also asked is, what if I wanted to have the same validation for the textbox but without the word "OPERATOR", how would the code look?

Any clue 6StringJazzer?

In the first line of code where the prefix is defined, change

"OPERATOR"

to

""

Sounds good; thanks!