Format textbox in VBA Userform

[Hellzing]

Hello guys, newbie here asking for help...

I am working on a userform wich requieres the user to fill some information on different textboxes, they can contain only decimal numbers and must have either a plus or minus sign at the beggining, e.g:
+1.25
-0.75
-2.25

I can´t figure out what would be the correct format for the textbox, and how to force the user to input + or - sign before the numbers. So far I have this:

Private Sub mytb_AfterUpdate()
    Me.mytb.value = Format(Me.mytb.value, "+#0.##;-#0.##")
End Sub

Private Sub mytb_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    Select Case KeyAscii
        Case vbKey0 To vbKey9
        Case vbKeySubtract
        Case Asc("-")
        Case vbKeyAdd
        Case Asc("+")
        Case vbKeyDecimal
        Case vbKeyDelete
        Case vbKeyBack
        Case vbKeyReturn
        Case Else
            KeyAscii = 0
            Beep
    End Select
End Sub

So if I try to input something like:
-0.25
after leaving the textbox I get:
-25,

Crossing fingers for some help, thanks in advance!

 

[Zot]

I don't have Excel 2019 to try but on my 2016, after leaving the textbox the -0.25 stayed as it was

 

[Hellzing]

Zot, thanks for your answer but yeah I keep having the same results, either with positive and negative numbers

 

[Hellzing]

I just figured out some code for the required sign before the numbers, but still the format problem persists...

Private Sub mytb_Change()
'   Check if content is valid
If Left(Me.mytb.value, 1) = "+" Or Left(Me.mytb.value, 1) = "-" Then
    Me.mytb.value = Format(Me.mytb.value, "+#.##;-#.##")
Else
    MsgBox "Text must include + ó -", vbOKOnly + vbExclamation, "Invalid data"
'   Select textbox content
    With Me.mytb
        .SelStart = 0
        .SelLength = Len(.Text)
    End With
End If
End Sub

 

[Zot]

I just figured out some code for the required sign before the numbers, but still the format problem persists...

Private Sub mytb_Change()
'   Check if content is valid
If Left(Me.mytb.value, 1) = "+" Or Left(Me.mytb.value, 1) = "-" Then
    Me.mytb.value = Format(Me.mytb.value, "+#.##;-#.##")
Else
    MsgBox "Text must include + ó -", vbOKOnly + vbExclamation, "Invalid data"
'   Select textbox content
    With Me.mytb
        .SelStart = 0
        .SelLength = Len(.Text)
    End With
End If
End Sub

Why do you need to format it in TextBox? Can be displayed as anything in text. You just need to format it when you copy to sheet

 

[Hellzing]

But in that case, what would be the format for the cells?

 

[Zot]

But in that case, what would be the format for the cells?

As far as I know the TextBox does not have any Format property. Whatever format you put to TextBox, it will just affecting how entered data is displayed in the Textbox.

I tried your code again and i warned me if I did not put + or - in front. If I removed the routine to force user to enter + or - sign, I still get the right display. Here are same samples. Just for your reference.
Keystroke > Result
1 > +1.
-.25 > -.25
+34 > +3.4
-0.1 > +..1 (Immediately after I pressed 0, the -ve sign will change to +ve)

Perhaps it is something to do with your Excel regional version or language setting?

 

[Hellzing]

Perhaps it is something to do with your Excel regional version or language setting?

First off, thanks for taking the time to test it by yourself. I did exactly as you say, removed all of the code except for the format line and I get the same results as you, so...
I think I give up with this method, it behaves quite clumsy specially if zeroes are involved. Besides I found that you can bypass the sign requirement after the alert popup is closed and focus is on other textboxes.

Anyway thank you so much for your kind help ?