Excel VBA Userform Keeping a running total of textboxes formatted to 2 decimal places

  • Hello,

    I have a simple userform with text boxes that I need added together, an employee enters a job number then the hours on the job then tabs to the next row. I have a check programmed that the employee can't submit the form until the total hours they worked equals the total hours for all the jobs they entered. That works. What I am trying to do now is add a visual that totals the hours they have entered so far. This also works until a fraction is entered. No matter what I try - the textbox that I use for the total will not display to 2 decimal places.

    I have attached a sheet. There are 2 hours columns, this is a manufacturing company and there can be machine hours that we need kept track of as well, so the employee enters the gross time, and the form will then subtract the unattended hours. Other than that I think its pretty self explanatory. What I am looking for is the Hour15 and Ex15 have a running total and those along with grand be displayed to 2 decimal places.

    The other thing I could not find a work around for, is that to keep a running total I had to populate all the boxes with zeros if they were still blank. If there is a better way to do that I am all for it.

    Thank you.

  • Re: Excel VBA Userform Keeping a running total of textboxes formatted to 2 decimal pl

    Use something like this:

    Hour15.Value = Format(CDbl(Hour1.Value) + CDbl(Hour2.Value), "#,##0.00")

    Your values are being read as text. You should consider converting them to a decimal using the CDBL() ,CSNG() or VAL() Functions.

    Also, you should get rid of all of the Exit and Change Events....you probably don't realize it, but when you write code like this you actually trigger a ton of events:

    Just have one button that's called calculate and have the user press it manually. This will save a ton of unneeded processing time....or I would suggest looking into using:

    Application.EnableEvents = False
    'Your Code
    Application.EnableEvents =True

    Matt Mickle
    Using Excel 2010,2013 & 2016

  • Re: Excel VBA Userform Keeping a running total of textboxes formatted to 2 decimal pl

    Welcome to the forum!

    You have so many errors in that that I can not edit the code. Some of your code does not makes sense where you are using numerical comparisons ">" for string values. In VBE's Debug menu, click Compile to compile code before a Run.

    I recommend always using Option Explicit as first like of code in Modules and Userforms.

    You should understand these concepts.
    1. All values in textbox controls are strings.
    2. Val() can be used to convert a numeric string to a number.
    3. Format() creates a string from a number.
    4. Controls() makes it easier to iterate controls in loops if prefix control names or tag values were used.

    Try using something like the concepts illustrated here:

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!