Averaging Text box values excluding blanks in VBA

Hello,

This is my second post on this forum, and I apologize if I do anything incorrectly (I have read the rules and am making every attempt to follow them).

I have created a sample file (TEST1 attached) to illustrate my questions. In the userform, I have textbox5 averaging when data is entered into textboxes 1-4. It seems like an easy fix, but I cannot figure it out today.

1.) How do I change the calculation to average only the cells with values in them?
2.) Does the code have to be input into each textbox_change command in order to work or is there a more efficient way?

That seems like a good place to start. Thank you in advance for your time and assistance.

Nolan

1) see the code below. It checks that a textbox contains a number before it adds it to the runnign sum.
2) there are several ways to do this, but all generate about the same amount of code. VBA will not allow you to share event handlers between controls.

Private Sub CommandButton1_Click()
UserForm1.Hide
End Sub

Private Sub TextBox1_Change()
DoCalc
End Sub
Private Sub TextBox2_Change()
DoCalc
End Sub
Private Sub TextBox3_Change()
DoCalc
End Sub
Private Sub TextBox4_Change()
DoCalc
End Sub

' check if a text string converts to a double
Private Function CheckValue(myText As String) As Boolean

On Error Resume Next
    Dim myValue As Double
    myValue = CDbl(myText)
    If (Err.Number <> 0) Then
        CheckValue = False
    Else
        CheckValue = True
    End If
On Error GoTo 0

End Function
Sub DoCalc()
Dim mySum As Double
Dim N As Long
mySum = 0
N = 0
If (CheckValue(Me.TextBox1.Text)) Then
    mySum = mySum + Val(Me.TextBox1.Value)
    N = N + 1
End If
If (CheckValue(Me.TextBox2.Text)) Then
    mySum = mySum + Val(Me.TextBox2.Value)
    N = N + 1
End If
If (CheckValue(Me.TextBox3.Text)) Then
    mySum = mySum + Val(Me.TextBox3.Value)
    N = N + 1
End If
If (CheckValue(Me.TextBox4.Text)) Then
    mySum = mySum + Val(Me.TextBox4.Value)
    N = N + 1
End If
If (N > 0) Then
    TextBox5.Value = mySum / N
Else
    TextBox5.Text = ""
End If
End Sub

Private Sub UserForm_Click()

End Sub