Ratio in textbox

[Av8tordude]

I have this code that allows the user to just type numbers in the textbox to avoid typing in the wrong format. I would like to know is this could be set up to allow for typing in a ratio. (i.e. 2:1, 10:1, etc.) so the user avoids typing in the wrong format. The number "1" should always be be constant. Can anyone assist. Thanks

Dim v As String

Select Case Len(tbInv)
    Case 1
        tbInv = Format(tbInv, "$0\.00")
    Case Is > 1
        v = Replace(tbInv, "$", "")
        v = Replace(v, ".", "")
        tbInv = Format(CCur(v) / 100, "$#,#0.00")
    Case Else
End Select

 

[Rosen]

Here is some code that might do the trick:

Private DisableChangeEvent As Boolean
Private Sub TextBox1_Change()
    If DisableChangeEvent Then Exit Sub
    If TextBox1.Text = VBA.Constants.vbNullString Then Exit Sub
    Dim Colon As Boolean
    Dim i As Long
    Dim ValidText As String
    Dim Char As String
    DisableChangeEvent = True
    For i = 1 To VBA.Strings.Len(TextBox1.Text) Step 1
        Char = VBA.Strings.Mid(TextBox1.Text, i, 1)
        If Colon Then
            If Char = "1" Then
                ValidText = ValidText + Char
                Exit For
            End If
        Else
            If Char = ":" Then
                ValidText = ValidText + Char
                Colon = True
            ElseIf IsNumeric(Char) Then
                ValidText = ValidText + Char
            End If
        End If
    Next i
    TextBox1.Text = ValidText
    DisableChangeEvent = False
End Sub
Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    If TextBox1.Text = VBA.Constants.vbNullString Then Exit Sub
    If Not VBA.Strings.Right(TextBox1.Text, 2) = ":1" Then
        If VBA.Strings.Right(TextBox1.Text, 1) = ":" Then
            TextBox1.Text = TextBox1.Text & "1"
        Else
            TextBox1.Text = TextBox1.Text & ":1"
        End If
    End If
End Sub

Please remember to back up any work before running new code.

Hope this helps!