Format Textbox Numbers For Currency

[avalpert]

I'm having a problem with this code:

Private Sub TextBox11_Change() 
    On Error Resume Next 
    TextBox11.Text = Format(CCur("0" & TextBox11.Text), "$#,##0.00") 
End Sub 

I am trying to get the textbox to format numbers as currency with a comma and set this to work on any change event. The problem is whenever I print or print preview the sheet it reverts to a simple number format. Any ideas on what is wrong and how I can fix it?

[Dave Hawley]

Use the Exit Event to format and the change event to validate;

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean) 
    TextBox1 = Format(TextBox1, "$#,##0.00") 
End Sub 
 
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 

[avalpert]

Dave,

I tried your code, the OnlyNumbers sub has a Compile Error (method or data member not found) and highlights ".ActiveControl" in the first line.

Also, the format doesn't change on the exit event - and I don't know why.

[shg]

I think Dave's code assumes this is a textbox on a userform. Perhaps you are using a textbox from the Controls Toolbox on a worksheet?

Private Sub TextBox11_LostFocus() 
    With Me.TextBox11 
        If Not IsNumeric(.Value) Then 
            MsgBox "Sorry, only numbers allowed" 
            .Value = vbNullString 
        Else 
            .Text = Format(.Value, "$#,##0.00") 
        End If 
    End With 
End Sub 

[Dave Hawley]

Shg is correct. I did assume a UserForm. However, IF your textbox is inside a Frame or MultiPage Control, use;

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean) 
    TextBox1 = Format(TextBox1, "$#,##0.00") 
End Sub 
 
Private Sub TextBox1_Change() 
    With TextBox1 
        If Not IsNumeric(.Value) And .Value <> vbNullString Then 
            MsgBox "Sorry, only numbers allowed" 
            .Value = vbNullString 
        End If 
    End With 
End Sub