Text box in excel(vba) 1

[sunnytahir]

To any any one who knows better.
Dear Sirs,
I have a text box in excel(vba) and I want the user to write only numeric values with two decimal points.

I have a text box in excel(vba) and I want the user to write only specific date format(dd/mm/yyyy).

thank you in advance.
tahir

 

[PHV]

You may consider some global validation rules in the BeforeUpdate event procedure and some character filtering in the KeyDown event procedure.

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[sunnytahir]

Dear PHV,
Thanks for your reply. I am new to vba and do not know what the validation rule should apply.
Please write a hint.
Thanks in advance.
Tahir

 

[PHV]

Is the textbox a control in a sheet or in an UserForm ?

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[sunnytahir]

Dear PHV,

Thanks for your prompt reply.
Textbox is on a userform.

Tahir

 

[PHV]

For the date textbox you may consider something like this:

Private Sub theDate_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
  If theDate <> "" Then
    If IsDate(theDate) Then
      theDate.Value = Format(CDate(theDate), "dd/mm/yyyy")
    Else
      Cancel = True
    End If
  End If
End Sub

For the numeric textbox you may consider something like this:

Private Sub theNum_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
  If theNum <> "" Then
    If IsNumeric(theNum) Then
      theNum = Format(CCur(theNum), "0.00;-0.00;#")
    Else
      Cancel = True
    End If
  End If
End Sub

Private Sub theNum_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
  Select Case KeyAscii
  Case Is < 32, 45, 46, 48 To 57
    Rem ControlChar, "-", ".", "0" to "9"
  Case Else
    KeyAscii = 0
  End Select
End Sub

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244