Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    155
    Thanks
    70
    Thanked 0 Times in 0 Posts

    Formatting Text Boxes in User Form

    Hi Experts-

    I am using the following code in a user form. However, I am having trouble formatting. I am using the following approach but it leaves me with a green arrow in the cell indicating it is being stored as text.

    Also, do I need to use the After Update for each cell or is there a less repetitive way?Any help is much appreciated.


    Private Sub TbMoInc_AfterUpdate()
    TbMoInc.Value = Format(TbMoInc.Value, "#,##0.00")
    End Sub


    Cells(1, 4).Value = TbAge.Value
    Cells(2, 4).Value = TbRetAge.Value
    Cells(3, 4).Value = TbMoInc
    Cells(4, 4).Value = TbInf
    Cells(5, 4).Value = TbTSav
    Cells(6, 4).Value = TbPerUse1
    Cells(7, 4).Value = TbNonTaxSav
    Cells(8, 4).Value = TbPerUse2
    Cells(9, 4).Value = TbIntRate
    Cells(10, 4).Value = TbTax
    Cells(11, 4).Value = TbInc1
    Cells(12, 4).Value = TbInc2
    Cells(13, 4).Value = TbInc3
    Cells(14, 4).Value = TbSsAdj
    Last edited by Excelnewbie; 2015-06-17 at 20:51.

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    E.N.,

    Text box Value are TEXT. The Format command returns a Text value.

    Excel 2010 Developer Reference > Visual Basic for Applications Language Reference > Visual Basic Language Reference > Functions

    Format Function

    Returns a Variant (String) containing an expression formatted according to instructions contained in a format expression.

    Syntax

    Format(expression[, format[, firstdayofweek[, firstweekofyear]]])
    Use this: Cells(1, 4).Value = "=" & TbAge.Value

    HTH
    Last edited by RetiredGeek; 2015-06-17 at 19:06.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,641
    Thanks
    115
    Thanked 652 Times in 594 Posts
    Excel,

    To answer your second question first, I like to name my textboxes with the following convention: TextBox1, TextBox2, TextBox3, etc.. These sample codes allow me to loop through them when I want to do repetitive actions on them.

    UserForm with textboxes:
    Code:
    Private Sub UserForm_Click()
    With ActiveSheet
    For I = 1 To 14
        .Cells(1, 4).Value = Format(Me.Controls("TextBox" & I).Value, "#,##0.00")
    Next I
    End With
    End Sub
    Worksheet module with textboxes on sheet:
    Code:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    For I = 1 To 14
        Cells(I, 4).Value = Format(Me.OLEObjects("TextBox" & I).Object.Value, "#,##0.00")
    Next I
    End Sub
    Standard module with textboxes on sheets:
    Code:
    Private Sub CopyValues()
    Dim I As Integer
    With ActiveSheet
        For I = 1 To 14
            .Cells(I, 4) = Format(.OLEObjects("TextBox" & I).Object.Value, "#,##0.00")
        Next I
    End With
    End Sub
    To answer your first question, if I understand you correctly, I think this is the code you need to achieve your goal

    Standard module:
    Code:
    Private Sub CommandButton1_Click()
    With ActiveSheet
    For I = 1 To 14
        .Cells(I, 4).Value = Me.Controls("TextBox" & I).Value
        .Cells(I, 4).NumberFormat = ("0.00")
    Next I
    End With
    End Sub
    HTH,
    Maud

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Quote Originally Posted by Excelnewbie View Post
    Also, do I need to use the After Update for each cell or is there a less repetitive way?
    I assume you mean for each textbox, rather than cell? Typically you would use a class module and a WithEvents variable for multiple controls that need the same event code but unfortunately AfterUpdate is one of the events that actually belongs to the Control object, whose events you can't hook in this way, not to the Textbox itself, so that approach won't work. You can however use the approach shown in post #9 here: http://www.mrexcel.com/forum/excel-q...xit-event.html
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    155
    Thanks
    70
    Thanked 0 Times in 0 Posts
    Thank you RG, Maud, Rory,

    I should have included a file to begin with. I've been working with each of the tips provided by you experts. But no cigar.

    Attached is a clean file, meaning I removed all of the failed approaches I tried. It has been a learning experience.


    In the attached file, Column E is how I would like the data to appear in both the UserForm and the designated cells in column D. As you can see the form works alright, formatting the form and worksheet is the problem.

    In the future, I will do as Maud suggested and name the text boxes correctly.

    Your help is very much appreciated.
    Attached Files Attached Files

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    E.N.,

    Don't know why it won't work for you but it seems to work on my machine.
    format.JPG
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,641
    Thanks
    115
    Thanked 652 Times in 594 Posts
    Excel,

    Thanks for the sample. I believe I know what you are trying to do now.

    Place the following code in the KeyUp event for the textboxes. It will format the textbox as you type. Adjust your format as needed

    HTH,
    Maud

    Code:
    Private Sub TbMoInc_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
        TbMoInc.Value = Format(TbMoInc.Value, "#,##0")
    End Sub
    excel1.png

  8. #8
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    155
    Thanks
    70
    Thanked 0 Times in 0 Posts
    RG-

    In the file I am using the data gets out of wack when I use it. Here is a screen shot to show you what is a happening.
    Attached Images Attached Images

  9. #9
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Maude,

    Am I missing something? This is what I was working on:
    I am using the following approach but it leaves me with a green arrow in the cell indicating it is being stored as text.
    The sample sheet showed the formatting flags and that is what he was trying to get rid of, Yes/No?
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  10. #10
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,641
    Thanks
    115
    Thanked 652 Times in 594 Posts
    If you are looking to format the cells as you go, in your code for the OK button, add a formatting line after each write to the cell.

    eg.
    Code:
    Cells(3, 4).Value = TbMoInc.Value
    Cells(3, 4).NumberFormat = ("#,##0")
    HTH,
    Maud

  11. #11
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,641
    Thanks
    115
    Thanked 652 Times in 594 Posts
    RG,

    Perhaps, but This is what I was working on from his OP.

    Also, do I need to use the After Update for each cell or is there a less repetitive way?Any help is much appreciated.

    Private Sub TbMoInc_AfterUpdate()
    TbMoInc.Value = Format(TbMoInc.Value, "#,##0.00")
    End Sub
    As you can see the form works alright, formatting the form and worksheet is the problem.
    The less repetitive way is looping through objects as I described in my earlier post.
    Last edited by Maudibe; 2015-06-18 at 19:28.

  12. #12
    jwoods
    Guest
    Quote Originally Posted by Excelnewbie View Post
    RG-

    In the file I am using the data gets out of wack when I use it. Here is a screen shot to show you what is a happening.
    It looks like the edit mask is not large enough for the number it's trying to format.

    See the VBA Format function...

    https://msdn.microsoft.com/en-us/lib.../gg251755.aspx
    Last edited by jwoods; 2015-06-18 at 21:27.

  13. #13
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    155
    Thanks
    70
    Thanked 0 Times in 0 Posts
    Hi Experts,

    Thanks to each of you for your help.


    There are two problems I am encountering:

    1. This is most puzzling for me. I used the VBA number formatting and this changed it from text to a number or percent. However, when I use the cell to compute with another cell I get odd results. See sheet 3. Instead of 2% it is computing at 200%.

    2. Is there code that would change 2 to 2% when the user enters the number in the form? Some progress here, but I think I may be on the wrong track.

    Lastly, the code I am using: Cells(4, 4).Value = TbInfI, and ect. I wonder if I using the wrong or less the best code. Your thoughts?

    Thanks again. Your help is much appreciated.
    Last edited by Excelnewbie; 2015-06-23 at 16:09.

  14. #14
    jwoods
    Guest
    See the above link to the VBA Format function.

    (%) Percentage placeholder. The expression is multiplied by 100. The percent character (%) is inserted in the position where it appears in the format string.

  15. #15
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    E.N.

    When you enter 2 in the Textbox it's a whole number and that is 200%. To get 2% you need to enter .02 in the text box or divide the textbox value by 100, e.g. TextboxName.Value / 100.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Page 1 of 2 12 LastLast

Posting Permissions

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