VBA - A summary code to ignore non-numeric values in textboxes

[MarkRCC]

Hi All,
I've been scouring many different forums looking for code that works for me.
I have a multi-page userform, each with between 9 and 25 text boxes (it's a data entry interface)
I want there to be a text box that summarizes the scores that have been assigned to the criteria.

The code I'm currently using is:

Private Sub TextBoxesSum()
Dim Total As Double
Total = 0
If txtHabPIR.Value = n Then txtHabPIR.Value = 0
If Len(txtHabPIR.Value) > 0 Then Total = Total + CDbl(txtHabPIR.Value)
If Len(txtFishPIR.Value) > 0 Then Total = Total + CDbl(txtFishPIR.Value)
If Len(txtBirdPIR.Value) > 0 Then Total = Total + CDbl(txtBirdPIR.Value)
If Len(txtFaunaPIR.Value) > 0 Then Total = Total + CDbl(txtFaunaPIR.Value)
If Len(txtAquaPIR.Value) > 0 Then Total = Total + CDbl(txtAquaPIR.Value)
txtSummary5.Value = Total
End Sub

and for the individual text boxes I use:

Private Sub txtAquaPIR_Change()
    TextBoxesSum
End Sub

This works fine for me, but the biggest issue I have is that "NA" is one of the potential inputs. Entering anything except a numeric value instantly closes the userform. I would also prefer to keep using Textboxes instead of a ComboBox.

Is there a work around for this? Preferably, a way to assign a value of 0 to "NA" or to make the code completely ignore non-numeric values in the sum.

Thanks in advance!!

 

[JoeMo]

A minor possible problem with the highlighted test... if the user types a value like 3D5 in the TextBox, your test will pass it and 300000 will be added to the total. Other (probably less likely) entries that would pass your test and add (maybe) a surprising value to the total...

&123 would add 83

&HEAD would add 3757

If you read the OP Rick you will see that the concern is with a value of "NA" being in the text box rather than a number. So, the problems you highlight are not likely to arise. That's why I used the more compact approach instead of your suggestion in post #6.

 

[Rick Rothstein]

If you read the OP Rick you will see that the concern is with a value of "NA" being in the text box rather than a number. So, the problems you highlight are not likely to arise.

Which is why I labeled it a "minor possible problem" with the emphasis on the word "possible". I would note, however, that just after the OP said "that 'NA' is one of the potential inputs" (emphasis mine), he went on to say... "Entering anything except a numeric value..." which leads me to believe his users could be typing in other non-numeric values besides "NA". The only reason I raised the concern that I did was to alert the OP (and others who might read this thread in the future) that the method you employed was not absolutely foolproof. Actually, I liked the method that you used to test if a number was a number, and I would expect it to work trouble-free in well over 99% of the time, but... there is still that ever so slight weakness in it. And the only reason for concern over that slight weakness is because a real number could be generated for the calculation where none would be expected.

 

[SpillerBD]

Rick,
I'm still trying to get the worksheet function AGREGATE into my "regular use" set of functions. Reading this thread I was wondering your opinion and how valid it would be here?

(reader's info: AGGREGATE was introduced w Excel 2013.)

 

[Rick Rothstein]

I'm still trying to get the worksheet function AGREGATE into my "regular use" set of functions. Reading this thread I was wondering your opinion and how valid it would be here?

Actually, I have not studied the AGGREGATE function well enough to know the answer to that question.

(reader's info: AGGREGATE was introduced w Excel 2013.)

I am using Excel 2010 and the AGGREGATE function exists in my copy of Excel, so I am guessing it was introduced in Excel 2010.

 

[JoeMo]

Which is why I labeled it a "minor possible problem" with the emphasis on the word "possible". I would note, however, that just after the OP said "that 'NA' is one of the potential inputs" (emphasis mine), he went on to say... "Entering anything except a numeric value..." which leads me to believe his users could be typing in other non-numeric values besides "NA". The only reason I raised the concern that I did was to alert the OP (and others who might read this thread in the future) that the method you employed was not absolutely foolproof. Actually, I liked the method that you used to test if a number was a number, and I would expect it to work trouble-free in well over 99% of the time, but... there is still that ever so slight weakness in it. And the only reason for concern over that slight weakness is because a real number could be generated for the calculation where none would be expected.

Fair enough, and I appreciate your enlightening all of us about the potential pitfalls.