Text format

[nasreen]

Hi
I want to change the text format of a text box to numeric as (##,###.00).
It is very easy to do in the event of LostFocus, GotFocus etc.

But I want to do this in the event of text change. As I type a number it shoud be formatted properly.

I coded :

Private Sub TxtAmnt_Change()
TxtAmnt.Text = Format(Trim(TxtAmnt.Text), "#,###.00")
End Sub

But this doesn't work properly.
Any idea to solve this.

With regards,
Nasreen

[Code Doc]

You should not try to format it until all the numbers are typed. Notice how Excel works on formatted cells.

[LaVolpe]

Agree with Code Doc. There are 2 issues with what you are trying to do:

1) You are changing the text property each time user enters a keystroke, which could trigger a 2nd Change event reproducing unexpected results

2) The user will lose their cursor position each time a 2nd change event fires and the cursor will move back to the 1st character which can be very annoying for the user.

[nasreen]

Thanks
Solved almost
One of my local friend forwarded me an idea.

Private Sub TxtAmnt_Change()
TxtAmnt.Text = Format(Trim(TxtAmnt.Text), "#,###")
SendKeys "{end}"
End Sub

This is OK.

Nasreen

[gavio]

This works when only digits are inserted.

Here's a code that allows only digits being inserted. It also prevents copy/paste (both, via keyboard and/or menu):

Option Explicit
  Private Declare Function GetWindowLong Lib "User32" Alias "GetWindowLongA" (ByVal hwnd As Long, ByVal nIndex As Long) As Long
  Private Declare Function SetWindowLong Lib "User32" Alias "SetWindowLongA" (ByVal hwnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long
  
  Private Const GWL_STYLE = (-16)
  Private Const ES_NUMBER As Long = &H2000&

Private Sub Form_Load()
  Dim lStyle As Long
  
  TxtAmnt.Text = vbNullString
  
  lStyle = GetWindowLong(TxtAmnt.hwnd, GWL_STYLE)
  SetWindowLong TxtAmnt.hwnd, GWL_STYLE, lStyle Or ES_NUMBER
End Sub

Private Sub TxtAmnt_KeyPress(KeyAscii As Integer)
  Select Case KeyAscii
    Case 3, 22
      MsgBox "Copy/paste disabled!"
      
      KeyAscii = 0
  End Select
End Sub

Private Sub TxtAmnt_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
  Select Case Button
    Case vbRightButton
      MsgBox "Copy/paste disabled!"
  End Select
End Sub

[anhn]

Thanks
Solved almost
One of my local friend forwarded me an idea.

Private Sub TxtAmnt_Change()
TxtAmnt.Text = Format(Trim(TxtAmnt.Text), "#,###")
SendKeys "{end}"
End Sub

This is OK.

Nasreen

This is a very bad advice! Very annoying!

[Code Doc]

This is a very bad advice! Very annoying!

I have to agree with AnHn. Wait a minute, I really don't have to. However, I still do.