userform textbox to always have a decimal.

[danbates]

Hi,

At the moment I have my textbox allowing 5 characters. These can only be numbers or decimal(s).

I was wondering if the following was possible?

If a user was to enter 11111. Instead of 11111 showing it would automatically put the decimal in place and it always be the third character, so it would show as 11.11

Any help would be appreciated.

Thanks

Dan

 

[davesexcel]

what kind of text-box are you using?

 

[Trevor G]

The best thing would be to add an error trap to invoke a message box letting them know, and clear the text box value and take them to complete it again.

 

[danbates]

Hi,

davesexcel: It is a userform text box.

Trevor: Any chance you could help me add in an error trap?

thanks

Dan

 

[mikerickson]

Do you want the result to always be 2 digits followed by . and then two other digits?

 

[danbates]

Hi,

Yes please.

 

[mikerickson]

This might be one approach.

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    Dim newString As String
    Dim goodStrings As Variant, onePattern As Variant
    With TextBox1
        newString = Left(.Text, .SelStart) & Chr(KeyAscii) & Mid(.Text, 1 + .SelStart + .SelLength)
    End With
    
    goodStrings = Array(".", "#.", "##.", ".#", ".##", "#.#", "#.##", "##.#", "##.##")
    
    For Each onePattern In goodStrings
        If newString Like onePattern Then GoTo AcceptInput
    Next onePattern
        KeyAscii = 0
        Beep
AcceptInput:
End Sub

Private Sub TextBox1_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    Dim nStart As Long, nLen As Long
    
    With TextBox1
        If .SelText Like "*.*" Then
            
            If InStr(.SelText, ".") <= (Len(.SelText) / 2) Then
                nStart = InStr(.Text, ".")
                nLen = .SelLength + .SelStart - nStart
                Me.Caption = nStart & ":" & nLen
            Else
                nStart = .SelStart
                nLen = InStr(.Text, ".") - nStart - 1
            End If
            .SelStart = nStart
            .SelLength = nLen
        End If
    End With
End Sub

Private Sub UserForm_Initialize()
    TextBox1.AutoWordSelect = False
    TextBox1.Text = "."
    TextBox1.EnterFieldBehavior = fmEnterFieldBehaviorRecallSelection
End Sub

 

[dmt32]

Hi,
Assuming you have defined some control on number of characters allowed in your textbox then try following & see if will do what you want

Private Sub TextBox1_AfterUpdate()
    With Me.TextBox1
        .Text = Replace(.Text, ".", "")
        .Text = Left(.Text, 2) & "." & Right(.Text, 2)
    End With
End Sub

May need some polish but hopefully, goes in right direction

Change TextBox1 name as required

Dave

 

[danbates]

Hi,

mikerickson: When I load the userform you code shows the decimal in the textbox but I can't enter any numbers?

Dave: Your code does what I have asked, so thank you for that but now I've realised it has created a few more problems and that's my fault with my initial question. The problem I've got Dave is if the user enters 20 your code enters 20.20 which isn't right.

So I was wondering if you could possible alter your code to suit the following examples:

user enters / your code does
1 / 01.00
11 / 11.00
02 / 02.00

So looking at that I think what I am asking is, if the user enters 2 numbers I would like your code to fill the rest with zeros but if the user enters 1 number then I would like your code to put 1 zero before the entered number and 2 zeros after.

Does all that make sense?

Thanks again for your help from both of you.

Dan

 

[dmt32]

As with all these things, we take a bit of guess what OP is aiming for when offering suggestions which is why I asked if you had a defined some control on number of characters being entered - but no worries, hopefully one of us will resolve for you.

Try this update

Private Sub TextBox1_AfterUpdate()
    With Me.TextBox1
        If Len(.Text) < 4 Then
            .Text = Format(.Text, "00.00")
        Else
            .Text = Replace(.Text, ".", "")
            .Text = Left(.Text, 2) & "." & Right(.Text, 2)
        End If
    End With
End Sub

Dave