Need assistance to format a userform textbox

Hi VBA master,

I need your help to format a textbox, where I would like the textbox to follow the structure of "##.###.###-###.###"

I have two ideas, either when user input the number, it will follow the structure simultaneously or when the user click the textbox (keypressdown event), the format will show up like this __.___.___-___.___ and user just need to fill the blank.

Hope you understand and can help me with it.

Below is my current code that doesn't work....

Thanks in advance

Please Login or Register  to view this content.

Hi, so sad it cant live up to my expectation. But really appreciate for the alternative suggestion!!

Hi!

What a crack! Hahaha, thanks. However, I discovered one shortcoming by using that code.It doesn't allow us to erase the text in that textbox using backspace, because the . and - will still be there. Thus, create "Clear" function could be a solution somehow.

Hahaha,

Cheers and stay safe!

Originally Posted by mbrillian

It doesn't allow us to erase the text in that textbox using backspace

Maybe try like this to let the user use the backspace:

Please Login or Register  to view this content.

Place the code on top of your userform code module

Please Login or Register  to view this content.

Hi Mr. Karmapala,

Thanks in advance for your concern, but the code is not working, hehehe. Could it be because I modify a little of your previouse code? Here is the modified code to show "." and "-" :

Private Sub Text_box_Change()
If Len(Text_box.Value) = 2 Then Text_box.Value = Text_box.Value & "."
If Len(Text_box.Value) = 6 Then Text_box.Value = Text_box.Value & "."
If Len(Text_box.Value) = 10 Then Text_box.Value = Text_box.Value & "."
If Len(Text_box.Value) = 12 Then Text_box.Value = Text_box.Value & "-"
If Len(Text_box.Value) = 16 Then Text_box.Value = Text_box.Value & "."

Hi Mr. Jindom,

Thanks for your response. I've tried your code but the shortcoming of cannot erase the value using a backspace still becomes an issue here. In fact, the code will not give me smooth input experience, because when user start type some characters, all the "." and "-" will show up

But still appreciate your effort. Cheers

Hi mbrillian,

Originally Posted by mbrillian

Hi Mr. Karmapala,
the code is not working, hehehe.

The code in my post #4 is different than the code in my post #6.
code post #4 put the "." or "-" before the next number
---> example : user type 1 then 2 then the "." show before the user type the third number --> 12.

code post #6. put the "." or "-" after the next number
---> example : user type 1 then 2 then 3 then the "." show after the user type the third number --> 12.3

Then the Sub TextBox1_KeyDown, is for the backspace.
when the user hit the backspace once to erase the "." or the "-",
the code double erase the character as if the user hit the backspace twice.

Example :
the display in the text box is : 12.3
The user want to correct number 2,
in a "real world" the user need to hit backspace 3 times so the number left is 1
within the code, the user just need two times hit the backspace, the number left is 1.

Could it be because I modify a little of your previouse code?

It's not because you modify code post #4, but because the code is different than code post #6 .

Originally Posted by karmapala

It's not because you modify code post #4, but because the code is different than code post #6 .

Hi Sir,

My bad, I thought its the extention code of previous code instead of new one.

Meanwhile, I wanna confirm that the code works perfectly and has answered this post issue. Thanks

Hi Mr. Jindon,

I've your code, and the backspace works, But this time the "." and "-" show up after we input 14 characters. Hehe. But worry not, that's other alternative that can be use. Thanks for your contribution btw.

Originally Posted by mbrillian

However if using my previous code of #,### and input "2,000"

mbrillian, I don't exactly get what you mean on "2,000".
Did you mean when the user input the number, he also hit the comma key on the computer keyboard ?
A. So, he hit 2, hit comma, hit zero, hit zero, hit zero ....

Or he input without hitting the comma key ?
B. So, he hit 2, hit zero, hit zero, hit zero

it will becomes 2 when I place the value of the textbox into a cell.

If you want to put the value in the text box to a cell,
assuming that the user input is in model B, then I think maybe use the code like this :

Please Login or Register  to view this content.

The CommandButton1_Click Sub is at the time to input what's in the text-box into a cell.
Change the "Range("A1").Value" according to your situation.

Is it possible to use "." in the formatting instead of ","

Within the code, I think the formatting still need to use ",".
But the display result show "." . Please have a look to the image below :

2020-06-21_14-57-18.gif

Please don't forget that your system for digit grouping is "." and for decimal point is ",".
Also in Excel Option Advanced Editing Options, tick the Use System Separator.

Hi Mr. Karmapala!

Thanks for the answer, the cdbl code is what I need.