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

gjwilson1216 · Jan 24th 2017

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.

mrmmickle1 · Jan 24th 2017

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

Use something like this:

Code

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:

Code

If Hour1.Value = "" Then Hour1.Value = 0
    If Hour2.Value = "" Then Hour2.Value = 0
    If Hour3.Value = "" Then Hour3.Value = 0
    If Hour4.Value = "" Then Hour4.Value = 0
    If Hour5.Value = "" Then Hour5.Value = 0
    If Hour6.Value = "" Then Hour6.Value = 0
    If Hour7.Value = "" Then Hour7.Value = 0
    If Hour8.Value = "" Then Hour8.Value = 0
    If Hour9.Value = "" Then Hour9.Value = 0
    If Hour10.Value = "" Then Hour10.Value = 0
    If Hour11.Value = "" Then Hour11.Value = 0
    If Hour12.Value = "" Then Hour12.Value = 0
    If Hour13.Value = "" Then Hour13.Value = 0
    If Hour14.Value = "" Then Hour14.Value = 0

Display More

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:

Code

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

**Kenneth Hobson** · Jan 24th 2017

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:

Code

Private Sub TimeTicket_Initialize()
  c0 'Set first 13 to 0
  'Example data for 14 only
  Ex14 = 1
  Hour14 = "5.10"
  SumCs 'Sum 14 controls to control 15's
End Sub


Private Sub c0()
  Dim i%
  For i = 1 To 13
    Controls("Ex" & i) = 0
    Controls("Hour" & i) = 0
  Next i
End Sub


Private Sub SumCs()
  Dim i%, d1#, d2#
  For i = 1 To 14
    d1 = d1 + Val(Controls("Ex" & i))
    d2 = d2 + Val(Controls("Hour" & i))
  Next i
  Ex15 = Format(d1, "#.00")
  Hour15 = Format(d2, "#.00")
End Sub

Display More

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

Participate now!

Share