data type of textbox in excel
1) How can we set data type of text box to number in excel so that it always accepts number only and if user inputs character then it'll not accept it.
2) How can we set not null to a text box so that textbox would become mandetory..
»
- 0713910067's blog
- Login or register to post comments
- 7356 reads
Data type of TextBox in Excel
Hi,
You can use the TextBox_KeyPress event to restrict the allowed characters. For example:
' ************************* ' ************************* '
Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
Case Asc("0") To Asc("9")
' Allow digits
Case Asc("-")
' Allow only one negative sign at the first position
If InStr(1, Me.TextBox1.Text, "-") > 0 Or Me.TextBox1.SelStart > 0 Then
KeyAscii = 0
End If
Case Asc(".")
' Allow only one decimal point
If InStr(1, Me.TextBox1.Text, ".") > 0 Then
KeyAscii = 0
End If
Case Else
KeyAscii = 0
End Select
End Sub
' ************************* ' ************************* '
To make TextBox mandatory you can check whether its value is empty, for example:
- when submit the form, If you use TextBox in the UserForm;
- into the Worksheet_Deactivate event or add some additional submit button, If the Textbox is on the worksheet.
Best regards.