Format textbox in VBA Userform

Hellzing

New Member
Joined
Apr 21, 2021
Messages
5
Office Version
  1. 2019
Platform
  1. Windows
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:

VBA Code:
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!
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
I don't have Excel 2019 to try but on my 2016, after leaving the textbox the -0.25 stayed as it was :unsure:
 
Upvote 0
Zot, thanks for your answer but yeah I keep having the same results, either with positive and negative numbers :(
 
Upvote 0
I just figured out some code for the required sign before the numbers, but still the format problem persists...

VBA Code:
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
 
Upvote 0
I just figured out some code for the required sign before the numbers, but still the format problem persists...

VBA Code:
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
 
Upvote 0
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?
 
Upvote 0
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 ?
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top