Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 6 of 6

Thread: VBA Userform textbox numbers formating

  1. #1
    Join Date
    14th April 2011
    Location
    California
    Posts
    133

    VBA Userform textbox numbers formating

    I have a userform with several textbox (tb1, tb2, tb3). When the form opens, the textbox are populated from values in a worksheet:

    VB:
    Private Sub UserForm_Initialize() 
        [INDENT]tb1.Text = Format(Worksheets("Input").Cells(1, 1), "$#,##0")[/INDENT] 
        [INDENT]tb2.Text = Format(Worksheets("Input").Cells(2, 1), "0%")[/INDENT] 
    End Sub 
    
    
    The code works, but since the textbox are being populated with numbers I'm assuming the code needs to be changed to remove the "text" part. I tried entering "number" instead of text but got an error message. (Note- "N/A" is a possible option for the values)

    Is there a code for the textbox to keep its formatting when a user enters in a new number? (If they enter 12345 into tb1, the textbox goes to $12,345 when the user clicks outside of the textbox)

    For tb3, I need a code that will add tb1 and tb2. If the user changes either tb1 or tb2, tb3 will automatically update

    Thanks!

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    1st September 2010
    Posts
    7,334

    Re: VBA Userform textbox numbers formating

    A textbox is just that - a box to store/edit text. The .TEXT property returns the contents of the textbox as a string, it has no .NUMBER property. It does have a .VALUE property but that just reflects the 'value' of the .TEXT property as text... and vice versa.

    Also, It has no inbuilt formatting so you're going to have to do it all yourself, unfortunately. The code should go in the EXIT event handler

    If you want to format the text as numbers, you'll have to add code to the EXIT event:
    VB:
    Private Sub TB1_Exit() 
         
        TB1.Text = Format$(TB1.Text, "0.00") 
         
    End Sub 
    
    


    To add the contents of TB1 and TB2 to TB3 you need to add code to either the EXIT or CHANGE event handlers of both textboxes. If you add the code to the EXIT event handler, TB3 will be updated as TB1 or TB2 loses focus. If you add to the CHANGE event, then TB3 is updated every time either 1 or 2 is changed. Going back to what was said earlier, the contents of a text box is text, not a number, so you have to explicitly convert in any calculations.

    Example:
    VB:
     
    Private Sub tb1_Change() 
         
        tb3.Text = Val(tb1.Text) + Val(tb2.Text) 
         
    End Sub 
    
    
    The same code goes in the TB2 Change event handler...
    Last edited by cytop; May 5th, 2011 at 19:03.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    14th April 2011
    Location
    California
    Posts
    133

    Re: VBA Userform textbox numbers formating

    Thanks for the response cytop!

    The issue with the text box being for text seems a little too obvious. Thanks for the codes and advice. I'll give it a shot and see what happens

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    14th April 2011
    Location
    California
    Posts
    133

    Re: VBA Userform textbox numbers formating

    I got the userform initialization to work properly. However I run into problems running the userfom. Instead of writing out all of the code, I've attached my file.

    The userform is a little more complicated than initially described. There are now 4 textbox (tb1, tb2, tbP1, tbP2) and a label (I changed the textbox tb3 into the label tbTotal). tbTotal = tb1 / tbP1 + tb2 / tbP2. tb1, tb2, tbTotal are formated as "$#,##0" and tbP1, tbP2 as "0%"

    The goal is that the userform will load with tb1= Sheet!B1, tb2 = Sheet1!B2, tbP1 = Sheet!A7, tbP2 = Sheet!A8. The output of the userform replaces the values on the sheet with the values in the in the userform (if the user doesn't change any values, the values remain the same).

    It will probably be best for you to run the model yourself to see my flaws, but here are some of my problems:

    When the user clicks on tbP1 and tbP2 then clicks elsewhere without changing the anything, the values remain unchanged. tb1 and tb2 however revert to 0. tb1 and tb2 need to be fixed

    tbTotal initially calculates the proper total but once the user clicks on any textbox the value reverts to 0, not the new total.

    Lastly, the model is not outputting the proper values. This is probably related to the above problems but could be a problem of its own.

    -------
    I will keep working on this too and I'll let you know if I make any progress. Thanks in advance to all those who take a crack at this!
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    1st September 2010
    Posts
    7,334

    Re: VBA Userform textbox numbers formating

    The problem is to do with the $ and % signs interfering with the conversion from text to numbers...

    Can you explain the logic, please - I'm having a stupid night.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    14th April 2011
    Location
    California
    Posts
    133

    Re: VBA Userform textbox numbers formating

    I want to take the currency values in B1:B2 (eventually there will be roughly 20 values in all but lets start with 2 and I will expand it from there) and divide them by the percents in A7:A8. So this results in B1/A7, B2/A8, and so on. These new values are listed in B7:B8. B9 contains sum(B7:B9). This is part of a much larger model that I can explain a little more if you would like me to.

    The user has the option of going to B1:B2 or A7:A8 and manually changing these values directly on the worksheet. However I am creating this userform to make it more convenient for users. In this userform, B1 = tb1, B2 = tb2, A7 = tbP1, A8 = tbp2

    The userform lists all the currency values with its respective percentage next to it. The total label on the userform is only there for the connivence of the user to show what the value of tb1/tbP1 + tb2/tbP2 is.

    The user has the option to change any of the currency or percentage values in the userform. When they hit the run command button, the values in the userform replace the original values in B1:B2 and A7:A8. B7 has the formula =B1/A7 and B8 =B2/A8. B9 =sum(B7:B8). I've left these cells as formulas so that the user still has the option to manually change the other cells

    Hopefully this is enough information. Let me know

    Thanks!

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Replies: 12
    Last Post: January 29th, 2010, 02:41
  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. Validating UserForm TextBox to Only Accept Numbers
    By Dave Hawley in forum Excel FAQ
    Replies: 0
    Last Post: June 30th, 2005, 13:33
  5. VBA: More TextBox formating problems
    By JJacob in forum EXCEL HELP
    Replies: 7
    Last Post: October 27th, 2003, 08:49

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