Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 5 of 5

Thread: Format Textbox Numbers For Currency

  1. #1
    Join Date
    12th March 2006
    Posts
    3

    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?

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,789

    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

  3. #3
    Join Date
    12th March 2006
    Posts
    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.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    12th September 2006
    Location
    The Great State of Texas
    Posts
    4,324

    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.
    MS MVP - Excel

  5. #5
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,789

    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

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Change Cell Format Based On Existing Currency Format
    By excelnoob01 in forum EXCEL HELP
    Replies: 5
    Last Post: June 13th, 2008, 15:10
  2. UserForm Textbox Format For Numbers
    By gsandy in forum EXCEL HELP
    Replies: 6
    Last Post: April 23rd, 2008, 13:37
  3. Replies: 4
    Last Post: April 11th, 2008, 11:43
  4. Format Textbox For Currency
    By minitman in forum EXCEL HELP
    Replies: 9
    Last Post: September 21st, 2007, 18:51
  5. Replies: 2
    Last Post: October 26th, 2003, 17:19

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno