Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / 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:

    VB:
    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,716

    Re: Format Textbox Numbers For Currency

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

    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?
    VB:
    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,716

    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;
    VB:
    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, 14:10
  2. UserForm Textbox Format For Numbers
    By gsandy in forum EXCEL HELP
    Replies: 6
    Last Post: April 23rd, 2008, 12:37
  3. Replies: 4
    Last Post: April 11th, 2008, 10:43
  4. Format Textbox For Currency
    By minitman in forum EXCEL HELP
    Replies: 9
    Last Post: September 21st, 2007, 17:51
  5. Replies: 2
    Last Post: October 26th, 2003, 16: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