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]

For each of the textboxes, maybe something like this

If Len(txtHabPIR.Value) > 0 And IsNumeric(CDbl(txtHabPIR.Value)) Then Total = Total + CDbl(txtHabPIR.Value)

 

[MarkRCC]

For each of the textboxes, maybe something like this

If Len(txtHabPIR.Value) > 0 And IsNumeric(CDbl(txtHabPIR.Value)) Then Total = Total + CDbl(txtHabPIR.Value)

I'm getting:

Run-Time error '13':
Type Mismatch

this is the code after adding your suggestion (in case theres something I did wrong)

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

 

[MarkRCC]

Update:

I just removed the CDbl part and associated brackets

And IsNumeric(txtHabPIR.Value) Then

and this works!!!

Thanks a lot for your help!

 

[MarkRCC]

How would I go about getting an average for these values? I was going to simply divide the total by the number of textboxes, but that wouldn't work if there are textboxes not included in the total.

 

[Rick Rothstein]

For each of the textboxes, maybe something like this

If Len(txtHabPIR.Value) > 0 And IsNumeric(CDbl(txtHabPIR.Value)) Then Total = Total + CDbl(txtHabPIR.Value)

The CDbl function will error out if its argument is a text string that cannot be converted to a number. If you want to test if txtHabPIR.Value is a number or not, you could use this If..Then test instead...

If Len(txtHabPIR.Value) > 0 And _
   txtHabPIR.Value <> "." And _
   Not txtHabPIR.Value Like "*[!0-9.]*" And _
   Not txtHabPIR.Value Like "*.*.*" Then

 

[JoeMo]

How would I go about getting an average for these values? I was going to simply divide the total by the number of textboxes, but that wouldn't work if there are textboxes not included in the total.

You can add a counter like this ( I also changed the test for a numeric value).

If Len(txtHabPIR.Value) > 0 And Val(txtHabPIR.Value) = txtHabPIR.Value Then 
       ct = ct +1
       Total = Total + CDbl(txtHabPIR.Value)
End If
'more blocks like the above for other txtboxes

myAverage = Total/ct

 

[MarkRCC]

Sorry, that counter is confusing me a bit. This is how i set it out:

Private Sub TextBoxesSum()
Dim Total As Double
Total = 0
If Len(txtHabPIR.Value) > 0 And IsNumeric(txtHabPIR.Value) Then Total = Total + CDbl(txtHabPIR.Value)
    If Len(txtHabPIR.Value) > 0 And Val(txtHabPIR.Value) = txtHabPIR.Value Then
       ct = ct + 1
       Total = Total + CDbl(txtHabPIR.Value)
    End If

'... the other 4 textboxes

txtSummary5.Value = Total / ct
End Sub

If I put "10" in the input textbox, the summary says "20", and when I delete the 10, it gives me an error and says I can't divide by 0.

 

[Rick Rothstein]

You can add a counter like this ( I also changed the test for a numeric value).

If Len(txtHabPIR.Value) > 0 And Val(txtHabPIR.Value) = txtHabPIR.Value Then

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

 

[JoeMo]

Sorry, that counter is confusing me a bit. This is how i set it out:

Private Sub TextBoxesSum()
Dim Total As Double
Total = 0
[COLOR=#ff0000][B]If Len(txtHabPIR.Value) > 0 And IsNumeric(txtHabPIR.Value) Then Total = Total + CDbl(txtHabPIR.Value)[/B][/COLOR]
    If Len(txtHabPIR.Value) > 0 And Val(txtHabPIR.Value) = txtHabPIR.Value Then
       ct = ct + 1
       Total = Total + CDbl(txtHabPIR.Value)
    End If

'... the other 4 textboxes

txtSummary5.Value = Total / ct
End Sub

If I put "10" in the input textbox, the summary says "20", and when I delete the 10, it gives me an error and says I can't divide by 0.

Just use the part I posted. The line in red above is duplicative,making 10 become 20. Delete the line in red.