EXCEL VBA: How to only accept number format in a VBA Userform Textbox

To accept only numbers in a VBA Userform Textbox for example, use the following code:

Private Sub TextBox1_Change()

    OnlyNumbers

End Sub

Private Sub OnlyNumbers()

    If TypeName(Me.ActiveControl) = "TextBox" Then

        With Me.ActiveControl

            If Not IsNumeric(.Value) And .Value <> vbNullString Then

                MsgBox "Sorry, only numbers allowed"

                .Value = vbNullString

            End If

        End With

    End If

End Sub

DoMyExcel.com | Excel & VBA Consulting | Customized Excel Solutions starting at 89 USD

Services provided to Individuals and Businesses | Free Quote: DoMyExcel@gmail.com

EXCEL VBA: How to convert a Textbox value in number

The textbox always return a string, you have to use one of the function below to convert it to the format you want:

CBool(expression)CByte(expression)CChar(expression)CDate(expression)CDbl(expression)CDec(expression)CInt(expression)CLng(expression)CObj(expression)CSByte(expression)CShort(expression)CSng(expression)CStr(expression)CUInt(expression)CULng(expression) 

CUShort(expression) 


Example if we want to multiply by 2 the number entered in Textbox1:

Private Sub CommandButton1_Click()

a = CInt(TextBox1.Value)

MsgBox ("If we multiply Textbox1 value by 2, the result is: " & a * 2)

End Sub

DoMyExcel.com | Excel & VBA Consulting | Customized Excel Solutions starting at 89 USD

Services provided to Individuals and Businesses | Free Quote: DoMyExcel@gmail.com

Good practice when using EXCEL comparison function (VLOOKUP(), MATCH(), INDEX()) of values in text and number format

The "TRIM()" function in EXCEL helps to prevent error when comparing values in number format with value in text format. This "TRIM()" function will remove all the "space characters: in front of the value and will also convert any number format into a text format.
 



 

This function is particularly interesting when a large number of data need to be compared. It will ensure that you are comparing correctly text to text.

 

One of the good practice is therefore to add one more column to trim the keys of your database, and then to search the trimmed value into this column (works also with MATCH() function, INDEX() function, etc.)

 

 

Created by DoMyExcel.com © | Excel & VBA Consulting | Customized Excel Solutions starting at 29 USD

Services provided to Individuals and Businesses | Free Quote: DoMyExcel@gmail.com