CODEWITHSUNDEEP
excelvbatextboxnumeric
Alex
Alex

Reputation: 108

VBA Textbox to number value

I have this code and I need textbox 9 and 10 to be number vlaues when entering in my excel table any idea how I can fix this?

Sub FillRanges(ws As Worksheet, L As Long)
With ws
    .Range("C" & L).Value = (Now)
    .Range("D" & L).Value = Me.TextBox2
    .Range("E" & L).Value = Me.TextBox3
    .Range("F" & L).Value = Me.TextBox4
    .Range("G" & L).Value = Me.TextBox5
    .Range("K" & L).Value = Me.ComboBox1
    .Range("L" & L).Value = Me.ComboBox2
    .Range("M" & L).Value = Me.ComboBox3
    .Range("N" & L).Value = Me.TextBox9
    .Range("O" & L).Value = Me.TextBox10
    .Range("R" & L).Value = Me.TextBox39
    .Range("P" & L).Value = Me.TextBox40
End With

End Sub

Upvotes: 0

Views: 6121

Answers (2)

user3598756
user3598756

Reputation: 29421

I think it's preferable to validate user input before using (writing) it

so you may want to write some quite simple user input validation subs and call them from within controls change event handler, like follows:

Option Explicit

Private Sub TextBox9_Change()
    ValidateNumericInput Me.TextBox9, 0, 10.4 '<--| as soon as this control text changes, call 'ValidateNumericInput' to validate it 
End Sub


Private Sub ValidateNumericInput(tb As MSForms.TextBox, minVal As Double, maxVal As Double)
    Dim errMsg As String

    With tb
        If Len(.Text) > 0 Then '<-- proceed only if there's some text to validate!
            Select Case True
                Case Not IsNumeric(.value) '<--| if not a "numeric" input
                    errMsg = "please enter a number"
                Case CDbl(.Text) < minVal Or CDbl(.Text) > maxVal '<--| if "numeric" input exceeds passed range
                    errMsg = "please enter a number within " & minVal & " and " & maxVal
            End Select
            If errMsg <> "" Then '<--| if error message has been written
                MsgBox "invalid input in " & tb.name & vbCrLf & vbCrLf & errMsg, vbCritical + vbExclamation + vbOKOnly, "Invalid input" '<--| infrm the user
                .Text = "" '<--| delete textbox input
            End If
        End If
    End With
End Sub

where I assumed a Double type input would be needed, but you can easily adapt it to other types

so, you may then add such other subs as:

ValidateStringInput(tb As MSForms.TextBox, validStrings() as String)

and the likes...

Upvotes: 1

J. Kamans
J. Kamans

Reputation: 575

You can use a convert function like CDbl(). It would be something like:

Sub FillRanges(ws As Worksheet, L As Long)
With ws
    .Range("C" & L).Value = (Now)
    .Range("D" & L).Value = Me.TextBox2
    .Range("E" & L).Value = Me.TextBox3
    .Range("F" & L).Value = Me.TextBox4
    .Range("G" & L).Value = Me.TextBox5
    .Range("K" & L).Value = Me.ComboBox1
    .Range("L" & L).Value = Me.ComboBox2
    .Range("M" & L).Value = Me.ComboBox3
    .Range("N" & L).Value = CDbl(Me.TextBox9)
    .Range("O" & L).Value = CDbl(Me.TextBox10)
    .Range("R" & L).Value = Me.TextBox39
    .Range("P" & L).Value = Me.TextBox40
End With

There are also other convert functions. CInt()(integer), CLng()(long) and CDec()(decimal).

Upvotes: 3

Related Questions