Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Format Textbox Numbers For Currency

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Format Textbox Numbers For Currency

    I'm having a problem with this code:

    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?

  • #2
    Re: Format Textbox Numbers For Currency

    Use the Exit Event to format and the change event to validate;
    Code:
    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

    Comment


    • #3
      Re: Format Textbox Numbers For Currency

      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.

      Comment


      • #4
        Re: Format Textbox Numbers For Currency

        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?
        Code:
        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
        Entia non sunt multiplicanda sine necessitate.

        Comment


        • #5
          Re: Format Textbox Numbers For Currency

          Shg is correct. I did assume a UserForm. However, IF your textbox is inside a Frame or MultiPage Control, use;
          Code:
          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

          Comment

          Trending

          Collapse

          There are no results that meet this criteria.

          Working...
          X